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

On Tue, Jun 24, 2014 at 05:15:25PM +0200, Andres Freund wrote:
> Hi,
>
> On 2014-06-24 14:29:26 +0000, pinker(at)onet(dot)eu wrote:
> > The following bug has been logged on the website:
> >
> > Bug reference: 10748
> > Logged by: Alicja Kucharczyk
> > Email address: pinker(at)onet(dot)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.

I think if the user wants to use xmax this way they are going to have to
install contrib/pageinspect, then query the flags that indicate if xmax
is really expired.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +