Re: Transaction IDs not the same in same transaction?

On 10/22/05, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
>
> It makes sense if you're running PostgreSQL 8.0 or later and are
> using subtransactions, whether explicitly or implicitly. The example
> you posted didn't show the trigger definition or function -- does
> the function do any error trapping? Maybe you need GetTopTransactionId()
> instead of GetCurrentTransactionID().
>
> Why do you need the transaction ID at all? Might the xmin system
> column serve your purpose?

Using 8.0.4 right now. No subtransactions that I know of. The trigger
call is an AFTER I/U/D. The function called is somewhat lengthy, but
it does not explicitly intiate any transactions, and does not perform
any error trapping.

Maybe GetTopTransactionId() is a better function call. I have no
experience with it though. I played around with
GetCurrentTransactionID(), and noticed the following behavior. If I
add an extra integer field to my table to hold txn IDs; I get the same
txn ID stored if both of my updates in the initial post call
GetCurrentTransactionID() as part of their updated field lists.
However, the txn IDs in from each of the triggers is different. So
with the previous scenario, I end up with a total of three distinct
txn IDs.

I'm using this for an auditing script, and want to use the txn ID to
indicate an atomic set of changes(the pg txn ID is mapped to my own
txn ID to avoid wraparound issues). I would rather not use xmin, as it
is not available directly in any of the triggers(as far as I know). So
I would have to construct select statements on the fly, and I would
rather not do that. I'm open to suggestions though.

Thanks,
Steve