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