Re: Troubleshooting SPI_execp() failed in RI_FKey_cascade_del()

I think my general complaint is that the error message is so inscrutable.
The actual SQL code was the result of two separate thoughts, implemented
on different days. The original 'on delete do' was actually 'instead
update .....' which on further reflection would never work since the the
constraints would have killed the transaction anyway. So I was wrong to
write that in the first place - I think it might have been nice to know
that prior to digging into the source code for
backend/utils/adt/ri_trigger.c and backend/executor/spi.c.

I suppose it's not actually that important or anything especially since
it's a "don't do that" sort of thing. I appreciated the kick in the pants
regardless.

Thanks much,
Joshua b. Jore ; http://www.greentechnologist.org

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.
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>