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