PostgreSQL Discussion - "xmin" system column

Author

"xmin" system column

Eric B. Ridge

2006-01-26, 4:57 pm

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've tried both "VACUUM" and "VACUUM FULL" on specific tables and
neither seem to have an impact, but I haven't done extensive testing
against very large tables that have experienced lots of churn.

Any input will be greatly appreciated!

eric

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Tom Lane

2006-01-26, 4:57 pm

"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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Eric B. Ridge

2006-01-26, 4:57 pm

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

> "Eric B. Ridge" <ebr@tcdi.com> writes:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Eric B. Ridge

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Tom Lane

2006-01-26, 4:57 pm

"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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2006-01-26, 4:57 pm

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

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Michael Fuhr

2006-01-26, 4:57 pm

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:
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Eric B. Ridge

2006-01-26, 8:23 pm

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 GetCurrentTransactio
nStartTimestamp() 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Marko Kreen

2006-01-27, 3:23 am

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/piperma...ary/003668.html
http://gborg.postgresql.org/piperma...ary/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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Christian Kratzer

2006-01-27, 7:23 am

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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-01-31, 8:23 pm

On Fri, Jan 27, 2006 at 11:18:23AM +0100, Christian Kratzer wrote:
> Hi,
>
> On Thu, 26 Jan 2006, Eric B. Ridge wrote:
> <snip/>
>
> 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

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Sponsored Links