Comparing txid_current() to xmin

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

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

Ah yes... That would be a problem. What about using just the lower 32
bits of the txid_current?

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