Thread: autonomous transactions

autonomous transactions

From
"Roberts, Jon"
Date:
<div class="Section1"><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I really needed this functionality in PostgreSQL.  A common use for autonomous transactions is error
logging. I want to log sqlerrm in a function and raise an exception so the calling application knows there is an error
andI have it logged to a table.  </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I figured out a way to "hack" an autonomous transaction by using a dblink in a function and here is
asimple example:</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">create or replace function fn_log_error(p_function varchar, p_location int, p_error varchar) returns void
as</span></font><pclass="MsoPlainText"><font face="Courier New" size="2"><span style="font-size: 
10.0pt">$$</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">declare</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_sql varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_return varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  v_error varchar;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">begin</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  perform dblink_connect('connection_name', 'dbname=...');</span></font><p class="MsoPlainText"><font
face="CourierNew" size="2"><span style="font-size: 
10.0pt">  </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  v_sql := 'insert into error_log (function_name, location, error_message, error_time) values (''' ||
p_function_name|| ''', ' || </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">           p_location || ', ''' || p_error || ''', clock_timestamp())';</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">  </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  select * from dblink_exec('connection_name', v_sql, false) into v_return;</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  --get the error message</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  select * from dblink_error_message('connection_name') into v_error;</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0 then</span></font><p
class="MsoPlainText"><fontface="Courier New" size="2"><span style="font-size: 
10.0pt">    raise exception '%', v_error;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">  end if;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  perform dblink_disconnect('connection_name');</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">exception</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">  when others then</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span
style="font-size:
10.0pt">    perform dblink_disconnect('connection_name');</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">    raise exception '(%)', sqlerrm;</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt">end;</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">$$</span></font><p class="MsoPlainText"><font face="Courier New" size="2"><span style="font-size:
10.0pt">language 'plpgsql' security definer;</span></font><p class="MsoPlainText"><font face="Courier New"
size="2"><spanstyle="font-size: 
10.0pt"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I thought I would share and it works rather well.  Maybe someone could enhance this concept to
includeit with the core database to provide autonomous transactions.</span></font><p class="MsoPlainText"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoPlainText"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Jon</span></font></div>

Re: autonomous transactions

From
Neil Conway
Date:
On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
> Maybe someone could enhance this concept to include it with the core
> database to provide autonomous transactions.

I agree that autonomous transactions would be useful, but doing them via
dblink is a kludge. If we're going to include anything in the core
database, it should be done properly (i.e. as an extension to the
existing transaction system).

-Neil




Re: autonomous transactions

From
Alvaro Herrera
Date:
Neil Conway wrote:
> On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
> > Maybe someone could enhance this concept to include it with the core
> > database to provide autonomous transactions.
> 
> I agree that autonomous transactions would be useful, but doing them via
> dblink is a kludge. If we're going to include anything in the core
> database, it should be done properly (i.e. as an extension to the
> existing transaction system).

Agreed.  I think Pavel Stehule was doing some experiments with them, I
don't know if he got anywhere.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support


Re: autonomous transactions

From
"Roberts, Jon"
Date:
> On Tue, 2008-01-22 at 10:02 -0600, Roberts, Jon wrote:
> > Maybe someone could enhance this concept to include it with the core
> > database to provide autonomous transactions.
> 
> I agree that autonomous transactions would be useful, but doing them via
> dblink is a kludge. 

Kludge or hack but I agree!

> If we're going to include anything in the core
> database, it should be done properly (i.e. as an extension to the
> existing transaction system).

I agree!  That is why I said "someone could enhance this concept to include
it with the core database".  




Jon


Re: autonomous transactions

From
"Pavel Stehule"
Date:
>
> Agreed.  I think Pavel Stehule was doing some experiments with them, I
> don't know if he got anywhere.
>

I did only first research. Any hack is possible - you can stack
current transaction, but real implementation needs similar work like
nested transaction :( and it is too low level for me. And some code
cleaning is necessary. There are global variables.

And there is most important question about data visibility - is
autonomous transaction independent on main transaction (isolation)?
You have to thing about deadlock, about reference integrity, etc. This
task isn't simple.

Pavel



> --
> Alvaro Herrera                                http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>


Re: autonomous transactions

From
Simon Riggs
Date:
On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
> >
> > Agreed.  I think Pavel Stehule was doing some experiments with them, I
> > don't know if he got anywhere.
> >
> 
> I did only first research. Any hack is possible - you can stack
> current transaction, but real implementation needs similar work like
> nested transaction :( and it is too low level for me. And some code
> cleaning is necessary. There are global variables.
> 
> And there is most important question about data visibility - is
> autonomous transaction independent on main transaction (isolation)?
> You have to thing about deadlock, about reference integrity, etc. This
> task isn't simple.

Yes, I think autonomous transactions should be on the TODO. They're
useful for
- error logging
- auditing
- creating new partitions automatically

Plus I think we'd be able to improve the code for CREATE INDEX under
HOT, and probably a few other wrinkly bits of code.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: autonomous transactions

From
Neil Conway
Date:
On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
> And there is most important question about data visibility - is
> autonomous transaction independent on main transaction (isolation)?

>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.

On Wed, 2008-01-23 at 08:13 +0000, Simon Riggs wrote:
> Yes, I think autonomous transactions should be on the TODO. They're
> useful for
> - error logging
> - auditing
> - creating new partitions automatically

I think they would also be useful to implement procedures that perform
DDL operations or COMMITs / ROLLBACKs.

-Neil




Re: autonomous transactions

From
"Pavel Stehule"
Date:
On 23/01/2008, Simon Riggs <simon@2ndquadrant.com> wrote:
> On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
> > >
> > > Agreed.  I think Pavel Stehule was doing some experiments with them, I
> > > don't know if he got anywhere.
> > >
> >
> > I did only first research. Any hack is possible - you can stack
> > current transaction, but real implementation needs similar work like
> > nested transaction :( and it is too low level for me. And some code
> > cleaning is necessary. There are global variables.
> >
> > And there is most important question about data visibility - is
> > autonomous transaction independent on main transaction (isolation)?
> > You have to thing about deadlock, about reference integrity, etc. This
> > task isn't simple.
>
> Yes, I think autonomous transactions should be on the TODO. They're
> useful for
> - error logging
> - auditing
> - creating new partitions automatically
>

I worked on workflow implementation only in stored procedures. Without
autonomous transaction you cannot implement some models. And it's
usable for AQ.

> Plus I think we'd be able to improve the code for CREATE INDEX under
> HOT, and probably a few other wrinkly bits of code.
>
> --
>   Simon Riggs
>   2ndQuadrant  http://www.2ndQuadrant.com
>
>


Re: autonomous transactions

From
Gregory Stark
Date:
"Neil Conway" <neilc@samurai.com> writes:

> On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
>> And there is most important question about data visibility - is
>> autonomous transaction independent on main transaction (isolation)?
>
>>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.

I think the hard part would be error handling. You have to be able to catch
any errors and resume the outer transaction.

--  Gregory Stark EnterpriseDB          http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support!


Re: autonomous transactions

From
"Gokulakannan Somasundaram"
Date:


I think the hard part would be error handling. You have to be able to catch
any errors and resume the outer transaction.
 
I think this is not right. Autonomous transactions are used as soon as you catch a error in order to log them. It can be used even for auditing. But resuming the outer transaction etc should not be on the plate of autonomous transactions. I am making an example here ...

Suppose you want to write a code which captures the attempt to change the sensitive information, and also fails the change made to sensitive information. In order to fail the change, we might need to rollback the transaction, which would prevent the attempt being logged. So if we have autonomous audit transaction, it will commit irrespective of the rollback which happened to the original transaction

The Audit transaction, which is a autonomous transaction need not catch any error and resume the outer transaction.

Thanks,
Gokul.

Re: autonomous transactions

From
Alvaro Herrera
Date:
Gokulakannan Somasundaram escribió:

> The Audit transaction, which is a autonomous transaction need not catch any
> error and resume the outer transaction.

What if the logging fails, say because you forgot to create the audit
table?

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


Re: autonomous transactions

From
Simon Riggs
Date:
On Wed, 2008-01-23 at 00:26 -0800, Neil Conway wrote:
> On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
> > And there is most important question about data visibility - is
> > autonomous transaction independent on main transaction (isolation)?
> 
> >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.

--  Simon Riggs 2ndQuadrant  http://www.2ndQuadrant.com



Re: autonomous transactions

From
Neil Conway
Date:
On Wed, 2008-01-23 at 09:30 +0000, Gregory Stark wrote:
> I think the hard part would be error handling. You have to be able to catch
> any errors and resume the outer transaction.

I agree that you'd need to do this, but I don't follow why it would be
particularly difficult. You essentially have a stack of active
transactions (since one autonomous transaction can start another
autonomous transaction, and so forth). If you encounter an error in the
current transaction, you abort it as normal, pop the stack, and resume
execution of the originating transaction.

I think the hard part is fixing the parts of the backend that assume
that a single process can only have a single top-level transaction in
progress at a given time.

-Neil




Re: autonomous transactions

From
Tom Lane
Date:
Simon Riggs <simon@2ndquadrant.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.
        regards, tom lane


Re: autonomous transactions

From
"Gokulakannan Somasundaram"
Date:


On Jan 24, 2008 2:46 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
Gokulakannan Somasundaram escribió:

> The Audit transaction, which is a autonomous transaction need not catch any
> error and resume the outer transaction.

What if the logging fails, say because you forgot to create the audit
table?
I get it now...

--
Alvaro Herrera                                 http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Re: autonomous transactions

From
"Jonah H. Harris"
Date:
On Jan 23, 2008 10:06 PM, Gokulakannan Somasundaram <gokul007@gmail.com> wrote:
> On Jan 24, 2008 2:46 AM, Alvaro Herrera <alvherre@commandprompt.com> wrote:
> > > The Audit transaction, which is a autonomous transaction need not catch
> any
> > > error and resume the outer transaction.
> >
> > What if the logging fails, say because you forgot to create the audit
> > table?
> >
> I get it now...

Autonomous transactions are, umm, autonomous.  The calling transaction
doesn't know about or care whether the autonomous transaction succeeds
or fails for any reason.

-- 
Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324
EnterpriseDB Corporation                | fax: 732.331.1301
499 Thornall Street, 2nd Floor          | jonah.harris@enterprisedb.com
Edison, NJ 08837                        | http://www.enterprisedb.com/


Re: autonomous transactions

From
Robert Treat
Date:
On Tuesday 22 January 2008 11:02, Roberts, Jon wrote:
> I really needed this functionality in PostgreSQL.  A common use for
> autonomous transactions is error logging.  I want to log sqlerrm in a
> function and raise an exception so the calling application knows there is
> an error and I have it logged to a table.
>
>
>
> I figured out a way to "hack" an autonomous transaction by using a dblink
> in a function and here is a simple example:
>
>
>

This is an enhanced version of the "hack", maybe it will be of some help... 
https://labs.omniti.com/trac/pgsoltools/browser/trunk/autonomous_logging_tool

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL


Re: autonomous transactions

From
Decibel!
Date:
On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:
> Simon Riggs <simon@2ndquadrant.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.
--
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Re: autonomous transactions

From
"Roberts, Jon"
Date:
> On Wed, Jan 23, 2008 at 05:50:02PM -0500, Tom Lane wrote:
> > Simon Riggs <simon@2ndquadrant.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.
> --

You are describing an uncommitted transaction and not an autonomous
transaction.  Transactions in Oracle are not automatically committed
like they are in PostgreSQL.

Here is a basic example of an autonomous transaction:

create or replace procedure pr_log_error (p_error_message
errorlog.message%type) is  pragma autonomous_transaction;
begin insert   into errorlog        (log_user,         log_time,         error_message) values (user,
sysdate(),        p_error_message); commit; 
exception when others then   rollback;   raise;
end;

And then you can call it from a procedure like this:

create or replace procedure pr_example is
begin null;--do some work commit;  --commit the work
exception when others   pr_log_error(p_error_message => sqlerrm);   rollback;   raise;
end;

The autonomous transaction allows me to insert and commit a record in
different transaction than the calling procedure so the calling
procedure can rollback or commit.

You can also remove the commit/rollback from pr_example and instead do
it from the anonymous block that calls it.  I just added it to make it
clear that it is a different transaction than the error logging
transaction.



Jon


Re: autonomous transactions

From
Hans-Juergen Schoenig
Date:

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@2ndquadrant.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


Re: autonomous transactions

From
Bruce Momjian
Date:
Simon Riggs wrote:
> On Tue, 2008-01-22 at 20:53 +0100, Pavel Stehule wrote:
> > >
> > > Agreed.  I think Pavel Stehule was doing some experiments with them, I
> > > don't know if he got anywhere.
> > >
> > 
> > I did only first research. Any hack is possible - you can stack
> > current transaction, but real implementation needs similar work like
> > nested transaction :( and it is too low level for me. And some code
> > cleaning is necessary. There are global variables.
> > 
> > And there is most important question about data visibility - is
> > autonomous transaction independent on main transaction (isolation)?
> > You have to thing about deadlock, about reference integrity, etc. This
> > task isn't simple.
> 
> Yes, I think autonomous transactions should be on the TODO. They're
> useful for
> - error logging
> - auditing
> - creating new partitions automatically
> 
> Plus I think we'd be able to improve the code for CREATE INDEX under
> HOT, and probably a few other wrinkly bits of code.

Added to TODO:

* Add anonymous transactions
 http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php


--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: autonomous transactions

From
Bruce Momjian
Date:
Bruce Momjian wrote:
> > Plus I think we'd be able to improve the code for CREATE INDEX under
> > HOT, and probably a few other wrinkly bits of code.
> 
> Added to TODO:
> 
> * Add anonymous transactions
> 
>   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php

Sorry, updated to "Add _autonomous_ transactions".  (The one time I
don't cut/paste and I get it wrong.)

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://postgres.enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: autonomous transactions

From
Josh Berkus
Date:
All,

> 
> Added to TODO:
> 
> * Add anonymous transactions
> 
>   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
>

IMHO, autonomous transactions should be part of a package with a 
spec-compliant CREATE PROCEDURE statement.    That is, the difference 
between PROCEDURES and FUNCTIONS would be that:

-- PROCs have autonomous transactions
-- PROCs have to be excuted with CALL, and can't go in a query
-- PROCs don't necessarily return a result

--Josh Berkus


Re: autonomous transactions

From
Alvaro Herrera
Date:
Josh Berkus escribió:
> All,
>
>>
>> Added to TODO:
>>
>> * Add anonymous transactions
>>
>>   http://archives.postgresql.org/pgsql-hackers/2008-01/msg00893.php
>
> IMHO, autonomous transactions should be part of a package with a  
> spec-compliant CREATE PROCEDURE statement.

IMHO we should try to get both things separately, otherwise we will
never get either.

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support