Re: Referential Integrity

"Alexander Stanier" <alexander(dot)stanier(at)egsgroup(dot)com> writes:
> [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]

> CREATE FUNCTION test() RETURNS INT4 AS '
> DECLARE
> v_return INTEGER;
> BEGIN
> DELETE FROM alex
> WHERE aid =3D ''1''
> AND bid =3D ''1'';

> INSERT INTO alex (aid,bid,itemdesc)
> VALUES (''1'',''1'',''OneOne'');

> INSERT INTO alexette (aid,bid,vcode)
> VALUES (''1'',''1'',''V'');

> RETURN 0;
> END;
> ' LANGUAGE 'plpgsql';

The cascaded deletes will be implemented at end of statement --- which
I believe is always taken to be the end of the current interactive
statement, ie, the "SELECT test()" you typed. So they haven't happened
yet at the time the function tries to do the inserts.

This is probably a bug, but IIRC, it's not entirely obvious what to do
instead. I seem to recall some inconclusive discussions in pgsql-hackers
about designing a better rule for when to fire the RI actions. Check
the list archives for details.

regards, tom lane

On Mon, 10 Feb 2003, Tom Lane wrote:

> "Alexander Stanier" <alexander(dot)stanier(at)egsgroup(dot)com> writes:
> > [ expected ON DELETE CASCADE doesn't seem to happen in this context: ]
>
> > CREATE FUNCTION test() RETURNS INT4 AS '
> > DECLARE
> > v_return INTEGER;
> > BEGIN
> > DELETE FROM alex
> > WHERE aid =3D ''1''
> > AND bid =3D ''1'';
>
> > INSERT INTO alex (aid,bid,itemdesc)
> > VALUES (''1'',''1'',''OneOne'');
>
> > INSERT INTO alexette (aid,bid,vcode)
> > VALUES (''1'',''1'',''V'');
>
> > RETURN 0;
> > END;
> > ' LANGUAGE 'plpgsql';
>
> The cascaded deletes will be implemented at end of statement --- which
> I believe is always taken to be the end of the current interactive
> statement, ie, the "SELECT test()" you typed. So they haven't happened
> yet at the time the function tries to do the inserts.
>
> This is probably a bug, but IIRC, it's not entirely obvious what to do
> instead. I seem to recall some inconclusive discussions in pgsql-hackers
> about designing a better rule for when to fire the RI actions. Check
> the list archives for details.

Yeah, I don't think we came to a conclusion. Looking at SQL99, the
"correct" behavior seems to be that the actions occur as part of the
actual delete or update action so that effectively at the "same" point in
time the dependant rows are changed, using (I think) 14.14 and 14.20 as a
guide. This is significantly different from what we do AFAICT though. :(
(For example, I'm not sure if 14.14 implies that after triggers from the
statement inside the function to occur as part of that statement rather
than after the function ends)

This was originally logged under pgsql-sql, as "[SQL] Referential
Integrity", but after some discussion it seems that this is a bug.

I have the following scenario in pg7.3.1.:

CREATE TABLE alex (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
itemdesc varchar(100),
CONSTRAINT alex_pkey PRIMARY KEY (aid, bid));

CREATE TABLE alexette (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
vcode varchar(1),
CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));

ALTER TABLE alexette
ADD CONSTRAINT alexette_fk_1 FOREIGN KEY (aid,bid)
REFERENCES alex (aid,bid)
ON DELETE CASCADE;

INSERT INTO alex VALUES ('1','1','OneOne');

INSERT INTO alexette VALUES ('1','1','V');

CREATE FUNCTION test() RETURNS INT4 AS '
DECLARE
v_return INTEGER;
BEGIN
DELETE FROM alex
WHERE aid = ''1''
AND bid = ''1'';

INSERT INTO alex (aid,bid,itemdesc)
VALUES (''1'',''1'',''OneOne'');

INSERT INTO alexette (aid,bid,vcode)
VALUES (''1'',''1'',''V'');

RETURN 0;
END;
' LANGUAGE 'plpgsql';

Please could someone tell me why running the function test() causes the
following error:

"Cannot insert a duplicate key into unique index alexette_pkey"

The foreign key with "on delete cascade" should ensure that the delete
statement within this function should delete the records from both alex
and alexette. Indeed a simple "delete from alex" demonstrates that this
does indeed work. However, it appears that within the function this is
not happening or that the insert statements cannot see the full extent
of the changes made by the delete statement. Whichever, surely this is
wrong?

Regards,

Alexander Stanier.
mailto:alex(at)egsgroup(dot)com