Re: Update with subselect sometimes returns wrong result

Andres Freund escribió:

> Several things:
> a) If the old lockmode is stronger than the new one, we can just promote
> the new one. That's fine.
> b) the old xmax cannot be an update, we wouldn't see the row version in that
> case. And in any way, ISUPDATE_from_mxstatus() isn't sufficient to
> determine whether the old row was an update, one needs to look at
> LOCK_ONLY as well, no?
> c) Any reason we can't apply this optimization for subtransactions in
> some scenarios?
>
> a), b) are relatively easy. Patch attached. Being a clear regression, I
> think it should be backpatched, but I'm not sure if it has to be this
> point release. It's simple enough, but ...

Nice idea. I modified the patch slightly, please see attached.

I'm not sure about the added assert that the tuple cannot possibly be
locked. I fear cursors provide strange ways to access at tuples. I
haven't been able to reproduce a problem but consider an example such as
the one below. Is it possible, I wonder, to arrive at the problematic
scenario considering that we might try to traverse an update chain to
lock future versions of the tuple? I suspect not, because if the tuple
was updated (so that there is an update chain to traverse in the first
place) then we wouldn't be able to update the original anyway. (I guess
I'm mainly talking to myself to assure me that there's no real problem
here.)

In any case I think it's easy to handle the case by doing something like
is_update |= ISUPDATE_from_mxstatus(old_status);
and remove the Assert().

alvherre=# create table f (a int primary key, b text);
CREATE TABLE
alvherre=# insert into f values (1, 'one');
INSERT 0 1

alvherre=# begin;
BEGIN
alvherre=# select * from f for update;
a | b
---+-------
1 | three
(1 fila)

alvherre=# declare f cursor for select * from f;
DECLARE CURSOR
alvherre=# fetch 1 from f;
a | b
---+-------
1 | three
(1 fila)

alvherre=# update f set b = 'two';
UPDATE 1
alvherre=# move backward all from f;
MOVE 0
alvherre=# fetch 1 from f;
a | b
---+-------
1 | three
(1 fila)

alvherre=# update f set b = 'four' where current of f;
UPDATE 1
alvherre=# select * from f;
a | b
---+------
1 | four
(1 fila)

alvherre=# commit;

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services