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

> 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