[Prefix-users] Changed primary key behaviour in 1.0?

Daniel Swarbrick daniel at pressure.net.nz
Mon Nov 30 00:44:43 UTC 2009


I've just upgraded a DB from 8.3 to 8.4, and with it installed the
latest .deb of postgresql-8.4-prefix, and noticed the when upgrading the
cluster, it couldn't recreate the primary key on the my prefix table.
The error message complained about duplicate values. I'm 100% positive
the table does not contain duplicate values.

I've since found that the behaviour seems to have changed.

Make a table

CREATE TABLE prefixes (
    prefix prefix_range PRIMARY KEY NOT NULL,
    oga_id integer NOT NULL
);

Throw a few values in a table:

talk=> insert into prefixes values ('1201', 770);
INSERT 0 1
talk=> insert into prefixes values ('1202', 771);
INSERT 0 1
talk=> insert into prefixes values ('1203', 772);
INSERT 0 1

as you'd expect:

talk=> insert into prefixes values ('1203', 772);
ERROR:  duplicate key value violates unique constraint "prefixes_pkey"

but wtf?

talk=> insert into prefixes values ('1', 772);
ERROR:  duplicate key value violates unique constraint "prefixes_pkey"
talk=> insert into prefixes values ('12', 772);
ERROR:  duplicate key value violates unique constraint "prefixes_pkey"
talk=> insert into prefixes values ('120', 772);
ERROR:  duplicate key value violates unique constraint "prefixes_pkey"
talk2=> insert into prefixes values ('12012345', 772);
ERROR:  duplicate key value violates unique constraint "prefixes_pkey"

Basically I can no longer add any prefixes that are a substring or
superstring of an existing prefix. Doesn't this kind of defeat the
purpose of longest match? eg that I can have multiple prefixes that
start the same, but have gradually more digits?

Is this a bug?




More information about the Prefix-users mailing list