Thread: Autonomous Transactions
I'm looking for a way to enable a function to commit a unit of work that does not affect the caller's transaction. I'm coming from the Oracle world where I've used the "autonomous_transaction" pragma of PL/SQL to do this. I'm new to Postgres, but I'm hopeful that I can move our systems from Oracle. I realize that a plpgsql function cannot commit, and that a rollback happens automatically when an exception is raised. Beyond this, I'm not seeing what transaction management tools I have within a function. Maybe there is a standard idiom out there that employs nested function calls or something. I'm willing to use a different language, or even the libpq API if necessary.
On Wed, Jun 01, 2005 at 03:38:01PM +0000, Matt Miller wrote: > I'm looking for a way to enable a function to commit a unit of work that > does not affect the caller's transaction. I'm coming from the Oracle > world where I've used the "autonomous_transaction" pragma of PL/SQL to > do this. I'm new to Postgres, but I'm hopeful that I can move our > systems from Oracle. > > I realize that a plpgsql function cannot commit, and that a rollback > happens automatically when an exception is raised. Beyond this, I'm not > seeing what transaction management tools I have within a function. > Maybe there is a standard idiom out there that employs nested function > calls or something. In 8.0 you can use the EXCEPTION clause. This uses savepoints internally, so a given BEGIN/END block is effectively rolled back and you can continue with the transaction. (Note that savepoints and EXCEPTIONs can be nested.) > I'm willing to use a different language, or even the libpq API if > necessary. If you really need autonomous transactions, you can establish an independent connection within a function in, say, PL/Perl or PL/Python. For example in PL/PerlU you can load the DBI driver and then use DBD::Pg to create another connection. Any command and transaction you initiate on that other connection will be, of course, completely separate and independent from the connection the function is executing in. -- Alvaro Herrera (<alvherre[a]surnet.cl>) Licensee shall have no right to use the Licensed Software for productive or commercial use. (Licencia de StarOffice 6.0 beta)
On Wed, 2005-06-01 at 10:52, Alvaro Herrera wrote: > On Wed, Jun 01, 2005 at 03:38:01PM +0000, Matt Miller wrote: > > I'm willing to use a different language, or even the libpq API if > > necessary. > > If you really need autonomous transactions, you can establish an > independent connection within a function in, say, PL/Perl or PL/Python. > For example in PL/PerlU you can load the DBI driver and then use DBD::Pg > to create another connection. Any command and transaction you initiate > on that other connection will be, of course, completely separate and > independent from the connection the function is executing in. I've done the same thing here at work with dblink. Works a charm.
> > a way to enable a function to commit a unit of work that > > does not affect the caller's transaction. > you can establish an independent connection within a function in, say, > PL/Perl or PL/Python. Okay, multiple connections seems to be my best shot. However, I would like standard developers to be able to stay in PL/pgSQL and SQL. If each of our anonymous transactions needs to be coded in PL/Perl then people are not as happy. (Silly people.) So, can I write some dirty little utility functions (in PL/Perl, C, whatever) that a PL/pgSQL caller use to somehow switch connections? The way our apps are currently structured I'm picturing that each app's logical connection is actually two physical connections: the main thread of control happens on one connection, and autonomous transactions happen on the other connection.
On Fri, 2005-06-03 at 15:10, Matt Miller wrote: > > > a way to enable a function to commit a unit of work that > > > does not affect the caller's transaction. > > > you can establish an independent connection within a function in, say, > > PL/Perl or PL/Python. > > Okay, multiple connections seems to be my best shot. However, I would > like standard developers to be able to stay in PL/pgSQL and SQL. If > each of our anonymous transactions needs to be coded in PL/Perl then > people are not as happy. (Silly people.) > > So, can I write some dirty little utility functions (in PL/Perl, C, > whatever) that a PL/pgSQL caller use to somehow switch connections? The > way our apps are currently structured I'm picturing that each app's > logical connection is actually two physical connections: the main thread > of control happens on one connection, and autonomous transactions happen > on the other Is dblink a possible answer? (it's a contrib package.)
> > a way to enable a function to commit a unit of work that > > does not affect the caller's transaction. > Is dblink a possible answer? (it's a contrib package.) Very interesting. When you earlier mentioned dblink I found only DBLink-TDS on pgFoundry, and I dismissed it since I'm not accessing MSSQL. So, your idea is that I accomplish autonomous transactions in PL/pgSQL by just using dblink_connect, dblink_open, dblink_exec, and dblink_close? This looks promising. I'll give it a try.
> > a way to enable a function to commit a unit of work that > > does not affect the caller's transaction. > accomplish autonomous transactions in PL/pgSQL > by just using dblink_connect, dblink_open, dblink_exec, > and dblink_close? My initial tests lead me to believe that dblink is a simple and effective way to get anonymous transactions. Thanks.
On Fri, 2005-06-03 at 18:04, Matt Miller wrote: > > > a way to enable a function to commit a unit of work that > > > does not affect the caller's transaction. > > > accomplish autonomous transactions in PL/pgSQL > > by just using dblink_connect, dblink_open, dblink_exec, > > and dblink_close? > > My initial tests lead me to believe that dblink is a simple and > effective way to get anonymous transactions. We've used them at work for autonomous transactions outside of plpgsql. Not sure of how well they would or wouldn't work inside plpgsql.