Re: Table locks

Andreas Seltenreich <seltenreich(at)gmx(dot)de> writes:
> Jake Stride writes:
>> I thought of doing:
>> SELECT max(jobno) from jobs where companyid=1;

> I think SELECT FOR UPDATE should work fine here.

Nope; he'll get something like

regression=# select max(unique1) from tenk1 for update;
ERROR: SELECT FOR UPDATE is not allowed with aggregate functions

His best bet is probably

BEGIN;
LOCK TABLE jobs;
SELECT max(jobno) from jobs where companyid=1;
INSERT INTO jobs ...
COMMIT;

This is pretty horrid from a concurrency point of view but I don't think
there's any other way to meet the "no gaps" requirement.

You could reduce the strength of the lock a bit, for instance
LOCK TABLE jobs IN EXCLUSIVE MODE;
which would allow readers of the jobs table to proceed concurrently,
but not writers. If you were willing to assume that all inserters into
jobs are cooperating by explicitly obtaining the correct lock, you
could reduce it to
LOCK TABLE jobs IN SHARE UPDATE EXCLUSIVE MODE;
which is the lowest self-conflicting table lock type. This would allow
unrelated updates to the jobs table to proceed concurrently too (though
not VACUUMs). See

http://www.postgresql.org/docs/7.4/static/explicit-locking.html

regards, tom lane