Thread: Foreign Key & Rule confusion WAS: Lost Trigger(s)?
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.
"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
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 >
"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
"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
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 >