AW: Re: [SQL] possible row locking bug in 7.0.3 & 7.1

> > > I doubt if it's a bug of SELECT. Well what
> > > 'concurrent UPDATE then SELECT FOR UPDATE +
> > > SELECT' return ?
> >
> > I'm going to add additional check to heapgettup and
> > heap_fetch:
> >
>
> SELECT seems to be able to return a different result
> from that of preceding SELECT FOR UPDATE even after
> applying your change.

Only if you left this cursor position without doing an actual update
(i.e. after fetch next). The select for update is only supposed to guard
the current cursor position. Once you leave without modification
another session can be allowed to update.
This is how it is supposed to react in read committed
mode. If you don't like this you need repeatable read.

The example given is of questionable value, since a select for update
without a cursor in read committed mode does not need to behave any different
than a simple select without for update.

> SELECT doesn't seem guilty but the result is far
> from intuitive.

It is intuitive. The bug was iirc, that you saw 2 versions of the same row
in the second select statement (= 2 rows returned by second select).
Vadim's patch will let you see only the newer row.

> It seems impossoble for all queires inside such
> a function to use a common snapshot.

In read committed they are not required to !

It looks like a lot of people on the list are absolute fans
of repeatable read isolation :-) Not me, I know a lot of applications
where committed read, or even read uncommitted makes a lot more
sense.

Andreas