Re: [SPAM] 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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)candle(dot)pha(dot)pa(dot)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.

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"