Finding new or modified rows since snapshot

From: Meel Velliste <meel(dot)velliste(at)gmail(dot)com>
To: Postgres General <pgsql-general(at)postgresql(dot)org>
Subject: Finding new or modified rows since snapshot
Date: 2015-04-30 08:13:28
Message-ID: CAKDa2+=yOZ1YZZ9L=eFBbQiaa9N7ksDLUp8TuDMxiYAjnPeLFg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?
3) Is it going to be performant (xmin is not indexed, is it)?

Thanks,
Meel

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Frank Langel 2015-04-30 09:16:36 Success story full text search
Previous Message Takashi Ohnishi 2015-04-30 05:06:52 Re: Pg_bulkload and speed