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

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

On Tue, Jun 24, 2014 at 05:15:25PM +0200, Andres Freund wrote:

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.

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@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs