Re: BUG #10748: xmax is not resetting properly with FOR UPDATE

Hi,

On 2014-06-24 14:29:26 +0000, pinker@onet.eu wrote:
> The following bug has been logged on the website:
>
> Bug reference:      10748
> Logged by:          Alicja Kucharczyk
> Email address:      pinker@onet.eu
> PostgreSQL version: 9.3.4
> Operating system:   RedHat
> Description:
>
> The problem is described here:
> http://stackoverflow.com/questions/24382158/strange-cleanup-behaviour-with-for-update
>
> The main problem is that xmax values stays set with xid of transaction that
> has already committed. The documentation says: "The identity (transaction
> ID) of the deleting transaction, or zero for an undeleted row version. It is
> possible for this column to be nonzero in a visible row version. That
> usually indicates that the deleting transaction hasn't committed yet, or
> that an attempted deletion was rolled back."

This isn't a bug. It's expected that xmax sometimes is nonzero for
undeleted rows. As you cite "It is possible for this column to be
nonzero in a visible row version.". The list of cases in which that can
happen isn't exhaustive...

> I have used this feature for a queue to avoid locking, but it doesn't work
> together with FOR UPDATE clause.

I doubt you can sensibly use xmax for this unless you're willing to
write server side C code. You're probably better of using something like
pgq or advisory locks.

Greetings,

Andres Freund

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