Trouble: [prefix]referrers_pkey in [prefix]referrers table
Posted: Sat Mar 29, 2008 1:01 pm
Hello,
Loooking into s9y database logs shows quite a number of this kind of error statements:
ERROR: duplicate key violates unique constraint "[prefix]referrers_pkey"
STATEMENT: INSERT INTO [prefix]referrers
(entry_id, day, count, scheme, host, port, path, query)
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart', 'query=somestring&src=web')
And the primary key is there as: PRIMARY KEY (host, "day", entry_id).
And database engine does its job correctly - blocks new inserts for a repeating combination of host, entry and date.
But that does not fit into real life, where one entry will on the same day receive clicks from search-engines with different keywords (rest is the same but query field is different) or same day clicks are otherwise coming from different urls of the same site (part and/or query fields are different).
Totally valid inserts would be:
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart2', 'query=somestring&src=web')
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart', 'query=somestring2&src=web')
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart2', 'query=somestring2&src=web')
But they will get rejected.
One possible solution would drop the primary key as it is and add separate id field (serial in postgres and int with auto_increment in mysql) and let it be the primary key. That would also equire rewitite that part of code
Another possible, but very non-conventional way could be adding all but count fields into primary key.
Loooking into s9y database logs shows quite a number of this kind of error statements:
ERROR: duplicate key violates unique constraint "[prefix]referrers_pkey"
STATEMENT: INSERT INTO [prefix]referrers
(entry_id, day, count, scheme, host, port, path, query)
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart', 'query=somestring&src=web')
And the primary key is there as: PRIMARY KEY (host, "day", entry_id).
And database engine does its job correctly - blocks new inserts for a repeating combination of host, entry and date.
But that does not fit into real life, where one entry will on the same day receive clicks from search-engines with different keywords (rest is the same but query field is different) or same day clicks are otherwise coming from different urls of the same site (part and/or query fields are different).
Totally valid inserts would be:
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart2', 'query=somestring&src=web')
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart', 'query=somestring2&src=web')
VALUES (17, '2008-03-28', 1, 'http', 'www.somesite.net', '', '/urlpart2', 'query=somestring2&src=web')
But they will get rejected.
One possible solution would drop the primary key as it is and add separate id field (serial in postgres and int with auto_increment in mysql) and let it be the primary key. That would also equire rewitite that part of code
Another possible, but very non-conventional way could be adding all but count fields into primary key.
