Thread: Current transaction ID?

Current transaction ID?

From
"Steve - DND"
Date:
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



Re: Current transaction ID?

From
Bruce Momjian
Date:
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

Re: Current transaction ID?

From
"Steve - DND"
Date:
> 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



Re: Current transaction ID?

From
Bruce Momjian
Date:
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

Re: Current transaction ID?

From
"Steve - DND"
Date:
> > 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



Re: Current transaction ID?

From
Bruce Momjian
Date:
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

Re: Current transaction ID?

From
"Steve - DND"
Date:
>
> 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



Re: [SPAM] Current transaction ID?

From
Marek Lewczuk
Date:
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!!

Re: Current transaction ID?

From
Scott Marlowe
Date:
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);

Re: Current transaction ID?

From
Alvaro Herrera
Date:
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"