Обсуждение: "xmin" system column
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
"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