Thread: Transaction IDs not the same in same transaction?
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. Here's the query I ran: BEGIN; UPDATE partners SET partner_name = 'partner #5', activity_status_id = 1 WHERE partner_id = 5; UPDATE partners SET partner_name = 'partner #7' WHERE partner_id = 3; COMMIT; Should that have had the same txn ID for both of those? The GetCurrentTransactionID call occurs in a trigger, would that have an impact on the ID? Thanks, Steve
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?