Scott Marlowe wrote:
> 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.
>
>
Thanks for the reply.
The chunk to be allocated is not the same size, so to set the increment
value will not help.
I'm not sure how the nextval function to handle this internally, if it
has to read and update the sequence object. Does it use some kind of
lock ? Otherwise the problem mentioned here should happen to nextval
function also.
The lock command does not work for the sequence, so in my example, I
have to use a dummy table for lock to work. Another thought is to have
the nextval function takes an extra argument for the increment value
(instead of the default increment value).
Gary
