Autonomous transaction

[Daniel Daoust]

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 about:

1) using memory structures to hold the info, then
commit to the database just before exit (after the
rollback occurred).
2) write to the file system, then extract and then
commit to the database just before exit (after the
rollback occurred).
3) send messages to a daemon/database job that will
write to the tables using another
connection/transaction ???
4) ....

Any comment/suggestion would be greatly appreciated.

Note: I am using PostgreSQL 7.4.3 on Linux.

Thanks,

Daniel

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[John Sidney-Woollett]

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:

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 about:

1) using memory structures to hold the info, then
commit to the database just before exit (after the
rollback occurred).
2) write to the file system, then extract and then
commit to the database just before exit (after the
rollback occurred).
3) send messages to a daemon/database job that will
write to the tables using another
connection/transaction ???
4) ....

Any comment/suggestion would be greatly appreciated.

Note: I am using PostgreSQL 7.4.3 on Linux.

Thanks,

Daniel

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Dan Sugalski]

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
da*@sidhe.org have teddy bears and even
teddy bears get drunk

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Greg Stark]

Daniel Daoust <da********@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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Daniel Daoust]

> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster