Re: How can I prevent duplicate inserts by multiple concurrent threads ? - Mailing list pgsql-novice

Sean Davis wrote:

Any suggestions, tips ? Am I at least thinking along the right lines ?
Should I be getting a table lock instead ? What is the recommended way
to prevent duplicate inserts in a concurrent situation like this ?

Is there a reason that you cannot use a UNIQUE constraint?  

Right, maybe that's what I should be doing. The reason I didn't do that initially was because creating new data can be *very* expensive. We have about a 100 different chunks and if say 10 threads hit the database and each thread re-creates 100 chunks, we will end up trying to save 100*10 (1000) chunks, 900 of which will be rightly rejected because of the uniqueness constraint, so that time spent in creating those chunks would have been wasted to the annoyance of the clients.

It would be nice to see if that data already exists before we try to re-insert it....but how ? the "select for update" doesn't seem to be working as I hoped..

Best,
--j