Thread: Comparing txid_current() to xmin

I am trying to make a trigger that updates a row once and only once per transaction (even if this trigger gets fired multiple times).  The general idea is that for a user we have a version number.  When we modify the user's data, the version number is incremented then set on the object.  We only need to increment the version number once.

On Tue, Nov 6, 2012 at 2:55 PM, Mike Lewis <mikelikespie@gmail.com> wrote:
> I am trying to make a trigger that updates a row once and only once per
> transaction (even if this trigger gets fired multiple times).  The general
> idea is that for a user we have a version number.  When we modify the user's
> data, the version number is incremented then set on the object.  We only
> need to increment the version number once.
>
> I am thinking about doing something like:
>
> update user
> set version=version+1
> where txid_current() != xmin and user_id = 352395;
>
>
> So I guess my questions are:
>
> How dirty is this?
> Will I run into issues?
> Is there a better way of doing this?

AFAIU it will work without issues. However I would use an additional
"modified" column that is set by trigger every time the row is updated
(and inserted) to the current time stamp and use it instead of
txid_current()/xmin. The only my reason is that it can give me more
control than txid based solution, for example if I need to set the
modified column from outside, say to sync it with some another
database shard's data.

--
a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray.ru@gmail.com Skype: gray-hemp Phone: +14158679984

Mike Lewis escribió:
> I am trying to make a trigger that updates a row once and only once per
> transaction (even if this trigger gets fired multiple times).  The general
> idea is that for a user we have a version number.  When we modify the
> user's data, the version number is incremented then set on the object.  We
> only need to increment the version number once.
>
> I am thinking about doing something like:
>
> update user
> set version=version+1
> where txid_current() != xmin and user_id = 352395;

Uh, txid_current returns a 64 bit value, whereas xmin only stores the 32
least significant bits.  They would certainly differ after a xid
wraparound.

--
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote:
> I am trying to make a trigger that updates a row once and only once per
> transaction (even if this trigger gets fired multiple times).  The general
> idea is that for a user we have a version number.  When we modify the
> user's data, the version number is incremented then set on the object.  We
> only need to increment the version number once.
>
> I am thinking about doing something like:
>
> update user
> set version=version+1
> where txid_current() != xmin and user_id = 352395;
>
>
> So I guess my questions are:
>
> How dirty is this?
> Will I run into issues?

It won't work in the presenence of subtransactions and is a bit more
complicated if you inserted the row in the same transaction.

On Wed, Nov 7, 2012 at 10:21 AM, Andres Freund <andres@anarazel.de> wrote:
> On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote:
>> I am trying to make a trigger that updates a row once and only once per
>> transaction (even if this trigger gets fired multiple times).  The general
>> idea is that for a user we have a version number.  When we modify the
>> user's data, the version number is incremented then set on the object.  We
>> only need to increment the version number once.
>>
>> I am thinking about doing something like:
>>
>> update user
>> set version=version+1
>> where txid_current() != xmin and user_id = 352395;
>>
>>
>> So I guess my questions are:
>>
>> How dirty is this?
>> Will I run into issues?
>
> It won't work in the presenence of subtransactions and is a bit more
> complicated if you inserted the row in the same transaction.

This can be solved by storing txid_current() into row
and using that in comparision instead xmin/xmax.


--
marko

On Thu, Nov 08, 2012 at 11:47:37AM +0200, Marko Kreen wrote:
> On Wed, Nov 7, 2012 at 10:21 AM, Andres Freund <andres@anarazel.de> wrote:
> > On Tue, Nov 06, 2012 at 02:55:40PM -0800, Mike Lewis wrote:
> >> I am trying to make a trigger that updates a row once and only once per
> >> transaction (even if this trigger gets fired multiple times).  The general
> >> idea is that for a user we have a version number.  When we modify the
> >> user's data, the version number is incremented then set on the object.  We
> >> only need to increment the version number once.
> >>
> >> I am thinking about doing something like:
> >>
> >> update user
> >> set version=version+1
> >> where txid_current() != xmin and user_id = 352395;
> >>
> >>
> >> So I guess my questions are:
> >>
> >> How dirty is this?
> >> Will I run into issues?
> >
> > It won't work in the presenence of subtransactions and is a bit more
> > complicated if you inserted the row in the same transaction.
>
> This can be solved by storing txid_current() into row
> and using that in comparision instead xmin/xmax.

If there is sufficient demand for this it should be easy enough to add a
function that checks for stuff like this using the information already
available in the backends memory.
The hardest part seems to be to find a good name...

It would basically only need to wrap
TransactionIdIsCurrentTransactionId.

Greetings,

Andres Freund

--
 Andres Freund                       http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services