> To the rest on the list:
> Try the above example by adding a lock between the two lines:
>
> create function nextid( varchar(32)) returns int8 as '
> update idseq set id = id + 1 where name = $1::text;
> select * from lock1;
> select id from idseq where name = $1::text for update;
> ' language 'sql';
>
> session1:
> begin work;
> lock table lock1 in access exclusive mode;
> session 2:
> not in txn: select nextid('one'); // this blocks
> select nextid('one');
> commit work;
>
> And stare at the results you get with and without for update :-(
> Something is definitely fishy with the visibility of SELECT here.
Without "for update" I see a tuple in session2 from before session1 began.
After both complete, the net result is correct (id is incremented by 2).
This is very interesting, unfortunately I must leave Internet access until
monday since my daughter called me home, and mail is so dead slow,
that I did not even receive my last mails yet :-(
Andreas