Обсуждение: Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?
I am attempting to create a new language implementation. The language is Andl (andl.org), so the handler is plandl. This is a question about executing SPI queries from inside plandl. The documentation makes it clear that SPI allows nested queries; that in some instances it will be necessary to call SPI_push()and SPI_pop(), but in others this will be handled automatically. Se http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. It is an important design feature of plandl to allow nested queries. My question is: where are the transaction boundaries if the inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Dothey nest, or does an inner COMMIT finish a transaction started by an outer BEGIN, or is it ignored? Regards David M Bennett FACS Andl - A New Database Language - andl.org
david@andl.org wrote: > I am attempting to create a new language implementation. The language is Andl (andl.org), so the > handler is plandl. > This is a question about executing SPI queries from inside plandl. > > The documentation makes it clear that SPI allows nested queries; that in some instances it will be > necessary to call SPI_push() and SPI_pop(), but in others this will be handled automatically. Se > http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. > > It is an important design feature of plandl to allow nested queries. > > My question is: where are the transaction boundaries if the inner/outer query do or do not contain > BEGIN/ABORT/COMMIT? Do they nest, or does an inner COMMIT finish a transaction started by an outer > BEGIN, or is it ignored? You cannot have BEGIN or COMMIT inside a function. Yours, Laurenz Albe
> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > > > I am attempting to create a new language implementation. The language > > is Andl (andl.org), so the handler is plandl. > > This is a question about executing SPI queries from inside plandl. > > > > The documentation makes it clear that SPI allows nested queries; that > > in some instances it will be necessary to call SPI_push() and > > SPI_pop(), but in others this will be handled automatically. Se > http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. > > > > It is an important design feature of plandl to allow nested queries. > > > > My question is: where are the transaction boundaries if the > > inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Do they > > nest, or does an inner COMMIT finish a transaction started by an outer > BEGIN, or is it ignored? > > You cannot have BEGIN or COMMIT inside a function. Are you sure you meant it like that? I already have BEGIN/COMMIT inside a function and it works perfectly. If it did not,then it would be impossible to use BEGIN/COMMIT in any language handler, since every call to a language handler is acall to a function. Did you mean 'inside a nested function'? Or something else? Regards David M Bennett FACS Andl - A New Database Language - andl.org
On 4/18/2016 5:41 PM, david@andl.org wrote: > Are you sure you meant it like that? I already have BEGIN/COMMIT inside a function and it works perfectly. If it did not,then it would be impossible to use BEGIN/COMMIT in any language handler, since every call to a language handler is acall to a function. the transaction has already been started before your function is called. and you can not issue a COMMIT from anywhere but the top level. you CAN have savepoints, which act something like nested transactions within a function. -- john r pierce, recycling bits in santa cruz
> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > > > I am attempting to create a new language implementation. The language > > is Andl (andl.org), so the handler is plandl. > > This is a question about executing SPI queries from inside plandl. > > > > The documentation makes it clear that SPI allows nested queries; that > > in some instances it will be necessary to call SPI_push() and > > SPI_pop(), but in others this will be handled automatically. Se > http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. > > > > It is an important design feature of plandl to allow nested queries. > > > > My question is: where are the transaction boundaries if the > > inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Do they > > nest, or does an inner COMMIT finish a transaction started by an outer > BEGIN, or is it ignored? > > You cannot have BEGIN or COMMIT inside a function. Are you sure you meant it like that? I already have BEGIN/COMMIT inside a function and it works perfectly. If it did not,then it would be impossible to use BEGIN/COMMIT in any language handler, since every call to a language handler is acall to a function. Did you mean 'inside a nested function'? Or something else? Regards David M Bennett FACS Andl - A New Database Language - andl.org
David Bennett wrote: > > From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > > > > > I am attempting to create a new language implementation. The language > > > is Andl (andl.org), so the handler is plandl. > > > This is a question about executing SPI queries from inside plandl. > > > > > > The documentation makes it clear that SPI allows nested queries; that > > > in some instances it will be necessary to call SPI_push() and > > > SPI_pop(), but in others this will be handled automatically. Se > > http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. > > > > > > It is an important design feature of plandl to allow nested queries. > > > > > > My question is: where are the transaction boundaries if the > > > inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Do they > > > nest, or does an inner COMMIT finish a transaction started by an outer > > BEGIN, or is it ignored? > > > > You cannot have BEGIN or COMMIT inside a function. > > Are you sure you meant it like that? I already have BEGIN/COMMIT inside a function and it works > perfectly. If it did not, then it would be impossible to use BEGIN/COMMIT in any language handler, > since every call to a language handler is a call to a function. > > Did you mean 'inside a nested function'? Or something else? I guess I'm out of my depth when it comes to language handlers... But I cannot see how you can have BEGIN or COMMIT called from inside one. Doesn't it look like that: BEGIN; SELECT my_andl_function(); COMMIT; Since there are no autonomous transactions in PostgreSQL, how can you have BEGIN and COMMIT called from the code that is invoked by "SELECT my_andl_function()"? Yours, Laurenz Albe
> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at] > > > > I am attempting to create a new language implementation. The > > > > language is Andl (andl.org), so the handler is plandl. > > > > This is a question about executing SPI queries from inside plandl. > > > > > > > > The documentation makes it clear that SPI allows nested queries; > > > > that in some instances it will be necessary to call SPI_push() and > > > > SPI_pop(), but in others this will be handled automatically. Se > > > http://www.postgresql.org/docs/9.5/interactive/spi-spi-push.html. > > > > > > > > It is an important design feature of plandl to allow nested queries. > > > > > > > > My question is: where are the transaction boundaries if the > > > > inner/outer query do or do not contain BEGIN/ABORT/COMMIT? Do they > > > > nest, or does an inner COMMIT finish a transaction started by an > > > > outer > > > BEGIN, or is it ignored? > > > > > > You cannot have BEGIN or COMMIT inside a function. > > > > Are you sure you meant it like that? I already have BEGIN/COMMIT > > inside a function and it works perfectly. If it did not, then it would > > be impossible to use BEGIN/COMMIT in any language handler, since every call > to a language handler is a call to a function. > > > > Did you mean 'inside a nested function'? Or something else? > > I guess I'm out of my depth when it comes to language handlers... > > But I cannot see how you can have BEGIN or COMMIT called from inside one. > > Doesn't it look like that: > > BEGIN; > SELECT my_andl_function(); > COMMIT; > > Since there are no autonomous transactions in PostgreSQL, how can you have > BEGIN and COMMIT called from the code that is invoked by "SELECT > my_andl_function()"? I really don't know. But I have code that does this (no explicit BEGIN): SELECT * FROM COMPILE($$ V6 := {{ abo:=true, abi:=b'DEADBEEF', anu:=123456789.987654321, ate:='abcdef', ati:=t'2015-12-31 23:59:58.9999' }, { abo:=false, abi:=b'DEADBEEF', anu:=987654321.123456789, ate:='ghijklmno', ati:=t'2016-12-31 23:59:58.9999' }} V6 $$); And the generated code (which executes without error): BEGIN; DROP TABLE IF EXISTS "V6" ; CREATE TABLE "V6" ( "abo" BOOLEAN, "abi" BYTEA, "anu" NUMERIC, "ate" TEXT, "ati" TIMESTAMP, UNIQUE ( "abo", "abi", "anu","ate", "ati" ) ); COMMIT; INSERT INTO "V6" ( "abo", "abi", "anu", "ate", "ati" ) VALUES ( $1, $2, $3, $4, $5 ); SELECT "abo", "abi", "anu", "ate", "ati" FROM "V6"; Maybe the generated BEGIN/COMMIT are ignored? I haven't tried an ABORT yet. Regards David M Bennett FACS Andl - A New Database Language - andl.org
<david@andl.org> writes:
>> From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
>> Since there are no autonomous transactions in PostgreSQL, how can you have
>> BEGIN and COMMIT called from the code that is invoked by "SELECT
>> my_andl_function()"?
> I really don't know. But I have code that does this (no explicit BEGIN):
> SELECT * FROM COMPILE($$
> V6 := {{ abo:=true, abi:=b'DEADBEEF', anu:=123456789.987654321, ate:='abcdef', ati:=t'2015-12-31 23:59:58.9999' },
> { abo:=false, abi:=b'DEADBEEF', anu:=987654321.123456789, ate:='ghijklmno', ati:=t'2016-12-31 23:59:58.9999'
}}
> V6
> $$);
> And the generated code (which executes without error):
> BEGIN;
> DROP TABLE IF EXISTS "V6" ;
> CREATE TABLE "V6" ( "abo" BOOLEAN, "abi" BYTEA, "anu" NUMERIC, "ate" TEXT, "ati" TIMESTAMP, UNIQUE ( "abo", "abi",
"anu","ate", "ati" ) );
> COMMIT;
> INSERT INTO "V6" ( "abo", "abi", "anu", "ate", "ati" ) VALUES ( $1, $2, $3, $4, $5 );
> SELECT "abo", "abi", "anu", "ate", "ati" FROM "V6";
Define "executes". You could shove those lines in via the wire protocol,
sure, but SPI won't take them.
regards, tom lane
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> > I really don't know. But I have code that does this (no explicit BEGIN):
>
> > SELECT * FROM COMPILE($$
> > V6 := {{ abo:=true, abi:=b'DEADBEEF', anu:=123456789.987654321,
> ate:='abcdef', ati:=t'2015-12-31 23:59:58.9999' },
> > { abo:=false, abi:=b'DEADBEEF', anu:=987654321.123456789,
> > ate:='ghijklmno', ati:=t'2016-12-31 23:59:58.9999' }}
> > V6
> > $$);
>
> > And the generated code (which executes without error):
>
> > BEGIN;
> > DROP TABLE IF EXISTS "V6" ;
> > CREATE TABLE "V6" ( "abo" BOOLEAN, "abi" BYTEA, "anu" NUMERIC, "ate"
> > TEXT, "ati" TIMESTAMP, UNIQUE ( "abo", "abi", "anu", "ate", "ati" ) );
> > COMMIT; INSERT INTO "V6" ( "abo", "abi", "anu", "ate", "ati" ) VALUES
> > ( $1, $2, $3, $4, $5 ); SELECT "abo", "abi", "anu", "ate", "ati" FROM
> > "V6";
>
> Define "executes". You could shove those lines in via the wire protocol,
> sure, but SPI won't take them.
Now you really have me puzzled. What I provided is an extract from the log
of generated SQL commands sent to the SPI interface. [Obviously there also
values bound to the parameters which do not show up here.]
The code executes without error and produces exactly the output I expected.
I'll do some more checking to see if I missed something, but so far it just
works.
Regards
David M Bennett FACS
Andl - A New Database Language - andl.org
<david@andl.org> writes:
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>> Define "executes". You could shove those lines in via the wire protocol,
>> sure, but SPI won't take them.
> Now you really have me puzzled. What I provided is an extract from the log
> of generated SQL commands sent to the SPI interface. [Obviously there also
> values bound to the parameters which do not show up here.]
> The code executes without error and produces exactly the output I expected.
Are you remembering to check the result code from SPI_execute_whatever?
regards, tom lane
> >> Define "executes". You could shove those lines in via the wire > >> protocol, sure, but SPI won't take them. > > > Now you really have me puzzled. What I provided is an extract from the > > log of generated SQL commands sent to the SPI interface. [Obviously > > there also values bound to the parameters which do not show up here.] > > > The code executes without error and produces exactly the output I expected. > > Are you remembering to check the result code from SPI_execute_whatever? Of course. Every SPI call is checked and any unexpected error is trapped, resulting eventually in a call to elog(ERROR). The sequence I provided is a CREATE TABLE followed by an INSERT. The table is successfully created with the correct contents. Regards David M Bennett FACS Andl - A New Database Language - andl.org