On 12/4/06, Bob Pawley <rjpawley@shaw.ca> wrote:
> Your missing the point.
>
> I am creating a design system for industrial control.
>
> The control devices need to be numbered. The numbers need to be sequential.
> If the user deletes a device the numbers need to regenerate to again become
> sequential and gapless.
if that's the case then you need to simply renumber the tables after
deletion. serial column is ok. method is after record deletion:
1. acquire lock to prevent race (use advisory lock or some other lock,
table for example)
2. reset sequence to initial state (read setval in docs)
3. update foo set col=nextval('s')
4. release lock
this may be done on trigger if necesary. if using advisory lock, make
sure to catch sql exception and release lock just in case.
records will be renumberd and sequence will be pointed at next
allocation slot. if your design can accomidate short term gaps in the
sequence, simply keep a freelist table maintained by a trigger upon
record deletions and check that first before grabbing sequence.
also, do not even cotemplate using this column as primary key (use
unique constraint).
merlin