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.
Trouble: [prefix]referrers_pkey in [prefix]referrers table
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: Trouble: [prefix]referrers_pkey in [prefix]referrers tab
Hi!
Yeah, this issue has been reported a few times already...sadly nobody yet volunteered to rewrite the referrer tracking code...
Regards,
Garvin
Yeah, this issue has been reported a few times already...sadly nobody yet volunteered to rewrite the referrer tracking code...
Regards,
Garvin
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
Re: Trouble: [prefix]referrers_pkey in [prefix]referrers tab
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 wrote:Yeah, this issue has been reported a few times already...sadly nobody yet volunteered to rewrite the referrer tracking code
-
- Core Developer
- Posts: 30022
- Joined: Tue Sep 16, 2003 9:45 pm
- Location: Cologne, Germany
- Contact:
Re: Trouble: [prefix]referrers_pkey in [prefix]referrers tab
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
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
# Garvin Hicking (s9y Developer)
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/
# Did I help you? Consider making me happy: http://wishes.garv.in/
# or use my PayPal account "paypal {at} supergarv (dot) de"
# My "other" hobby: http://flickr.garv.in/