About subxact and xact nesting level...

On Mon, May 2, 2016 at 12:24 PM, <david@andl.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

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

Thomas Munro <thomas.munro@enterprisedb.com> writes:

On Mon, May 2, 2016 at 12:24 PM, <david@andl.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

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

From: Thomas Munro [mailto:thomas.munro@enterprisedb.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Thomas Munro [mailto:thomas.munro@enterprisedb.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

From: Tom Lane [mailto:tgl@sss.pgh.pa.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

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

"dandl" <david@andl.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

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

owner@postgresql.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

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