Re: INSERT WHERE NOT EXISTS

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
>mascarm(at)mascari(dot)com
>
>
>---------------------------(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