Re: [psycopg] Nested transactions support for code composability

On 01/22/2017 08:49 AM, Daniel Fortunov wrote:
> On 16 January 2017 at 23:29, Christophe Pettus <xof@thebuild.com
> <mailto:xof@thebuild.com>> wrote:
>
>
>     > On Jan 16, 2017, at 15:26, Daniel Fortunov <psycopg-list@danielfortunov.com
>     <mailto:psycopg-list@danielfortunov.com>> wrote:
>     >
>     > I'd like to implement support for nested transactions in psycopg2 using a context manager that internally uses
postgressavepoints to implement the ability to nest transactions within each other, with sensible commit and rollback
semantics.
>
>     You can see two existing examples of this, based on Django.  Django
>     implements the @atomic() decorator, which was based on my @xact()
>     decorator:
>
>             https://github.com/Xof/xact
>
>     They can almost certainly be eased out of the Django infrastructure
>     easily enough!
>
> Yes, this is exactly what I'm talking about.
>
> So what am I missing? Doesn't anyone find the need for this outside of
> Django?!
>
> How do people use transactions in (non-Django) library code?

See Christophe's post for a pre-built solution.

I have not used nested transaction outside of Django's implementation of
@xact() eg @atomic(). Still a little fooling around with psycopg2 code
led to this:

In [3]: con = psycopg2.connect("dbname=test user=aklaver host=localhost")

In [4]: cur = con.cursor()

In [5]: cur.execute('select 1')

In [6]: rs = cur.fetchone()

In [7]: rs
Out[7]: (1,)

In [8]: cur.execute('savepoint test_savepoint')

In [9]: try:
    ...:     cur.execute('select 1/0')
    ...: except psycopg2.DataError:
    ...:     cur.execute('ROLLBACK TO SAVEPOINT test_savepoint')
    ...:     print('Rollback')
    ...:
    ...:
Rollback

In [10]: cur.execute('select 2')

In [11]: rs = cur.fetchone()

In [12]: rs
Out[12]: (2,)

>
> Daniel


--
Adrian Klaver
adrian.klaver@aklaver.com