Re: 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);