Trouble: [prefix]referrers_pkey in [prefix]referrers table

[jemm4jemm]

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.

[garvinhicking]

Hi!

Yeah, this issue has been reported a few times already...sadly nobody yet volunteered to rewrite the referrer tracking code...

Regards,
Garvin

[jemm4jemm]

Yeah, this issue has been reported a few times already...sadly nobody yet volunteered to rewrite the referrer tracking code

How much logic is there on the php side? Can't we achieve reasonable result with just changes in sql - something in the lines that I've proposed.

[garvinhicking]

Hi!

The problem is that the referrer tracking code (which is tightly hooked up on the Exit tracking code as well) is ages old, before the time I joined the project. It is quite strange because it also uses "rejection" (suppress) logic that I never fully got a grasp of.

So, there sadly is a lot of PHP logic that fills and queries this table...

Regards,
Garvin