Re: INSERT .. SELECT should redo SELECT if a duplicate key is found

On Sat, Jul 06, 2002 at 12:18:56PM -0300, wrote:
>
> When you are trying to insert a row in a table with a UNIQUE constraint,
> unless it already exists, you can try something like:
>
> INSERT INTO table (id) SELECT val WHERE NOT EXISTS (SELECT 1 FROM table WHERE id = val)
>
> However, this does not work as expected if another backend inserts a row
> with the same unique column(s).
>

The same thing also happens with DELETE:

psql 1:
teste=# create table teste (id integer primary key, parent integer references teste (id));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'teste_pkey' for table 'teste'
NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
teste=# insert into teste values (1, null);
INSERT 826882 1
teste=# begin;
BEGIN

psql 2:
teste=# begin;
BEGIN
teste=# select 1 from teste where id = 1 for update;
?column?
----------
1
(1 row)

psql 1:
teste=# delete from teste where not exists (select 1 from teste where parent = 1);
[sits there waiting]

psql 2:
teste=# insert into teste values (2,1);
INSERT 826884 1
teste=# commit;
COMMIT

psql 1:
ERROR: <unnamed> referential integrity violation - key in teste still referenced from teste
teste=# commit;
COMMIT

So, the problem is probably with the subselects. I wonder if UPDATE has
the same problem.

--
Cesar Eduardo Barros
ElNet Hightech -- Administrador de Sistemas Unix
cesarb(at)elnetcorp(dot)com(dot)br