> > Will we still have readers-dont-block-writers behaviour?
>
> Sure. The only thing this really affects is VACUUM and schema-altering
> commands, which will now have to wait until reader transactions commit.
And "lock table ...", which would need some deadlock resolution code,
because two sessions with select * from foo; lock table foo; should not need
to deadlock, since one of the two could get the lock without breaking anything.
> In other words
>
> Session 1 Session 2
>
> BEGIN;
> SELECT * FROM foo;
>
> ALTER TABLE foo ...
>
> ...
>
> COMMIT;
>
> Session 2 will have to wait for session 1 to commit; before it didn't.
> An example of why this is a good idea is
The below is a completely different thing than the above. In the below
it is clear that a shared lock is needed until the last row from c is fetched,
since the statement is still active.
In this particular example there would be two different behaviors in my proposal
depending on how many rows are in foo (1 or many).
If 0 or 1 row -> relese lock after fetch, if more rows release at commit.
>
> Session 1 Session 2
>
> BEGIN;
> DECLARE c CURSOR FOR
> SELECT * FROM foo;
>
> ALTER TABLE foo ...
>
> FETCH FROM c;
>
> COMMIT;
>
> Without a held read lock on foo, session 1 is in deep trouble,
> because its cursor is no longer correctly planned.
Andreas