Forum: Too Lazy BBS

[Info-ingres] x100 transaction

From Martin Bowes@martin.bowes@ndph.ox.ac.uk to info-ingres@lists.planetingres.org on Fri May 7 10:43:04 2021

From Newsgroup: comp.databases.ingres

--_000_3d80aac9cb7e47b3ab7938d663951fa0ndphoxacuk_
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
X-WatchGuard-AntiVirus: part scanned. clean action=allow

Hi All,

On an x100 enabled database I did:

drop table if exists base;
create table base(...) with structure =3D x100;

The base table does not exist, I only added the drop command in case I need=
to rerun this creation script.

So I was somewhat surprised to be confronted with error:
E_US250E X100 statements are not allowed in this transaction.
Create or access an X100 table at the start of a new transaction
to enable X100 statement processing.

But I can do this with no error:
drop table if exists normal;
create table normal(a integer4 not null not default);
select count(1) from normal;
insert into normal values(1);
drop table if exists base;
create table base(...) with structure =3D x100;

This doesn't look right to me. Any ideas?

Martin Bowes

--_000_3d80aac9cb7e47b3ab7938d663951fa0ndphoxacuk_
Content-Type: text/html; charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
X-WatchGuard-AntiVirus: part scanned. clean action=allow

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" xmlns:o=3D"urn:schemas-micr= osoft-com:office:office" xmlns:w=3D"urn:schemas-microsoft-com:office:word" = xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" xmlns=3D"http:= //www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3Dus-ascii"=

<meta name=3D"Generator" content=3D"Microsoft Word 15 (filtered medium)"> <style>
</head>
<body lang=3D"EN-GB" link=3D"#0563C1" vlink=3D"#954F72">
<div class=3D"WordSection1">
Hi All,<o:p></o:p>
<o:p> </o:p>
On an x100 enabled database I did:<o:p></o:p>
<o:p> </o:p>
drop table if exists base;<o:p></o:p>
create table base(…) with structure =3D x100;<= o:p></o:p>
<o:p> </o:p>
The base table does not exist, I only added the drop=
command in case I need to rerun this creation script.
<o:p></o:p>
<o:p> </o:p>
So I was somewhat surprised to be confronted with er= ror:<o:p></o:p>
E_US250E X100 statements = are not allowed in this transaction.<o:p></o:p>
    Create=
or access an X100 table at the start of a new transaction<o:p></o:p></span= >
    to ena= ble X100 statement processing.<o:p></o:p>
<o:p> </o:p>
But I can do this with no error:<o:p></o:p>
drop table if exists normal;<o:p></o:p>
create table normal(a integer4 not null not default)= ;<o:p></o:p>
select count(1) from normal;<o:p></o:p>
insert into normal values(1);<o:p></o:p>
drop table if exists base;<o:p></o:p>
create table base(…) with structure =3D x100;<= o:p></o:p>
<o:p> </o:p>
This doesn’t look right to me. Any ideas?<o:p>= </o:p>
<o:p> </o:p>
Martin Bowes<o:p></o:p>
<o:p> </o:p>
</div>
</body>
</html>

--_000_3d80aac9cb7e47b3ab7938d663951fa0ndphoxacuk_--
--- Synchronet 3.21b-Linux NewsLink 1.2

From Karl Schendel@schendel@kbcomputer.com to Martin Bowes on Fri May 7 10:25:38 2021

From Newsgroup: comp.databases.ingres

On May 7, 2021, at 6:43 AM, Martin Bowes <martin.bowes@ndph.ox.ac.uk> wrote:

Hi All,

On an x100 enabled database I did:

drop table if exists base;
create table base(rCa) with structure = x100;

The base table does not exist, I only added the drop command in case I need to rerun this creation script.

So I was somewhat surprised to be confronted with error:
E_US250E X100 statements are not allowed in this transaction.
Create or access an X100 table at the start of a new transaction
to enable X100 statement processing.

You can put a commit after the drop if exists.
What's going on is that the delay_txn_start config parameter is probably set to ON.
That tells the DBMS server to not bother starting an X100 transaction when an Ingres transaction starts, unless something x100-y is being done. The initial drop
table doesn't force an x100 transaction start because there's no x100 work to do.
The create table does an explicit check to see if it's in an Ingres transaction but
no x100 transaction and throws that error. Once X100 tables exist, the check is
a little different and you don't get the error.
Karl
--- Synchronet 3.21b-Linux NewsLink 1.2

From Martin Bowes@martin.bowes@ndph.ox.ac.uk to Karl Schendel on Tue May 11 10:09:43 2021

From Newsgroup: comp.databases.ingres

Hi Karl,

What's going on is that the delay_txn_start config parameter is probably set to ON.

Yes.

That tells the DBMS server to not bother starting an X100 transaction when an Ingres transaction starts, unless something x100-y is being done. The initial drop table doesn't force an x100 transaction start because there's no x100 work to do. The create table does an explicit check to see if it's in an Ingres transaction but no x100 transaction and throws that error.

Seems fair.

Once X100 tables exist, the check is a little different and you don't get the error.

Whoa! An initialisation condition.

So starting with an empty database...

The drop table if exists / create table with structure = x100 fails as before.

However, if we ensure we have at least one existing x100 table first...:

create table dummy(id integer4 not null not default) with structure = x100; commit;

drop table if exists base;
create table base(id integer4 not null not default) with structure = x100; commit;

That now works as expected.

Marty
_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres
--- Synchronet 3.21b-Linux NewsLink 1.2

Who's Online

System Info

Sysop:	Amessyroom
Location:	Fayetteville, NC
Users:	59
Nodes:	6 (0 / 6)
Uptime:	25:29:48
Calls:	810
Files:	1,287
Messages:	196,022

[Info-ingres] x100 transaction

Who's Online

System Info