Horton

Thread

  1. autonomous transactions

    Roberts, Jon <jon.roberts@asurion.com> — 2008-01-22T16:02:44Z

    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:

 

create or replace function fn_log_error(p_function varchar, p_location int,
p_error varchar) returns void as

$$

declare

  v_sql varchar;

  v_return varchar;

  v_error varchar;

begin

  perform dblink_connect('connection_name', 'dbname=...');

  

  v_sql := 'insert into error_log (function_name, location, error_message,
error_time) values (''' || p_function_name || ''', ' || 

           p_location || ', ''' || p_error || ''', clock_timestamp())';

  

  select * from dblink_exec('connection_name', v_sql, false) into v_return;

 

  --get the error message

  select * from dblink_error_message('connection_name') into v_error;

 

  if position('ERROR' in v_error) > 0 or position('WARNING' in v_error) > 0
then

    raise exception '%', v_error;

  end if;

 

  perform dblink_disconnect('connection_name');

 

exception

  when others then

    perform dblink_disconnect('connection_name');

    raise exception '(%)', sqlerrm;

end;

$$

language 'plpgsql' security definer;

 

I thought I would share and it works rather well.  Maybe someone could
enhance this concept to include it with the core database to provide
autonomous transactions.

 

 

Jon

  2. Re: autonomous transactions

    Neil Conway <neilc@samurai.com> — 2008-01-22T18:53:53Z

    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

  3. Re: autonomous transactions

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-01-22T19:04:16Z

    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

  4. Re: autonomous transactions

    Pavel Stehule <pavel.stehule@gmail.com> — 2008-01-22T19:53:59Z

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

  5. Re: autonomous transactions

    Simon Riggs <simon@2ndquadrant.com> — 2008-01-23T08:13:55Z

    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

  6. Re: autonomous transactions

    Neil Conway <neilc@samurai.com> — 2008-01-23T08:26:52Z

    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

  7. Re: autonomous transactions

    Pavel Stehule <pavel.stehule@gmail.com> — 2008-01-23T09:05:50Z

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

  8. Re: autonomous transactions

    Gregory Stark <stark@enterprisedb.com> — 2008-01-23T09:30:54Z

    "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!

  9. Re: autonomous transactions

    Gokulakannan Somasundaram <gokul007@gmail.com> — 2008-01-23T20:47:50Z

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

  10. Re: autonomous transactions

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-01-23T21:16:47Z

    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.

  11. Re: autonomous transactions

    Simon Riggs <simon@2ndquadrant.com> — 2008-01-23T21:32:59Z

    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

  12. Re: autonomous transactions

    Neil Conway <neilc@samurai.com> — 2008-01-23T22:35:32Z

    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

  13. Re: autonomous transactions

    Tom Lane <tgl@sss.pgh.pa.us> — 2008-01-23T22:50:02Z

    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

  14. Re: autonomous transactions

    Gokulakannan Somasundaram <gokul007@gmail.com> — 2008-01-24T03:06:56Z

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

  15. Re: autonomous transactions

    Jonah H. Harris <jonah.harris@gmail.com> — 2008-01-24T03:35:22Z

    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/

  16. Re: autonomous transactions

    Robert Treat <xzilla@users.sourceforge.net> — 2008-01-24T22:37:04Z

    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

  17. Re: autonomous transactions

    Decibel! <decibel@decibel.org> — 2008-01-25T06:27:40Z

    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

  18. Re: autonomous transactions

    Roberts, Jon <jon.roberts@asurion.com> — 2008-01-28T21:33:05Z

    > 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

  19. Re: autonomous transactions

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2008-01-29T07:52:39Z

    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

  20. Re: autonomous transactions

    Bruce Momjian <bruce@momjian.us> — 2008-01-29T16:12:25Z

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

  21. Re: autonomous transactions

    Bruce Momjian <bruce@momjian.us> — 2008-01-29T16:14:51Z

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

  22. Re: autonomous transactions

    Josh Berkus <josh@agliodbs.com> — 2008-01-30T17:04:28Z

    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

  23. Re: autonomous transactions

    Alvaro Herrera <alvherre@commandprompt.com> — 2008-01-30T17:11:14Z

    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