Skip site navigation (1) Skip section navigation (2)

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

From: Cesar Eduardo Barros <cesarb(at)elnetcorp(dot)com(dot)br>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: INSERT .. SELECT should redo SELECT if a duplicate key is found
Date: 2002-07-06 17:04:26
Message-ID: 20020706170426.GC26802@cerberus.elnet.grupomk (view raw or flat)
Thread:
Lists: pgsql-bugs
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



In response to

pgsql-bugs by date

Next:From: Francis ReaderDate: 2002-07-08 13:13:28
Subject: Possible Bug?
Previous:From: Cesar Eduardo BarrosDate: 2002-07-06 16:10:36
Subject: Move constant evaluation to inside IN subselect

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group