Re: Autonomous Transactions

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)