On 8/3/07, Steve Midgley <public@misuse.org> wrote:
> Hi Scott,
>
> Thanks for this info (and Michael too!).
>
> Let me see if I understand your suggestion. I would run these three
> commands in sequence:
>
> # select nextval('[my_seq_name]');
> returns => 52 [I believe that the sequence is at 52]
> # alter sequence [my_seq_name] increment by 5000;
> # select nextval('[my_seq_name]');
> returns => 5052
>
> If the third command doesn't return "5052" - i.e. it returns 5053, then
> I know that somewhere in this sequence another process grabbed an id
> out from under me. It doesn't matter where, but I would know that my
> 5000 id's are not unique and should be discarded? If the third command
> DOES return 5052, then I know that those 5000 id's are "locked" for my
> use and no other application could have grabbed one of them?
No, that's not what would happen. If someone grabbed an id after the
increment value was changed, then you'd get 10052, cause they would
increment the sequence by 5,000.since you're not using setval, and
you're keeping the increment positive, there's no danger of collision,
only of over-incrementing and leaving a giant hole in your sequence.
which is ok.
> Can anyone see a flaw in that? It looks right to me..
>
> Scott - it also seems to me that I need not waste all those id's if
> another application does grab one during my statement: If I detect a
> failure, I could just reset the pk sequence back to the max id of the
> underlying table before trying again. I think this code would do it
> (stolen from Ruby's postgres adaptor):
That is open to a race condition. The bad kind.
> SELECT setval('[seq_name]', (SELECT COALESCE(MAX([pk_of_seq]), (SELECT
> min_value FROM [seq_name])) FROM [table_of_pk]), false)
As long as you're using setval, you have a race condition. Please
avoid it. Unless you can guarantee that no one else is using the
database at the same time (during a maintenance window etc...)
> So for table "property" with pk of "id" and sequence name
> "property_id_seq":
>
> SELECT setval('property_id_seq', (SELECT COALESCE(MAX(id), (SELECT
> min_value FROM property_id_seq)) FROM property), false)
You'd think that the select coalesce and the outer select setval would
not have a race condition, but they still do. Just a much smaller
one.
> I'm now starting to think that there's no way to solve this problem in
> an "elegant manner" even in a stored procedure? Your method seems to be
> as good as it's going to get? (Not that I'm complaining!)
Yep. Safe is better than pretty or elegant. :)