Re: Troubleshooting SPI_execp() failed in RI_FKey_cascade_del()

On Wed, 12 Jun 2002, Tom Lane wrote:

> Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> > On Wed, 12 Jun 2002, Joshua b. Jore wrote:
> >> The following sql demonstrates the problem. What I'm getting at here is
> >> cases where the rows in "b" are being altered instead of being deleted
> >> consequently the delete to "a" shouldn't happen.
>
> > When you do the delete from a, the constraint will no longer
> > be satisifed. It throws the error to prevent the delete
> > from working.
>
> While I agree that some error should be thrown here, the actual behavior
> seems all wrong. It looks to me like we're getting an error as a result
> of an internal crosscheck that happens to be unhappy because of the
> particular result that spi.c returns when a rule rewrites a DELETE to
> "do instead nothing". This is so fragile --- it could break anytime
> someone decides to clean up any of several routines. What's worse,
> it will not detect interference from a rule that rewrites DELETEs in
> any way less drastic than "do instead nothing", even if the rule has
> the effect of suppressing the particular delete we need to do.

Yep, which is why I'd said that I'd want to disallow it (assuming
that fk uses DELETE). Of course that still wouldn't probably help
before delete triggers that returned NULL in some cases.

> I think we are looking at another effect of the foreign-key
> implementation being based on much higher-level operations than it
> should be. Would it be feasible to tweak the SELECTs in these
> RI triggers to extract CTIDs for the target rows, and then invoke
> heap_delete without going through a DELETE command?

Well, we'd still need to do trigger firing for other foreign keys,
and possibly if users had logging triggers and such those should
fire (I'm not sure what SQL99 says, I have it at work, but not here).