From: | Heikki Linnakangas <heikki(at)enterprisedb(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | 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 14:50:27 |
Message-ID: | 471F5BB3.7040104@enterprisedb.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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?
That would solve the problem with two updates of the same row, but not this:
UPDATE .. WHERE CURRENT OF...
FETCH RELATIVE 0
At the moment, that returns the next row, not the one that was updated.
Same problem with FETCH NEXT + FETCH PRIOR after the UPDATE.
What does the SQL standard have to say about this?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2007-10-24 15:45:04 | Re: second DML operation fails with updatable cursor |
Previous Message | Heikki Linnakangas | 2007-10-24 14:34:40 | Re: Feature Freeze date for 8.4 |