allocate chunk of sequence

From: Gary Fu <gfu(at)saicmodis(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: allocate chunk of sequence
Date: 2007-06-14 15:12:02
Message-ID: f4rlrp$22cc$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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' ?

Thanks,
Gary

create or replace function proc_allocate_seq(int)
returns int as $$

declare
nNumberOfFiles alias for $1;

aFileId int;
aNewFileId int;

begin

lock lock_table;

aFileId = nextval('aa_seq');

; sleep(3); if you have the proc

aNewFileId = setval('aa_seq', aFileId + nNumberOfFiles - 1);

return aFileId;

end;
$$ language plpgsql;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Smith 2007-06-14 15:40:05 Re: PostGreSQL for a small Desktop Application
Previous Message Andrus 2007-06-14 15:10:44 Disable duplicate index creation