Transaction IDs not the same in same transaction?

On Sat, Oct 22, 2005 at 01:30:32PM -0700, Steve V wrote:

So I was finally able to get a compiled binary for the code in this
thread(thanks Magnus):
http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php

So everything seemed to be fine with my GetCurrentTransactionID()
function call returning the txn ID for each query I would run(as far
as I could tell). Then I tried running a txn with multiple queries,
and instead of just having one txn ID, each query had it's own. Does
that make any sense? I was under the impression that a regular
transaction block would have one txn ID assigned to it for its
duration.

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?

--
Michael Fuhr

On 10/22/05, Michael Fuhr <mike@fuhr.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

Hi,

On Sat, 22 Oct 2005, Steve V wrote:

On 10/22/05, Michael Fuhr <mike@fuhr.org> wrote:

[snipp]

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.

we use a touple of (now(),session_backend_pid()) in our logging triggers
to identify stuff done in the same transaction.

now() will be frozen during the transaction and session_backend_pid()
disambuguates this for cases where two transaction could have the
same start time.

Greetings
Christian

--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136

Steve V <dndlists@gmail.com> writes:

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.

I think that last assertion is probably in error, but without seeing the
full text of the trigger it's difficult to be sure what's going on here.

regards, tom lane

Steve V <dndlists@gmail.com> writes:

Well it doesn't explicitly do it, so the only thing I could think of
is that it has something to do with the pltcl spi_exec calls?

Ah, you had not even mentioned that the trigger was in pltcl.
spi_exec runs everything in subtransactions as of 8.0, so that
it can catch errors and propagate them back as Tcl errors.

regards, tom lane

Hi,

On Sun, 23 Oct 2005, Christian Kratzer wrote:

Hi,

On Sat, 22 Oct 2005, Steve V wrote:

On 10/22/05, Michael Fuhr <mike@fuhr.org> wrote:

[snipp]

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.

we use a touple of (now(),session_backend_pid()) in our logging triggers
to identify stuff done in the same transaction.

now() will be frozen during the transaction and session_backend_pid()
disambuguates this for cases where two transaction could have the
same start time.

I of course meant a tuple of now() and pg_backend_pid().

session_backend_pid is the name of a column in our session table.

Greetings
Christian

--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136

Ah, you had not even mentioned that the trigger was in pltcl.
spi_exec runs everything in subtransactions as of 8.0, so that
it can catch errors and propagate them back as Tcl errors.

Okay, so are there any possible options? Would GetTopTransactionId()
work, as Michael suggested further up?

Thanks,
Steve

On Sun, Oct 23, 2005 at 02:00:47PM -0700, Steve V wrote:

Ah, you had not even mentioned that the trigger was in pltcl.
spi_exec runs everything in subtransactions as of 8.0, so that
it can catch errors and propagate them back as Tcl errors.

Okay, so are there any possible options? Would GetTopTransactionId()
work, as Michael suggested further up?

I just did some tests with a pltcl function that uses spi_exec in
an AFTER trigger and GetTopTransactionId() returned the same value
for each of several inserts and updates that were in the same
transaction. However, I can't say whether this will work for you
without seeing your code. It would be easier for us to help -- and
you'd get a solution sooner -- if you'd post a minimal but complete
example that shows what you're doing and that exhibits the behavior
you're seeing.

--
Michael Fuhr

I just did some tests with a pltcl function that uses spi_exec in
an AFTER trigger and GetTopTransactionId() returned the same value
for each of several inserts and updates that were in the same
transaction. However, I can't say whether this will work for you
without seeing your code. It would be easier for us to help -- and
you'd get a solution sooner -- if you'd post a minimal but complete
example that shows what you're doing and that exhibits the behavior
you're seeing.

Okay, so using GetTopTransactionId() will do the trick it sounds like.
To see all of what's happening, just place the pltcl function that I
posted earlier in this thread as an AFTER trigger, and run code
similar to that which I showed in my first post to start the thread.

Which library is GetTopTransactionId() available in? Do I need to
compile an library on my own that will expose it?

On Sun, 2005-23-10 at 16:35 -0700, Steve V wrote:

Which library is GetTopTransactionId() available in?

It's defined in the backend executable, as is GetCurrentTransactionId().
A similar wrapper function to the one shown here:

http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php

should work. But I agree with the other folks in this thread who have
questioned whether this is a good idea: backend APIs are known to change
significantly between releases, and making assumptions about how they
behave seems like asking for trouble to me.

-Neil

On Sun, Oct 23, 2005 at 04:35:14PM -0700, Steve V wrote:

Okay, so using GetTopTransactionId() will do the trick it sounds like.
To see all of what's happening, just place the pltcl function that I
posted earlier in this thread as an AFTER trigger, and run code
similar to that which I showed in my first post to start the thread.

I haven't received that message yet, nor do I see it in the archives.
One of Tom Lane's replies appeared to quote from it -- did you send
it just to him? If you copied the list then the message might be
stuck somewhere.

Which library is GetTopTransactionId() available in? Do I need to
compile an library on my own that will expose it?

See "C-Language Functions" in the documentation, especially "Compiling
and Linking Dynamically-Loaded Functions" and "Extension Building
Infrastructure":

http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#DFUNC
http://www.postgresql.org/docs/8.0/interactive/xfunc-c.html#XFUNC-C-PGXS

What platform are you using? If Windows then see also the User Comments
at the bottom of the above pages.

Where did you get the GetCurrentTransactionId() code? Your first
message suggests that somebody named Magnus might have sent it to
you. If you don't have the means to build the code yourself then
perhaps that person could help again.

--
Michael Fuhr

I haven't received that message yet, nor do I see it in the archives.
One of Tom Lane's replies appeared to quote from it -- did you send
it just to him? If you copied the list then the message might be
stuck somewhere.

Yeah, I accidentally only sent it to Tom. Sorry about that.

What platform are you using? If Windows then see also the User Comments
at the bottom of the above pages.

Where did you get the GetCurrentTransactionId() code? Your first
message suggests that somebody named Magnus might have sent it to
you. If you don't have the means to build the code yourself then
perhaps that person could help again.

Yeah, I'm on a Win32 platform for the time being. The
GetCurrentTransactionID() code was posted in response to one of my
threads a few months back. Magnus Hagander from the hackers-win32 list
was the one that was kind enough to compile the code for me. I'll see
if he doesn't mind whipping up another library for me.

Thanks,
Steve

It's defined in the backend executable, as is GetCurrentTransactionId().
A similar wrapper function to the one shown here:

http://archives.postgresql.org/pgsql-general/2005-06/msg00709.php

should work. But I agree with the other folks in this thread who have
questioned whether this is a good idea: backend APIs are known to change
significantly between releases, and making assumptions about how they
behave seems like asking for trouble to me.

I agree with all of you as well that using backend APIs is not the
most continually reliable way of doing this. Unfortunately the
requirement stands that I need to audit all actions that occur in a
single transaction, as one group.

Christian's method seems like it should work properly. Does anyone see
any problems with doing it in the fashion mentioned by him?