Skip site navigation (1) Skip section navigation (2)

Re: autonomous transactions

From: Hans-Juergen Schoenig <postgres(at)cybertec(dot)at>
To: "Decibel!" <decibel(at)decibel(dot)org>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Simon Riggs <simon(at)2ndquadrant(dot)com>, Neil Conway <neilc(at)samurai(dot)com>, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Alvaro Herrera <alvherre(at)commandprompt(dot)com>, "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: autonomous transactions
Date: 2008-01-29 07:52:39
Message-ID: D81F36E4-65FB-4857-A6C6-543530948845@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
On Jan 25, 2008, at 7:27 AM, Decibel! wrote:

> On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:
>> Simon Riggs <simon(at)2ndquadrant(dot)com> writes:
>>>> From looking at how Oracle does them, autonomous transactions are
>>>> completely independent of the transaction that originates them  
>>>> -- they
>>>> take a new database snapshot. This means that uncommitted  
>>>> changes in the
>>>> originating transaction are not visible to the autonomous  
>>>> transaction.
>>
>>> Oh! Recursion depth would need to be tested for as well. Nasty.
>>
>> Seems like the cloning-a-session idea would be a possible  
>> implementation
>> path for these too.
>
> Oracle has a feature where you can effectively save a session and  
> return
> to it. For example, if filling out a multi-page web form, you could  
> save
> state in the database between those calls. I'm assuming that they use
> that capability for their autonomous transactions; save the current
> session to the stack, clone it, run the autonomous transaction, then
> restore the saved one.
>

If you want to use it for webforms you cannot just put it on the  
stack - you had to put it in shared memory because you don't know if  
you will ever get the same database connection back from the pool.
personally i like marko's idea. if a snapshot was identified by a key  
it would be perfect. we could present the snapshots saved as a nice  
nice superuser-readable system view (similar to what we do for 2PC)

the only thing i would do is to give those snapshots some sort of  
timeout (configurable). otherwise we will get countless VACUUM  
related reports.
this sounds like a very cool feature - definitely useful.

	many thanks,

		hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, 2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql.at, www.cybertec.at


In response to

pgsql-hackers by date

Next:From: Marko KreenDate: 2008-01-29 08:06:45
Subject: Re: [GENERAL] SHA1 on postgres 8.3
Previous:From: Kris JurkaDate: 2008-01-29 05:09:28
Subject: Re: Proposed patch: synchronized_scanning GUC variable

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group