Re: More FOR UPDATE/FOR SHARE problems

There already is quite an extensive discussion of how FOR UPDATE  
behaves including these kinds of violations.

What you propose is interesting though. It would have been impossible  
before subtransactions but it's doable now. Still the performance  
might be unusable for complex queries. It's basically generalizing the  
logic a serializable transaction would take to a read committed command.

-- 
Greg


On 24 Jan 2009, at 18:50, Jeff Davis <pgsql@j-davis.com> wrote:

> This post is a follow-up of an off-list discussion with Nathan Boley.
> All references to FOR UPDATE apply to FOR SHARE as well.
>
> create table a(i int, j int);
> insert into a values(1, 10);
> insert into a values(2, 10);
> insert into a values(3, 10);
> insert into a values(4, 20);
> insert into a values(5, 20);
> insert into a values(6, 20);
>
> Session 1:
>  BEGIN;
>  UPDATE a SET j = (j - 10) WHERE i = 3 OR i = 4;
>
> Session 2:
>  SELECT * FROM a WHERE j = 10 FOR UPDATE; -- blocks
>
> Session 1:
>  COMMIT;
>
> Session 2 (results):
>   i | j
>  ---+----
>   1 | 10
>   2 | 10
>  (2 rows)
>
> There you see a snapshot of the table that never existed. Either the
> snapshot was taken before the UPDATE, in which case i=3 should be
> included, or it was taken after the UPDATE, in which case i=4 should  
> be
> included. So atomicity is broken for WHERE.
>
> So, FOR UPDATE produces known incorrect results for:
> * WHERE
> * ORDER BY:
>   http://archives.postgresql.org/pgsql-bugs/2009-01/msg00017.php
> * LIMIT
> * SAVEPOINT/ROLLBACK TO
>
> And I expect we'll find more, as well.
>
> It's not simply that FOR UPDATE works strangely in a couple isolated
> edge cases, as the docs imply. It works contrary to the basic
> assumptions that people familiar with PostgreSQL rely on. Furthermore,
> the people using FOR UPDATE are likely to be the people who care about
> these edge cases.
>
> I think that FOR UPDATE deserves a jarring disclaimer in the docs if  
> we
> maintain the current behavior. Something along the lines of "this does
> not follow normal transactional semantics and will produce incorrect
> results". Existing users may find current FOR UPDATE behavior useful  
> to
> avoid full-table locks, but they should be properly warned.
>
> If there is a fix, the only thing that I can imagine working (aside  
> from
> a full table lock) would be to iteratively acquire new snapshots and
> re-run the query until no concurrent transaction interferes.
>
> Regards,
>    Jeff Davis
>
>
>
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers