Thread: Autonomous transaction
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
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
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