Re: How to check whether the row was modified by this transaction before?

On Thu, Dec 6, 2012 at 3:58 AM, Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru> wrote:
> In a BEFORE UPDATE trigger I need to know whether the row was previously
> modified by this transaction. Is it safe to use xmin and txid_current() for
> this purpose (xmin is 32-bit txid type but txid_current() returns 64-bit
> bigint).
>
> CREATE FUNCTION test_trigger()
> RETURNS TRIGGER AS $$
> BEGIN
> IF OLD.xmin = txid_current() THEN
> -- Do something.
> ELSE
> -- Do something else.
> END IF;
> END;
> $$ LANGUAGE plpgsql;

txid_current() will return a different value from xmin after the XID
space has wrapped around at least once; also, you might need to
consider subtransactions.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company