Re: autonomous transactions

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