Re: conditional insert

At 07:02 PM 9/5/2011, J. Hondius wrote:
>I agree that there are better ways to do this.
>But for me this works. (legacy driven situation)
>
>INSERT INTO tbinitialisatie (col1, col2)
> SELECT 'x', 'y'
> FROM tbinitialisatie
> WHERE not exists (select * from tbinitialisatie where col1 = 'x'
> and col2 = 'y')
> LIMIT 1

Hi,

That does not work 100%. Try it with two psql instances.

Do:
*** psql #1
begin;
INSERT INTO tbinitialisatie (col1, col2)
SELECT 'x', 'y'
FROM tbinitialisatie
WHERE not exists (select * from tbinitialisatie where col1 = 'x'
and col2 = 'y')
LIMIT 1 ;

*** psql #2
begin;
INSERT INTO tbinitialisatie (col1, col2)
SELECT 'x', 'y'
FROM tbinitialisatie
WHERE not exists (select * from tbinitialisatie where col1 = 'x'
and col2 = 'y')
LIMIT 1 ;
commit;

*** psql #1
commit;

You should find duplicate inserts.

In most cases the "begin" and "commit" are very close together so you
won't notice the problem. But one day you might get unlucky.

Your options are:
a) put a unique constraint and handle the insert errors when they occur
b) lock the entire table first (affects performance: blocks all
selects on that table)
c) use a lock elsewhere (but this requires all applications using the
database to cooperate and use the lock).
d) wait for SQL MERGE to be implemented ( but from what I see the
current proposal seems to require a) or b) anyway:
http://wiki.postgresql.org/wiki/SQL_MERGE )

You could do both a) and b) too. Or both a) and c) (if you don't want
insert errors in the cooperating apps and want to allow other selects
during the transaction).

Regards,
Link.