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@dc.baikal.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