Thread: autonomous transactions
<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>
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
> > 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@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 > >
"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.
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ó:What if the logging fails, say because you forgot to create the audit
> The Audit transaction, which is a autonomous transaction need not catch any
> error and resume the outer transaction.
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@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 arecompletely independent of the transaction that originates them -- theytake a new database snapshot. This means that uncommitted changes in theoriginating 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 implementationpath for these too.Oracle has a feature where you can effectively save a session and returnto it. For example, if filling out a multi-page web form, you could savestate in the database between those calls. I'm assuming that they usethat capability for their autonomous transactions; save the currentsession to the stack, clone it, run the autonomous transaction, thenrestore 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@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