Re: Better alternative for Primary Key then serial??

On Dec 13, 2007, at 14:12, John D. Burger wrote:

> Alban Hertroys wrote:
>
>> The problem the OP is pointing out seems difficult to solve. A
>> sequence doesn't know about existing records with a possibly
>> higher number than the sequence is at.
>>
>> This may be worked around by keeping a list of numbers used up
>> beyond the current sequence value so the sequence knows what
>> numbers to skip, but that has problems of its own (if there are
>> many such numbers, or if the sequence gets created after data has
>> been added to the list). It gets ugly.
>
> I would just have a trigger that sets the serial to NEW.id + 1.
> Dunno if this has concurrency issues, though, and it may leave huge
> gaps in the key space, and (more importantly) use up your sequence
> too quickly.

That thought had crossed my mind, but I rejected it.

Besides those issues, you'd need to add that trigger to every table
that potentially has this "issue", it does indeed have concurrency
issues, and you're in for some fun if someone decides to use the last
possible value of a serial field to define a special case (although
the sequence could be defined to end before that value of course) and
causes immediate wraparound of the sequence (provided it's allowed to
rotate) and thus causing duplicate key violations as soon as the
sequence matches the first record in the table.

I was looking at a general solution that would work as automatic as
sequences already do. Not that it's a big issue anyway, you just have
to keep in mind that it works like it does.

Just to prevent any mistakes, I am not requesting a change in
behaviour, I'm fine with how it works (and has worked for ages in
many database servers). It is an interesting puzzle though ;)

> I have, in fact, had situations where I wanted a serial PK, =and= I
> needed to insert with external IDs sometimes - essentially a mix of
> natural and surrogate keys (shudder). It turned out that the
> natural keys were always positive, so I set up the sequence to
> range =downward= from 0.

That's a common solution to the problem, although it leaves the
possibility that people are being smart and enter negative integers
exactly to prevent this problem. And of course you cannot rely on
sorting it by index to get your data more or less in the order inserted.

--
Alban Hertroys

"If you lose your memory,
you can't remember where you left it."

!DSPAM:737,476419a19654199211162!