Thread: Inserting possible dublicate unique keys
Hi, what is the best method to make concurrent inserts to a table with unique/primary key? Scenario: I write a DBI logger for Apache, and this uses a table for all referers: CREATE TABLE referer ( id SERIAL, referer varchar(2048) NOT NULL PRIMARY KEY ); so, you can imagine that there are two accesses with the same referer at the same time; at logging time, each process looks if there is already an entry for this referer and catches its id, but if not, it inserts the new referer. So, it is possible that two processes trying to insert the same primary key into the table. My solution is: if transaction is broken, I restart the hole transaction (there are more then one inserts like this for each request) a second time. But i can not be sure that the transaction is aborted because a dublicate unique key, and it seems to me not the most elegant solution. Any other ideas?!? Ciao Alvar -- AGI Magirusstrasse 21B, 70469 Stuttgart Fon +49 (0)711.228 74-50, Fax +49 (0)711.228 74-88 +++news+++news+++news+++ Beste Image-Website 2001 kommt von AGI http://www.agi.de/tagebuch http://www.agi.com/diary (english)
From: "Alvar Freude" <alvar.freude@gate.agi.de> > Hi, > > what is the best method to make concurrent inserts to a table with > unique/primary key? > > Scenario: > I write a DBI logger for Apache, and this uses a table for all referers: > > > CREATE TABLE referer ( > id SERIAL, > referer varchar(2048) NOT NULL PRIMARY KEY > ); > > > so, you can imagine that there are two accesses with the same referer at > the same time; at logging time, each process looks if there is already > an entry for this referer and catches its id, but if not, it inserts the > new referer. Why have you got id as a serial if referer is your primary key? Oh - I suppose it's easier to reference a serial of course, less data to carry around. > So, it is possible that two processes trying to insert the same primary > key into the table. Well, they'll try. > My solution is: if transaction is broken, I restart the hole transaction > (there are more then one inserts like this for each request) a second > time. But i can not be sure that the transaction is aborted because a > dublicate unique key, and it seems to me not the most elegant solution. I don't see an alternative if you wrap several inserts into a transaction. You're saying you want all to succeed or none of them. I presume you're doing this for performance reasons. There's really no easy way around this AFAIK - no matter what you do there is always the possibility that another process is inserting the same referrer as you in parallel. The only thing I can think of is to insert into a staging table where referer isn't unique and insert into the real table from a snapshot of that staging table. Not sure that's a cleaner solution than yours though. - Richard Huxton
Hi, Richard Huxton wrote: > > > CREATE TABLE referer ( > > id SERIAL, > > referer varchar(2048) NOT NULL PRIMARY KEY > > ); [...] > > Why have you got id as a serial if referer is your primary key? Oh - I > suppose it's easier to reference a serial of course, less data to carry > around. oh, in reality the id is an int4 with unique index and nectval(...) ;-) > The only thing I can think of is to insert into a staging table where > referer isn't unique and insert into the real table from a snapshot of that > staging table. Not sure that's a cleaner solution than yours though. hm, but for this I have to change IDs in the Referencing Log-Table later .... Now it works since two days on the production server with the solution "try once again if error" and it seems it works good :-) Ciao Alvar -- AGI Magirusstrasse 21B, 70469 Stuttgart Fon +49 (0)711.228 74-50, Fax +49 (0)711.228 74-88 +++news+++news+++news+++ Beste Image-Website 2001 kommt von AGI http://www.agi.de/tagebuch http://www.agi.com/diary (english)