Thread: Current transaction ID?
Simple question I'm sure, but how can I get the transaction ID that a query is currently running in? I can find all kinds of documentation talking about transaction ids, but nothing about how to get the current one. Thanks, Steve
Steve - DND wrote: > Simple question I'm sure, but how can I get the transaction ID that a query > is currently running in? I can find all kinds of documentation talking about > transaction ids, but nothing about how to get the current one. You can look at the xmin of a tuple you created or an xmax of a tuple you deleted. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> Steve - DND wrote: > > Simple question I'm sure, but how can I get the transaction ID > that a query > > is currently running in? I can find all kinds of documentation > talking about > > transaction ids, but nothing about how to get the current one. > > You can look at the xmin of a tuple you created or an xmax of a tuple > you deleted. Let's pretend I don't know how to do that. :) Do you have a link to a page in the manual that describes this process, or can you give me a quick runthrough of what you're talking about? Thanks, Steve
Steve - DND wrote: > > Steve - DND wrote: > > > Simple question I'm sure, but how can I get the transaction ID > > that a query > > > is currently running in? I can find all kinds of documentation > > talking about > > > transaction ids, but nothing about how to get the current one. > > > > You can look at the xmin of a tuple you created or an xmax of a tuple > > you deleted. > > Let's pretend I don't know how to do that. :) Do you have a link to a page > in the manual that describes this process, or can you give me a quick > runthrough of what you're talking about? Every row has an invisible xmin/xmax columns that represent the xid of the row inserted, updated, or deleted. Why do you need to know the xid is a better question. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> > Let's pretend I don't know how to do that. :) Do you have a > link to a page > > in the manual that describes this process, or can you give me a quick > > runthrough of what you're talking about? > > Every row has an invisible xmin/xmax columns that represent the xid of > the row inserted, updated, or deleted. Why do you need to know the xid > is a better question. Hmm...not quite what I was hoping for. I'm looking to add the ability to my auditing scripts to know what happened in a given transaction. Right now, it's just table based, so while it still logs all of the changes, it does it on a per table basis, not associating changes on two tables as being part of the same action. I can currently guess as to what was changed at one time by using the current user that's recorded, and the time at which the changes occurred, but it's not foolproof. Steve
Steve - DND wrote: > > > Let's pretend I don't know how to do that. :) Do you have a > > link to a page > > > in the manual that describes this process, or can you give me a quick > > > runthrough of what you're talking about? > > > > Every row has an invisible xmin/xmax columns that represent the xid of > > the row inserted, updated, or deleted. Why do you need to know the xid > > is a better question. > > Hmm...not quite what I was hoping for. I'm looking to add the ability to my > auditing scripts to know what happened in a given transaction. Right now, > it's just table based, so while it still logs all of the changes, it does it > on a per table basis, not associating changes on two tables as being part of > the same action. > > I can currently guess as to what was changed at one time by using the > current user that's recorded, and the time at which the changes occurred, > but it's not foolproof. Well, you can take the xmin of a row and look for other rows with the same xmin, either in the same table or in different tables. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> > Well, you can take the xmin of a row and look for other rows with the > same xmin, either in the same table or in different tables. Do these columns appear inside the NEW/OLD variables for triggers, or from some other method within a trigger? Steve
Steve - DND napisał(a):
> Simple question I'm sure, but how can I get the transaction ID that a query
> is currently running in? I can find all kinds of documentation talking about
> transaction ids, but nothing about how to get the current one.
>
You can compile attached function - this will return current transaction id.
#include "postgres.h"
#include "access/xact.h"
Datum getcurrenttransactionid(PG_FUNCTION_ARGS)
{
TransactionId xid = GetCurrentTransactionId();
PG_RETURN_INT32((int32) xid);
}
--
-- getCurrentTransactionId
--
SET search_path = public;
CREATE FUNCTION getCurrentTransactionId()
RETURNS integer
AS 'MODULE_PATHNAME'
LANGUAGE 'c';
--
-- eof
--
# $PostgreSQL: pgsql/contrib/chkpass/Makefile,v 1.6 2004/08/20 20:13:02 momjian Exp $
MODULE_big = getcurrenttransactionid
OBJS = getcurrenttransactionid.o
SHLIB_LINK = $(filter -lcrypt, $(LIBS))
DATA_built = getcurrenttransactionid.sql
DOCS = README.getcurrenttransactionid
ifdef USE_PGXS
PGXS = $(shell pg_config --pgxs)
include $(PGXS)
else
subdir = contrib/getcurrenttransactionid
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
TO DO!!
On Tue, 2005-06-14 at 19:03, Bruce Momjian wrote: > Steve - DND wrote: > > > > Let's pretend I don't know how to do that. :) Do you have a > > > link to a page > > > > in the manual that describes this process, or can you give me a quick > > > > runthrough of what you're talking about? > > > > > > Every row has an invisible xmin/xmax columns that represent the xid of > > > the row inserted, updated, or deleted. Why do you need to know the xid > > > is a better question. > > > > Hmm...not quite what I was hoping for. I'm looking to add the ability to my > > auditing scripts to know what happened in a given transaction. Right now, > > it's just table based, so while it still logs all of the changes, it does it > > on a per table basis, not associating changes on two tables as being part of > > the same action. > > > > I can currently guess as to what was changed at one time by using the > > current user that's recorded, and the time at which the changes occurred, > > but it's not foolproof. > > Well, you can take the xmin of a row and look for other rows with the > same xmin, either in the same table or in different tables. A query to pull all the rows that match. id is the primary key. select *,t1.xmin from test t1 join test t2 on (t1.xmin=t2.xmin and t1.id>t2.id);
On Tue, Jun 14, 2005 at 07:41:11PM -0400, Bruce Momjian wrote: > Steve - DND wrote: > > Simple question I'm sure, but how can I get the transaction ID that a query > > is currently running in? I can find all kinds of documentation talking about > > transaction ids, but nothing about how to get the current one. > > You can look at the xmin of a tuple you created or an xmax of a tuple > you deleted. Beware -- if you use savepoints, the Xid will change and you will think it was a different transaction when in fact it was the same one. You should also know that savepoints are used invisibly sometimes, for example in EXCEPTION clauses in PL/pgSQL. (Though you return to the original transaction when the EXCEPTION block finishes, I think.) -- Alvaro Herrera (<alvherre[a]surnet.cl>) "Los románticos son seres que mueren de deseos de vida"