From: | Sergey Konoplev <gray(dot)ru(at)gmail(dot)com> |
---|---|
To: | Mike Lewis <mikelikespie(at)gmail(dot)com> |
Cc: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Comparing txid_current() to xmin |
Date: | 2012-11-07 00:08:18 |
Message-ID: | CAL_0b1vwTG1D0xJ6BoTkU52gv=p68j-LA48ABOyunxuKpoKx7Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, Nov 6, 2012 at 2:55 PM, Mike Lewis <mikelikespie(at)gmail(dot)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(dot)ru(at)gmail(dot)com Skype: gray-hemp Phone: +14158679984
From | Date | Subject | |
---|---|---|---|
Next Message | Craig Ringer | 2012-11-07 01:54:37 | Re: Tuning / performance questions |
Previous Message | Mike Lewis | 2012-11-06 22:55:40 | Comparing txid_current() to xmin |