On 4/30/15 3:13 AM, Meel Velliste wrote:
My goal is to select rows that are new or have been modified since a
given snapshot. I am doing it like this:
SELECT * FROM my_table WHERE NOT
txid_visible_in_snapshot(xmin::TEXT::BIGINT,
'123456:123456:'::TXID_SNAPSHOT);
On one hand, it seems to me that the txid_visible_in_snapshot function
was taylor-made for what I am trying to accomplish. On the other hand,
it seems wrong because the first argument of txid_visible_in_snapshot is
BIGINT, and I am trying to coerce a xmin which is of type XID into this
argument.
So I have three questions:
1) Is this a good way to accomplish my goal (keep in mind I have
read-only access to the db)?
2) Will it fail when xmin wraps around at 2^32 or is
txid_visible_in_snapshot smart about it?
You're actually mixing two different data types here.
xmin is a 4 byte unsigned int; txid_snapshot is essentially comprised of
an 'epoch' and a txid. Essentially, every time txid wraps the current
epoch increases by one. That means that depending on it's value, an xmin
is either frozen, part of the current epoch, or part of the immediately
previous epoch.
3) Is it going to be performant (xmin is not indexed, is it)?
It's not, and even if it was that won't magically help when your where
clause is actually on the results of the boolean function
txid_visible_in_snapshot.
I'm not sure what you're ultimately trying to accomplish, but I suspect
there's an easier/better way than messing around with xmin to do it...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
