Found the issue.  Try out the attached SQL in a fresh database.

I had honestly expected the second delete to work properly as nothing
had to be removed that table.

The rule was added as a temporary measure to protect the data
currently in the table -- without the intent of otherwise impeding the
other informations use. I suppose I forgot that the table wouldn't be
looked at as the rule is checked quite early.



CREATE TABLE junk_parent (
  col SERIAL PRIMARY KEY
);

INSERT INTO junk_parent DEFAULT VALUES;
INSERT INTO junk_parent DEFAULT VALUES;
INSERT INTO junk_parent DEFAULT VALUES;

CREATE TABLE junk (
  col int4 NOT NULL REFERENCES junk_parent(col) ON UPDATE CASCADE ON
DELETE CASCADE
);

INSERT INTO junk VALUES ('1');

DELETE FROM junk_parent WHERE col = 1;
DELETE FROM junk_parent WHERE col = 2;



--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
Thu, 5 Apr 2001 19:27:45 -0400
"Rod Taylor" <rbt@zort.ca>
Foreign Key & Rule confusion  WAS: Lost Trigger(s)?
"Rod Taylor" <rbt@zort.ca> writes:
> Found the issue.  Try out the attached SQL in a fresh database.

And?  AFAICT it behaves as expected, in either 7.0.2 or current ...

			regards, tom lane

Fri, 06 Apr 2001 01:54:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? 
Not quite as expected.  I didn't expect deleting the 2 from the
primary table to fail because the CASCADE DELETE wasn't able to run on
the second (even though no values existed in that table).  I suppose
it does run properly (blocks all delete attempts) -- but I just didn't
expect it to error out on values which didn't exist in the second
table -- thereby blocking the deletion from the primary or referred
table..

Tried against 7.1beta3 and 7.1beta5.

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rbt@zort.ca>
Cc: "Hackers List" <pgsql-hackers@postgresql.org>
Sent: Friday, April 06, 2001 1:54 AM
Subject: Re: [HACKERS] Foreign Key & Rule confusion WAS: Lost
Trigger(s)?


> "Rod Taylor" <rbt@zort.ca> writes:
> > Found the issue.  Try out the attached SQL in a fresh database.
>
> And?  AFAICT it behaves as expected, in either 7.0.2 or current ...
>
> regards, tom lane
>



Fri, 6 Apr 2001 08:53:37 -0400
"Rod Taylor" <rod.taylor@inquent.com>
Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? 
"Rod Taylor" <rod.taylor@inquent.com> writes:
> Not quite as expected.  I didn't expect deleting the 2 from the
> primary table to fail because the CASCADE DELETE wasn't able to run on
> the second (even though no values existed in that table).

But it *doesn't* fail.  At least not in the versions I tried.

			regards, tom lane

Fri, 06 Apr 2001 10:09:19 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? 
"Rod Taylor" <rod.taylor@inquent.com> writes:
> I must apologize, I was copying from one screen to another due to
> network outage and gave a bad example -- missed the most important
> part.

> There should have been an AS ON DELETE TO junk DO INSTEAD NOTHING;
> rule.

Ah so.  With that in place, I see what you are talking about:

regression=# DELETE FROM junk_parent WHERE col = 1;
ERROR:  SPI_execp() failed in RI_FKey_cascade_del()
regression=# DELETE FROM junk_parent WHERE col = 2;
ERROR:  SPI_execp() failed in RI_FKey_cascade_del()


> The RI_FKey_cascade_del() trigger fails on the second delete attempt.
> To me it should ignore the error if there wasn't anything to delete in
> the first place.

Well, I think the issue is something different.  Right now, referential
integrity triggers are implemented as issuing actual queries --- which
are subject to rule rewrites.  It strikes me that perhaps this is wrong,
and a referential integrity operation should proceed without regard to
rules.

If you think that rules indeed should be able to affect referential
integrity updates, then it would probably be better that neither of
these examples fail (ie, the RI triggers should not complain about their
queries having been rewritten to nothing).

I don't see a good argument for raising an error on the first delete and
not the second.  Either ref integrity is subject to rules, or it's not.

Next question: should a trigger be able to defeat an RI update?  That
can happen now, too.

			regards, tom lane

Fri, 06 Apr 2001 11:20:33 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? 
Ack...

All my current history keeping methods are done via triggers on tables
(generally set off by various RI_ triggers).  Not real good if it
didn't set off those triggers for me.

I'm sure rules are a ditto in that case for others.

I was hoping for a way to prevent the RI trigger from failing if there
wasn't anything to do anyway -- SELECT FOR DELETE -- if no results
ignore, if there were results delete the results.  Delete does a
search anyway, this would lock the rows and later get rid of them.   A
hack, and I have no idea how it would pan out -- but that's would
produce what I expected to happen.

Otherwise I change all the ON DELETE DO INSTEAD NOTHING rules to
triggers which see if the parent still exists (and doesn't allow
deletion if it does) otherwise it cancels the delete.  Not a nice
solution.

--
Rod Taylor

There are always four sides to every story: your side, their side, the
truth, and what really happened.
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: "Rod Taylor" <rod.taylor@inquent.com>
Cc: <pgsql-hackers@postgreSQL.org>
Sent: Friday, April 06, 2001 11:20 AM
Subject: Re: [HACKERS] Foreign Key & Rule confusion WAS: Lost
Trigger(s)?


> "Rod Taylor" <rod.taylor@inquent.com> writes:
> > I must apologize, I was copying from one screen to another due to
> > network outage and gave a bad example -- missed the most important
> > part.
>
> > There should have been an AS ON DELETE TO junk DO INSTEAD NOTHING;
> > rule.
>
> Ah so.  With that in place, I see what you are talking about:
>
> regression=# DELETE FROM junk_parent WHERE col = 1;
> ERROR:  SPI_execp() failed in RI_FKey_cascade_del()
> regression=# DELETE FROM junk_parent WHERE col = 2;
> ERROR:  SPI_execp() failed in RI_FKey_cascade_del()
>
>
> > The RI_FKey_cascade_del() trigger fails on the second delete
attempt.
> > To me it should ignore the error if there wasn't anything to
delete in
> > the first place.
>
> Well, I think the issue is something different.  Right now,
referential
> integrity triggers are implemented as issuing actual queries ---
which
> are subject to rule rewrites.  It strikes me that perhaps this is
wrong,
> and a referential integrity operation should proceed without regard
to
> rules.
>
> If you think that rules indeed should be able to affect referential
> integrity updates, then it would probably be better that neither of
> these examples fail (ie, the RI triggers should not complain about
their
> queries having been rewritten to nothing).
>
> I don't see a good argument for raising an error on the first delete
and
> not the second.  Either ref integrity is subject to rules, or it's
not.
>
> Next question: should a trigger be able to defeat an RI update?
That
> can happen now, too.
>
> regards, tom lane
>


Fri, 6 Apr 2001 11:51:57 -0400
"Rod Taylor" <rod.taylor@inquent.com>
Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? 