Forest Wilkinson <fspam@home.com> writes:
> If I remember correctly, UPDATE establishes a lock on the affected rows,
> which will block another UPDATE on the same rows for the duration of the
> transaction. If that's true, shouldn't I be able to achieve my desired
> behavior by removing the initial as follows:
> create function nextid( varchar(32)) returns int8 as '
> update idseq set id = id + 1 where name = $1::text;
> select id from idseq where name = $1::text;
> ' language 'sql';
> Or, would I still have to add FOR UPDATE to that final SELECT?
You're right, the initial SELECT FOR UPDATE is a waste of cycles
considering that you're not using the value it returns. But you'll
still need the last select to be FOR UPDATE so that it plays by the
same rules as the UPDATE does.
regards, tom lane