Re: "xmin" system column - Mailing list pgsql-general

From Tom Lane
Subject Re: "xmin" system column
Date
Msg-id 24902.1138313960@sss.pgh.pa.us
Whole thread Raw
In response to Re: "xmin" system column  ("Eric B. Ridge" <ebr@tcdi.com>)
List pgsql-general
"Eric B. Ridge" <ebr@tcdi.com> writes:
> That's what I was afraid of.  I've pondering making a "grouping"
> column that gets set to "xmin" via an UPDATE trigger.  At least I'd
> have a constant value that would survive database dumps and reloads.

That will most assuredly NOT work.  You will have XID conflicts if
you reload into a different instance of Postgres, or even within the
same instance once it's been running long enough to wrap XIDs around.

> I really need a way to create a unique identifier at the start of a
> top-level transaction, and be able to use it via triggers and/or
> column default values in that or its subtransactions.

The only thing I can see that would work for you is to nextval() some
sequence object at the start of each transaction, and then store its
currval() wherever you need it.  As long as you store int8 not int4 or
xid values, this would be reasonably proof against wraparound issues.

            regards, tom lane

pgsql-general by date:

Previous
From: "Eric B. Ridge"
Date:
Subject: Re: "xmin" system column
Next
From: Michael Fuhr
Date:
Subject: Re: "xmin" system column