Re: Troubleshooting SPI_execp() failed in RI_FKey_cascade_del()

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.

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?

regards, tom lane