Re: Foreign Key & Rule confusion WAS: Lost Trigger(s)? - Mailing list pgsql-hackers

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
>