SEO

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



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

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


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



---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend