Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 a call 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

<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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

<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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general