"xmin" system column

"Eric B. Ridge" <ebr@tcdi.com> writes:

Outside of "VACUUM FREEZE", is there any way the "xmin" column in a
relation can change, assuming of course the tuple is never updated
again?

If the tuple lives long enough, VACUUM will change it to FrozenTransactionId
eventually, even without the FREEZE option.

I'm considering using this as a way to identify all tuples
modified in the same transaction (in an effort to group them
together), and am wondering if there's any way tuples from different
transactions could end up with the same xmin value.

This seems OK as long as the transaction was fairly recent. Note that
you will need a fairly restrictive definition of "same transaction"
(no subtransactions).

regards, tom lane

On Thu, Jan 26, 2006 at 04:19:34PM -0500, Eric B. Ridge wrote:

Outside of "VACUUM FREEZE", is there any way the "xmin" column in a
relation can change, assuming of course the tuple is never updated
again? I'm considering using this as a way to identify all tuples
modified in the same transaction (in an effort to group them
together), and am wondering if there's any way tuples from different
transactions could end up with the same xmin value.

I don't know about tuples from different transactions having the
same xmin (aside from 1/BootstrapXID and 2/FrozenXID), but tuples
from the same outer transaction could have different xmin values
due to savepoints.

test=> CREATE TABLE foo (x integer);
test=> BEGIN;
test=> INSERT INTO foo VALUES (1);
test=> SAVEPOINT s;
test=> INSERT INTO foo VALUES (2);
test=> RELEASE SAVEPOINT s;
test=> INSERT INTO foo VALUES (3);
test=> COMMIT;
test=> SELECT xmin, * FROM foo;
xmin | x
--------+---
424584 | 1
424585 | 2
424584 | 3
(3 rows)

Explicit savepoints aren't the only way to get this effect; you'll
also see it if the savepoint is implicit, as when trapping errors
in a function.

--
Michael Fuhr

On Jan 26, 2006, at 4:44 PM, Tom Lane wrote:

"Eric B. Ridge" <ebr@tcdi.com> writes:

Outside of "VACUUM FREEZE", is there any way the "xmin" column in a
relation can change, assuming of course the tuple is never updated
again?

If the tuple lives long enough, VACUUM will change it to
FrozenTransactionId
eventually, even without the FREEZE option.

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.

This seems OK as long as the transaction was fairly recent. Note that
you will need a fairly restrictive definition of "same transaction"
(no subtransactions).

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.

Is there some kind of "TopXID" magic variable/function that I haven't
found in the documentation?

eric

On Jan 26, 2006, at 4:50 PM, Michael Fuhr wrote:

test=> SELECT xmin, * FROM foo;
xmin | x
--------+---
424584 | 1
424585 | 2
424584 | 3
(3 rows)

hmm. Is it possible to grab that first xmin value when the
transaction first starts, then I can explicitly use when I need it?

eric

"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

On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote:

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.

I suppose a sequence is out of the question? Too easy to get it
wrong?

Is there some kind of "TopXID" magic variable/function that I haven't
found in the documentation?

Not in the standard installation, but I think a C function that
returns GetTopTransactionId() should work. It's trivial to write
and examples have been posted before; search the archives.

--
Michael Fuhr

On Thu, Jan 26, 2006 at 03:22:50PM -0700, Michael Fuhr wrote:

On Thu, Jan 26, 2006 at 05:05:19PM -0500, Eric B. Ridge wrote:

Is there some kind of "TopXID" magic variable/function that I haven't
found in the documentation?

Not in the standard installation, but I think a C function that
returns GetTopTransactionId() should work. It's trivial to write
and examples have been posted before; search the archives.

Tom made a good point against using this value: it's not guaranteed
to be unique, for example after a dump and reload. I suppose that's
a strong reason why the developers haven't provided such easy access
to it.

--
Michael Fuhr

On Jan 26, 2006, at 5:22 PM, Michael Fuhr wrote:

I suppose a sequence is out of the question? Too easy to get it
wrong?

Well, I just wanted to avoid embedding this idea into my
application. Would rather Postgres take care of it for me.

Not in the standard installation, but I think a C function that
returns GetTopTransactionId() should work. It's trivial to write
and examples have been posted before; search the archives.

Hmm. I also see GetCurrentTransactionStartTimestamp() in xact.h.
That could work as a mostly-unique identifier. Its value could
survive dumps (assuming clock is set correctly!) and a little wrapper
around it could be used by triggers or by default column values.

Futher reading in xact.c says:
/*
* This is the value of now(), ie, the transaction start time.
* This does not change as we enter and exit subtransactions, so we
don't
* keep it inside the TransactionState stack.
*/
static TimestampTz xactStartTimestamp;

<long pause>

hahaha, *blush*. I could just use "now()", right? pg8.1 docs say
that now()/CURRENT_TIMESTAMP "return the start time of the current
transaction; their values do not change during the transaction". I
could use a composite of (now(), GetTopTransctionId()) to assume
batch uniqueness.

eric

On 1/26/06, Eric B. Ridge <ebr@tcdi.com> wrote:

Outside of "VACUUM FREEZE", is there any way the "xmin" column in a
relation can change, assuming of course the tuple is never updated
again? I'm considering using this as a way to identify all tuples
modified in the same transaction (in an effort to group them
together), and am wondering if there's any way tuples from different
transactions could end up with the same xmin value.

I had the same problem - how to identify rows by transaction. I solved
it by using the xxid from Slony-I and making it 8-byte.

http://gborg.postgresql.org/pipermail/slony1-general/2006-January/003668.html
http://gborg.postgresql.org/pipermail/slony1-general/2006-January/003685.html

It has only 2 slight gotchas:

- the function will fail if there are more than 2G tx'es between calls
- you need to bump epoch if you reload dump.

otherwise seems to work fine.

Btw it uses TopTransactionId, so subtransactions should not be problem.

--
marko

Hi,

On Thu, 26 Jan 2006, Eric B. Ridge wrote:
<snip/>

<long pause>

hahaha, *blush*. I could just use "now()", right? pg8.1 docs say that
now()/CURRENT_TIMESTAMP "return the start time of the current transaction;
their values do not change during the transaction". I could use a composite
of (now(), GetTopTransctionId()) to assume batch uniqueness.

Or use a touple of (now(), pg_backend_pid()) for this kind of stuff.
pg_backend_pid() should sufficiently disambiguate now() to make obove
touple unique.

Greetings
Christian

--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136

On Fri, Jan 27, 2006 at 11:18:23AM +0100, Christian Kratzer wrote:

Hi,

On Thu, 26 Jan 2006, Eric B. Ridge wrote:
<snip/>

<long pause>

hahaha, *blush*. I could just use "now()", right? pg8.1 docs say that
now()/CURRENT_TIMESTAMP "return the start time of the current transaction;
their values do not change during the transaction". I could use a
composite of (now(), GetTopTransctionId()) to assume batch uniqueness.

Or use a touple of (now(), pg_backend_pid()) for this kind of stuff.
pg_backend_pid() should sufficiently disambiguate now() to make obove
touple unique.

That doesn't provide very good protection against the system clock
moving backwards though. I suspect you'd be better doing a tuple of
now() and a 2 byte sequence.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461