Re: Finding new or modified rows since snapshot

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