Thread: RI within PLPGSQL

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)

On Fri, 12 Dec 2003, cnliou wrote:

> It looks to me a referential integrity problem (only?)
> within PLPGSQL. Plesase see the test result below.

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.

Thank you very much for your explanation!

¡° Include¡m"Stephan Szabo" <sszabo@megazone.bigpanda.com>¡n
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.

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

On Sat, 13 Dec 2003, cnliou wrote:

> Thank you very much for your explanation!
>
> =A1=B0 Include=A1m"Stephan Szabo" <sszabo@megazone.bigpanda.com>=A1n
> 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.
>
> 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 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 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.

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.