Skip site navigation (1) Skip section navigation (2)

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

From: Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)ru>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-hackers(at)postgresql(dot)org
Subject: Re: How to check whether the row was modified by this transaction before?
Date: 2012-12-07 02:38:13
Message-ID: 50C15695.5080002@dc.baikal.ru (view raw or flat)
Thread:
Lists: pgsql-hackers
On 12/07/2012 02:53 AM, Tom Lane wrote:
> Vlad Arkhipov <arhipov(at)dc(dot)baikal(dot)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?


In response to

pgsql-hackers by date

Next:From: Andrew DunstanDate: 2012-12-07 02:41:00
Subject: Re: pg_upgrade problem with invalid indexes
Previous:From: Bruce MomjianDate: 2012-12-07 02:23:14
Subject: Re: pg_upgrade problem with invalid indexes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group