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
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
Import Notes
Resolved by subject fallback
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
Show quoted text
--
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
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@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.
Show quoted text
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@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!
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@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
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...
Show quoted text
--
Alvaro Herrera
http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
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/
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@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
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
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
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 automaticallyPlus 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. +
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. +
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