Skip site navigation (1) Skip section navigation (2)

Re: second DML operation fails with updatable cursor

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: Heikki Linnakangas <heikki(at)enterprisedb(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Dharmendra Goyal <dharmendra(dot)goyal(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: second DML operation fails with updatable cursor
Date: 2007-10-24 15:48:07
Message-ID: 1193240887.4242.148.camel@ebony.site (view raw or flat)
Thread:
Lists: pgsql-hackers
On Wed, 2007-10-24 at 15:50 +0100, Heikki Linnakangas wrote:
> Tom Lane wrote:
> > "Dharmendra Goyal" <dharmendra(dot)goyal(at)gmail(dot)com> writes:
> >> If i do update and delete operations on a row pointed by cursor's current
> >> then only first operation succeeds, second operation fails.
> > 
> > Hm, by "fails" you mean "does nothing", right?
> > 
> > The reason for this is that WHERE CURRENT OF is implemented as if it
> > were WHERE tid = <something>, and historically we've taken that to mean
> > the specific tuple at that exact TID.  After there's been an update
> > already, the tuple at that TID is no longer live to your transaction,
> > and so the tid-search fails.  To make this work as the spec requires,
> > we'd have to be willing to follow the tuple update chain to find the
> > currently-live instance of the row.
> > 
> > While I've not tried this, I think we could fix it by having nodeTidscan
> > use SnapshotAny instead of the query snapshot when fetching a tuple for
> > CurrentOf (but not otherwise, so as to not change the behavior of WHERE
> > tid = <something>).  We'd essentially be taking it on faith that the
> > CurrentOf gave us a TID that was live earlier in the transaction, and
> > so is still safe to fetch.  I think everything else would just fall out
> > if the initial heap_fetch weren't rejecting the tuple.
> > 
> > Comments anyone?

I don't like the faith bit.

I'd prefer if we attempted the fetch using the current Snapshot. If that
returns an invisible row, then re-fetch at SnapshotAny and follow the
chain forwards. That way we're just special casing this situation rather
than changing the main line of code. 
 
I wonder how serializable transactions are supposed to work in this
situation. Can the user really make the transaction throw an error by
trying to re-inspect his own changes? Surely not.

> That would solve the problem with two updates of the same row, but not this:
> UPDATE .. WHERE CURRENT OF...
> FETCH RELATIVE 0

Sounds like this problem was a pre-existing issue, but I've not checked.

FETCH RELATIVE 0 re-fetches the current row according to the manual. If
the current row has been updated then we can only see the new version;
the old pre-UPDATE version must not be visible to us, ever.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


In response to

Responses

pgsql-hackers by date

Next:From: tomasDate: 2007-10-24 15:50:01
Subject: Re: Feature Freeze date for 8.4
Previous:From: Tom LaneDate: 2007-10-24 15:45:04
Subject: Re: second DML operation fails with updatable cursor

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group