Re: serialization errors when inserting new records

On Sun, 23 Jan 2005, William Yu wrote:

> 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:

Ok, but then I have the same problem. The "display" field has to be
unique and it needs to use a SELECT MAX in an INSERT query and this gives
the same problem. Only not with the primary key but with possible
duplicate values in the "display" field.

CREATE TABLE test (
pk SERIAL,
disp1 CHAR(2),
disp2 INT,

UNIQUE(disp1, disp2),
PRIMARY KEY(pk)
)

and insert with something like:

INSERT INTO test (disp1, disp2)
SELECT 'XX', COALESCE(MAX(disp2)+1, 1)
FROM test WHERE disp1 = 'XX';

This gives the same serialization errors.

Ralph.