Re: serialization errors when inserting new records

Ralph van Etten wrote:
> I agree that a serial would be better.
>
> But I think there are situations where a serial isn't convenient
> Like when you want an primary key which consists of the current
> year and an sequence number. Like ('05', 1), ('05', 2), ('05', 3) etc.
> With a sequence you must write extra code to reset the sequence every year
> and you get into trouble if someone inserts data from the previous year...
> A 'MAX(id)+1' is much simpler and cleaner then.

My personal experience is trying to get primary keys to "mean" something
is a pain in the ass. In your example, I'd much rather stick with serial
as the primary key and store the year/sequence in another "display"
field. Think about this situation:

1) records 5/1 to 5/2000 are loaded into a table
2) oops, you made a mistake ... another 200 records should have been
shoehorned @ 5/20
3) ugh...you now have to add +200 to records 5/20 to 5/2000 -- and you
have to do it 1 record at a time in reverse order. (trying to do an
update via a single command will immediately produce a dup key violation
--> 5/20+200 = 5/220 which already exists)
4) double ugh...you've got FK already pointed to those records -- now
you have to drop the FKs first, update those tables, do step #3 again,
recreate the FKs.

On the otherhand, if you just used arbitary numbers, you could update
5/20 to 5/2000 with a single command, load the correct 5/20-5/220
records in and voila.