Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query? - Mailing list pgsql-general

From
Subject Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?
Date
Msg-id 001e01d19ae1$28614f00$7923ed00$@andl.org
Whole thread Raw
In response to Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?  (Albe Laurenz <laurenz.albe@wien.gv.at>)
Responses Re: How do BEGIN/COMMIT/ABORT operate in a nested SPI query?
List 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




pgsql-general by date:

Previous
From: Andreas Joseph Krogh
Date:
Subject: Re: Initdb --data-checksums by default
Next
From: Moreno Andreo
Date:
Subject: PG 9.1 on windows setup problem