Re: [HACKERS] How to check whether the row was modified by this transaction before?
On Thu, Dec 6, 2012 at 3:58 AM, Vlad Arkhipov arhi...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
Vlad Arkhipov arhi...@dc.baikal.ru writes: 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). IF OLD.xmin = txid_current() THEN Comparing to txid_current() mod 2^32 would probably work, but note this will not think that subtransactions or parent transactions are this transaction, so any use of savepoints or plpgsql exception blocks is likely to cause headaches. Why do you think you need to know this? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
Tom Lane wrote: Vlad Arkhipov arhi...@dc.baikal.ru writes: 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). IF OLD.xmin = txid_current() THEN Comparing to txid_current() mod 2^32 would probably work, I think we should be setting the initial epoch to something other than zero. That way, some quick testing would have revealed this problem immediately. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
Alvaro Herrera alvhe...@2ndquadrant.com writes: I think we should be setting the initial epoch to something other than zero. That way, some quick testing would have revealed this problem immediately. Yeah, having initdb start the epoch at 1 doesn't seem unreasonable. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
Andres Freund and...@2ndquadrant.com writes: On 2012-12-06 15:08:51 -0300, Alvaro Herrera wrote: Vlad Arkhipov arhi...@dc.baikal.ru writes: 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). I wonder if we shouldn't have a function txid_is_current(xid); Yeah, I was wondering that too, and wanted to know if the OP had a use-case that was mainstream enough to justify adding such a function. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] How to check whether the row was modified by this transaction before?
On 12/07/2012 02:53 AM, Tom Lane wrote: Vlad Arkhipov arhi...@dc.baikal.ru writes: 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). IF OLD.xmin = txid_current() THEN Comparing to txid_current() mod 2^32 would probably work, but note this will not think that subtransactions or parent transactions are this transaction, so any use of savepoints or plpgsql exception blocks is likely to cause headaches. Why do you think you need to know this? regards, tom lane The use case is quite simple. I'm trying to rewrite our internal system versioning extension (SQL feature T180) in more abstract way. Any temporal versioned table uses its associated history table to store updated and deleted data rows. For this purpose the extension adds AFTER UPDATE/DELETE triggers to the table that insert OLD row in the history table for updated and deleted rows. But if there are multiple changes to a row in the same transaction the trigger should generate a history row only for the first change. On 12/07/2012 06:26 AM, Tom Lane wrote: It strikes me that the notion of this row was previously modified by the current transaction is squishier than it might look, and we'd do well to clarify it before we consider exporting anything. I think there are three ways you might define such a function: 1. xmin is exactly equal to current (sub)transaction's XID. 2. xmin is this (sub)transaction's XID, or the XID of any subcommitted subtransaction of it. 3. xmin is this (sub)transaction's XID, or the XID of any subcommitted subtransaction, or the XID of any open parent transaction or subcommitted subtransaction thereof. If I understand you correctly, what I'm looking for is described by the 3rd case and I may use TransactionIdIsCurrentTransactionId() for this purpose? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
