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)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

On Fri, 12 Dec 2003, cnliou wrote:

There have been discussions in the past about when cascade events
should occur. The code currently does what I believe was last
agreed upon, although its behavior is fairly wierd for deferred
constraints and functions. Right now the cascade happens at the
end of the full statement (in this case the call to the function)
which is why you get a key constraint error in the second call to f1
and why the later inserted row is removed in f2.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Thank you very much for your explanation!

¡° Include¡m"Stephan Szabo" <sszabo (AT) megazone (DOT) bigpanda.com>¡n
wrote:
It sounds to me that the only solution to my case is
executing

DELETE FROM referenced_table

and

INSERT INTO referencing_table

in seperate transactions. Please correct me if I am wrong.

I also feel it might be a good idea to include an example
like the one in my previous message in the documentation so
that this question hopefully will not be asked repeatedly.

Regards,

CN

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Sat, 13 Dec 2003, cnliou wrote:

I think they only need to be in separate outer statements for non-deferred
triggers. It's just that the full set of triggered actions for the
function count as part of the one statement that calls it.

So, from psql, sending separate statements
DELETE FROM ... ;
INSERT INTO ... ;
should work, but a function body
'DELETE FROM ...;
INSERT INTO ...;'
counts as one statement and so the delete action happens after the insert.

I think the behavior of this hasn't entirely solidified yet. It's still
possible that it'll change as although we came to a behavior set, if it
can be shown to break the spec's requirements, it'll be changed.

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings