Hi!
It looks to me a referential integrity problem (only?)
within PLPGSQL. Plesase see the test result below.
Thank you!
CN
=======
CREATE TABLE test1(c1 INTEGER PRIMARY KEY) WITHOUT OIDS;
CREATE TABLE test2
( c1 INTEGER,
c2 INTEGER,
PRIMARY KEY (c1,c2),
CONSTRAINT ctest2 FOREIGN KEY (c1) REFERENCES test1 (c1)
ON UPDATE CASCADE ON DELETE CASCADE
) WITHOUT OIDS;
CREATE OR REPLACE FUNCTION f1(int) RETURNS VOID AS '
BEGIN
DELETE FROM test1 WHERE c1= $1;
INSERT INTO test1 VALUES($1);
INSERT INTO test2 VALUES($1,2);
RETURN;
END' LANGUAGE 'plpgsql' STABLE;
CREATE OR REPLACE FUNCTION f2(int) RETURNS VOID AS '
BEGIN
DELETE FROM test2 WHERE c1= $1;
DELETE FROM test1 WHERE c1= $1;
INSERT INTO test1 VALUES($1);
INSERT INTO test2 VALUES($1,2);
RETURN;
END' LANGUAGE 'plpgsql' STABLE;
db1=# select f1(1);
f1
----
(1 row)
db1=# select * from test1; select * from test2;
c1
----
1
(1 row)
c1 | c2
----+----
1 | 2
(1 row)
db1=# select f1(1);
ERROR: duplicate key violates unique constraint
"test2_pkey"
CONTEXT: PL/pgSQL function "f1" line 5 at SQL statement
db1=# select * from test1; select * from test2;
c1
----
1
(1 row)
c1 | c2
----+----
1 | 2
(1 row)
db1=# select f2(1);
f2
----
(1 row)
db1=# select * from test1; select * from test2;
c1
----
1
(1 row)
c1 | c2
----+----
(0 rows)
