Обсуждение: BUG #10748: xmax is not resetting properly with FOR UPDATE
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." I have used this feature for a queue to avoid locking, but it doesn't work together with FOR UPDATE clause. best regards, A.Kucharczyk
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
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. +