Gary Fu wrote:
hello,
I try to allocate a chunk of ids from a sequence with the following
proc. However, if I don't use the 'lock lock_table', the proc may not
work when it runs at the same time by different psql sessions. Is
there a better way without using the 'lock lock_table' ?
aNewFileId = setval('aa_seq', aFileId + nNumberOfFiles - 1);
This is NOT the best way to use sequences.
Much better would be to use a loop to allocate the them one after the
other, and put them into a record type or something.
Do they HAVE to be contiguous?
If they're always the same size, then set the increment value of the
sequence on creation to reflect that.
i.e.:
create sequence abc increment by 20
then just select nextval, and you have that plus the 20 after it all to
yourself. Lots of ways to handle this, but setval is generally the
worst way to handle anything in a highly parallel env.
