Thread: About subxact and xact nesting level...

About subxact and xact nesting level...

From
Date:
<div class="WordSection1"><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Stilltrying to find
myway around the source code…</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><p
class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Thefile xact.c
containsreferences to sub-transactions (subxact) and transaction nesting level, but no obvious documentation about what
thesecorrespond to in SQL. A search shows that plpython supports something called “proper sub transactions”. There are
randommentions of subtransactions in the release notes, but nothing substantive that I can find, and nothing about
transactionnesting.</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><p
class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Anypointers to docs
orhelp to understand much appreciated.</span><p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span><p
class="MsoNormal"><spanstyle="font-size:10.0pt;font-family:"Georgia",serif;color:#1F497D">Regards</span><span
style="font-size:11.0pt;font-family:"Georgia",serif;color:#1F497D"></span><pclass="MsoNormal"
style="mso-margin-top-alt:6.0pt;margin-right:0cm;margin-bottom:6.0pt;margin-left:0cm"><span
style="font-size:14.0pt;font-family:"Georgia",serif;color:#1F497D">DavidM Bennett </span><span
style="font-size:8.0pt;font-family:"Georgia",serif;color:#1F497D">FACS</span><span
style="font-size:14.0pt;font-family:"Georgia",serif;color:#1F497D"></span><divalign="center" class="MsoNormal"
style="text-align:center"><i><spanstyle="font-size:8.0pt;font-family:"Georgia",serif;color:#1F497D"><hr align="center"
size="2"width="100%" /></span></i></div><p class="MsoNormal"><i><span
style="font-size:8.0pt;font-family:"Georgia",serif;color:#1F497D">Andl- A New Database Language - <a
href="andl.org">andl.org</a></span></i><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"> </span><pclass="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"> </span></div>

Re: About subxact and xact nesting level...

From
Thomas Munro
Date:
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



Re: About subxact and xact nesting level...

From
Tom Lane
Date:
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



Re: About subxact and xact nesting level...

From
"dandl"
Date:
> 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
transactionenvironment is turning out to be a major challenge.  

Regards
David M Bennett FACS

Andl - A New Database Language - andl.org








Re: About subxact and xact nesting level...

From
Date:
> 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
transactionenvironment 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




Re: About subxact and xact nesting level...

From
"dandl"
Date:
> 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








Re: About subxact and xact nesting level...

From
Tom Lane
Date:
"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



Re: About subxact and xact nesting level...

From
"dandl"
Date:
> 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