concurrency - PostgreSQL - Duplicate Unique Key -

on table have secondary unique key labeled md5. before inserting, check see if md5 exists, , if not, insert it, shown below:

-- attempt find item select oresults (select domain_id db.domains "md5"=omd5);  if (oresults null)      -- attempt find domain     insert db.domains ("md5", "domain", "inserted")          values (omd5, odomain, now());      return currval('db.domains_seq');    end if; 

this works great single threaded inserts, problem when have 2 external applications calling function concurrently happen have same md5. end situation where:

app 1: sees md5 not exist

app 2: inserts md5 table

app 1: goes insert md5 table since thinks doesnt exist, gets error because right after seen not, app 2 inserted it.

is there more effective way of doing this?

can catch error on insert , if so, select domain_id?

thanks in advance!

---

this seems covered @ insert, on duplicate update in postgresql?

you go ahead , try insert md5 , catch error, if "unique constraint violation" error ignore , keep going, if other error bail out. way push duplicate checking right down database , race condition goes away.

something this:

• attempt insert md5 value.
  • if unique violation error, ignore , continue on.
  • if other error, bail out , complain.
  • if don't error, continue on.
• do select oresults (select domain_id db.domains "md5"=omd5) extract domain_id.

there might bit of performance hit "correct , little slow" better "fast broken".

eventually might end more exceptions successful inserts. try insert in table references (through foreign key) db.domains , trap fk violation there. if had fk violation, old "insert , ignore unique violations" on db.domains , retry insert gave fk violation. same basic idea, matter of choosing 1 throw least exceptions , go that.