Autonomous transaction

My understanding is that this feature won't be available even in v8 of
postgres. Nested transactions will roll back with the main transaction
when it's rolled back...

You could take a look at contrib/dblink because apparantly you can open
a connection to another (and probably the same) DB, and then write
your logging information that way (ie within a function or session).
Anything you do using the dblink will not be rolled back.

Although I haven't used it, I don't think that dblink is not as simple
to use as Oracle's database link with the "@dblink" notation. So you'll
need to check the docs to see how it works - the README looks OK though.

Maybe someone else can suggest a better approach?

John Sidney-Woollett

Daniel Daoust wrote:

At 11:18 AM -0400 9/13/04, Daniel Daoust wrote:

Hi, knowing that "autonomous transaction" (Oracle
concept of) are not yet implemented in PostgreSQL, has
anyone found a work-around. I need to preserve
database states from a potential rollback and then log
them inside database tables.

What I use for this is multiple DB connections. A rollback on one
connection doesn't (generally) affect any of the other connections,
so you can keep things separate and safe from rollbacks.

Note that you can deadlock yourself this way.
--
Dan

--------------------------------------it's like this-------------------
Dan Sugalski even samurai
dan@sidhe.org have teddy bears and even
teddy bears get drunk

Daniel Daoust <daoustd_ca@yahoo.ca> writes:

Hi, knowing that "autonomous transaction" (Oracle
concept of) are not yet implemented in PostgreSQL, has
anyone found a work-around. I need to preserve
database states from a potential rollback and then log
them inside database tables.

Postgres goes to great lengths to ensure this is impossible :)

What about:

1) using memory structures to hold the info, then
commit to the database just before exit (after the
rollback occurred).

Well this option in your application layer is how I would do it as it's the
simplest. However:

3) send messages to a daemon/database job that will
write to the tables using another
connection/transaction ???

This option is doable entirely in the database if that's your goal. You can do
this using dblink which is the contrib directory.

It would probably be quite simple to implement, though the most complex in
another sense. With twice as many connections it could be hard to determine
where slowdowns are starting for example. And Better make sure your error
logging connections don't trigger any errors...

--
greg

You could take a look at contrib/dblink because
apparantly you can open
a connection to another (and probably the same)
DB, and then write
your logging information that way (ie within a
function or session).
Anything you do using the dblink will not be rolled
back.

Superb! It took me a little while to get it to work
but ���dblink��� does perfectly what I need it for and
more.

Thanks so much John and, to everyone else.

Daniel Daoust

______________________________________________________________________
Post your free ad now! http://personals.yahoo.ca