Re: autonomous transactions

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

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

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

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

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

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

On 23/01/2008, Simon Riggs <simon(at)2ndquadrant(dot)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
>
>

"Neil Conway" <neilc(at)samurai(dot)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!

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

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.

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

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

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.

regards, tom lane

On Jan 24, 2008 2:46 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)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.
>

On Jan 23, 2008 10:06 PM, Gokulakannan Somasundaram <gokul007(at)gmail(dot)com> wrote:
> On Jan 24, 2008 2:46 AM, Alvaro Herrera <alvherre(at)commandprompt(dot)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(dot)harris(at)enterprisedb(dot)com
Edison, NJ 08837 | http://www.enterprisedb.com/

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

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.
--
Decibel!, aka Jim C. Nasby, Database Architect decibel(at)decibel(dot)org
Give your computer some brain candy! www.distributed.net Team #1828

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

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

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

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

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

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