About subxact and xact nesting level...

Still trying to find my way around the source code…

The file xact.c contains references to sub-transactions (subxact) and transaction nesting level, but no obvious documentation about what these correspond to in SQL. A search shows that plpython supports something called “proper sub transactions”. There are random mentions of subtransactions in the release notes, but nothing substantive that I can find, and nothing about transaction nesting.

Any pointers to docs or help to understand much appreciated.

Regards

David M Bennett FACS

_____

Andl - A New Database Language - andl.org

On Mon, May 2, 2016 at 12:24 PM, <david(at)andl(dot)org> wrote:
> Still trying to find my way around the source code…
>
>
>
> The file xact.c contains references to sub-transactions (subxact) and
> transaction nesting level, but no obvious documentation about what these
> correspond to in SQL. A search shows that plpython supports something called
> “proper sub transactions”. There are random mentions of subtransactions in
> the release notes, but nothing substantive that I can find, and nothing
> about transaction nesting.
>
>
>
> Any pointers to docs or help to understand much appreciated.

Subtransactions are used to implement SAVEPOINT, and also BEGIN blocks
with EXCEPTION clauses in plpgsql.

http://www.postgresql.org/docs/9.5/static/sql-savepoint.html
http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

--
Thomas Munro
http://www.enterprisedb.com

Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com> writes:
> On Mon, May 2, 2016 at 12:24 PM, <david(at)andl(dot)org> wrote:
>> The file xact.c contains references to sub-transactions (subxact) and
>> transaction nesting level, but no obvious documentation about what these
>> correspond to in SQL.

> Subtransactions are used to implement SAVEPOINT, and also BEGIN blocks
> with EXCEPTION clauses in plpgsql.

Yeah. The implementation is based on nested subtransactions, and that
concept also applies pretty directly to, eg, BEGIN/EXCEPT blocks in
plpgsql. But what's exposed to SQL is SAVEPOINT/RELEASE SAVEPOINT/
ROLLBACK TO SAVEPOINT, because those operations are what the standard
specifies. If you hold your head at the correct angle you can see those
as nested subtransactions, but it's not exactly obvious --- mainly because
RELEASE and ROLLBACK can exit multiple levels of nested subtransaction in
one command.

regards, tom lane

> From: Thomas Munro [mailto:thomas(dot)munro(at)enterprisedb(dot)com]

> > The file xact.c contains references to sub-transactions (subxact) and
> > transaction nesting level, but no obvious documentation about what
> > these correspond to in SQL. A search shows that plpython supports
> > something called “proper sub transactions”. There are random mentions
> > of subtransactions in the release notes, but nothing substantive that
> > I can find, and nothing about transaction nesting.
> >
> > Any pointers to docs or help to understand much appreciated.
>
> Subtransactions are used to implement SAVEPOINT, and also BEGIN blocks with
> EXCEPTION clauses in plpgsql.
>
> http://www.postgresql.org/docs/9.5/static/sql-savepoint.html
> http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Thanks. I guess that explains the nesting level too. It seems there is an internal API based on:
* BeginInternalSubTransaction
* RollbackAndReleaseCurrentSubTransaction
* ReleaseCurrentSubTransaction

This looks like something I shall need to use. I have the plandl language handler all working, and understanding the transaction environment is turning out to be a major challenge.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

> From: Thomas Munro [mailto:thomas(dot)munro(at)enterprisedb(dot)com]

> > The file xact.c contains references to sub-transactions (subxact) and
> > transaction nesting level, but no obvious documentation about what
> > these correspond to in SQL. A search shows that plpython supports
> > something called “proper sub transactions”. There are random mentions
> > of subtransactions in the release notes, but nothing substantive that
> > I can find, and nothing about transaction nesting.
> >
> > Any pointers to docs or help to understand much appreciated.
>
> Subtransactions are used to implement SAVEPOINT, and also BEGIN blocks with
> EXCEPTION clauses in plpgsql.
>
> http://www.postgresql.org/docs/9.5/static/sql-savepoint.html
> http://www.postgresql.org/docs/9.5/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

Thanks. I guess that explains the nesting level too. It seems there is an internal API based on:
* BeginInternalSubTransaction
* RollbackAndReleaseCurrentSubTransaction
* ReleaseCurrentSubTransaction

This looks like something I shall need to use. I have the plandl language handler all working, and understanding the transaction environment is turning out to be a major challenge.

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

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

> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
> >> The file xact.c contains references to sub-transactions (subxact) and
> >> transaction nesting level, but no obvious documentation about what
> >> these correspond to in SQL.
>
> > Subtransactions are used to implement SAVEPOINT, and also BEGIN blocks
> > with EXCEPTION clauses in plpgsql.
>
> Yeah. The implementation is based on nested subtransactions, and that
> concept also applies pretty directly to, eg, BEGIN/EXCEPT blocks in
plpgsql.
> But what's exposed to SQL is SAVEPOINT/RELEASE SAVEPOINT/ ROLLBACK TO
> SAVEPOINT, because those operations are what the standard specifies. If
you
> hold your head at the correct angle you can see those as nested
> subtransactions, but it's not exactly obvious --- mainly because RELEASE
and
> ROLLBACK can exit multiple levels of nested subtransaction in one command.

Yes, that answers the question.

What now concerns me is that access to these capability seems to require
calling these three 'internal' functions, for which it's hard to determine
the prerequisites. The SPI interface is well-documented and the conversion
functions I'm using are not stateful and look pretty safe. I've got the
process model figured out well enough, but the transaction model is not so
easy. State looks important; so does memory management.

Are there specific requirements or things to do/avoid in order to use
subtransactions (at the PL API level)?

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org

"dandl" <david(at)andl(dot)org> writes:
> Are there specific requirements or things to do/avoid in order to use
> subtransactions (at the PL API level)?

There isn't, currently, any very hard-and-fast rule about what APIs
extensions should use or not use. My advice is to borrow freely
from existing PLs, particularly pl/pgsql. You might have to change
your code in future PG major versions, but that could happen anyway.

regards, tom lane

> owner(at)postgresql(dot)org] On Behalf Of Tom Lane
> > Are there specific requirements or things to do/avoid in order to use
> > subtransactions (at the PL API level)?
>
> There isn't, currently, any very hard-and-fast rule about what APIs
> extensions should use or not use. My advice is to borrow freely from
> existing PLs, particularly pl/pgsql. You might have to change your code
in
> future PG major versions, but that could happen anyway.

I guess you're right. It's not that big, and most of the interesting stuff
seems to be in just a couple of files. And after all, that should be the
gold standard for a PL!

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org