Re: [GENERAL] xmin system column
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 [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 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
Re: [GENERAL] xmin system column
On 1/26/06, Eric B. Ridge [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] xmin system column
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 [EMAIL PROTECTED] CK Software GmbHhttp://www.cksoft.de/ Phone: +49 7452 889 135 Fax: +49 7452 889 136 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] xmin system column
Eric B. Ridge [EMAIL PROTECTED] 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
Re: [GENERAL] xmin system column
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
Re: [GENERAL] xmin system column
On Jan 26, 2006, at 4:44 PM, Tom Lane wrote: Eric B. Ridge [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] xmin system column
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
Re: [GENERAL] xmin system column
Eric B. Ridge [EMAIL PROTECTED] 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
Re: [GENERAL] xmin system column
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
Re: [GENERAL] xmin system column
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] xmin system column
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 ---(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
