Re: [GENERAL] INSERT WHERE NOT EXISTS

[Lincoln Yeoh]

That's why I resorted to "lock table", select, then insert/update. You have 
to block all the reads of other processes that are considering an insert.

This is not great for performance, but I was certain it will work, unlike 
the race-vulnerable suggestions (are people here actually using those? 
Whoa!), or the "end up with everything rolled back".

HOWEVER, IF (big if) all your applications and DB users can cooperate what 
you can do is lock a different dummy  table e.g. ForInsertsOnTableX, and 
thus implement an application layer "select for insert".

e.g.

lock table ForInsertsOnTableA;
select count(*) from TableA where field='foo';
if count=0 {
        insert into TableA ...
} else if count >0 {
        update TableA .... where field='foo';
} else {
        D'oh.... Can count ever return negative or null?
}
That way other applications that are just doing selects but not "selects 
for inserts" don't run into the table locks, and performance doesn't go 
down as much (coz there's no way to unlock a table in postgresql other than 
aborting or committing a transaction, so if your transaction takes 
time...). AFAIK this method should work on most RDBMS.

Now IF postgresql had a select for insert... But AFAIK that's nonstandard 
and requires discipline, but remembering to use select for update requires 
similar discipline too.

(Related: I also suggested arbitrary user locks years back, but I wasn't 
able to implement them.)

Summary: For postgresql if DB discipline is good and will remain good, you 
can use lock ForInsertsOnTableA, otherwise take the performance hit and 
lock TableA before select, insert/update.

If there's a reason why this won't work, or there are better solutions, I'd 
sure like to know :).

Regards,
Link.
At 04:23 PM 6/25/2003 -0400, Mike Mascari wrote:

But if two simultaneous "selects for update" fail to find rows, both
clients will then attempt the INSERT, which will cause one of them to
abort due to a unique key violation. In these "replace" scenarios, the
application must be prepared for the unique key violation with the
current version of PostgreSQL.
Mike Mascari
[EMAIL PROTECTED]
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
      joining column's datatypes do not match


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
     subscribe-nomail command to [EMAIL PROTECTED] so that your
     message can get through to the mailing list cleanly