Re: ON DELETE SET NULL clauses do error when more than two columns are referenced to one table

Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
>> I'm not sure what to do about this. We could change the order the
>> triggers are fired to breadth-first. If all the setnull triggers were
>> executed first, there would be no problem. But that seems like a pretty
>> big change, and I'm afraid it might have other unintended consequences.
>
> I think it's not so much that they should be "breadth first" as that the
> updates generated by the triggers shouldn't count as their own
> sub-statements.  The trigger events generated by those updates need to
> go at the end of the outer statement's trigger queue.  We'll need to
> change the API of SPI_execute_snapshot for this, but since that's only
> for the use of the RI triggers anyway, it doesn't seem like a problem.
>
> I also notice that only one of the
> afterTriggerMarkEvents/afterTriggerInvokeEvents pairs in trigger.c
> is coded as a "while" ... they probably all must be if we expect that RI
> triggers will generate events at the same trigger level.

I can take a stab at writing a patch.

Does this need to be backported? It's a bug, but I feel uncomfortable
backporting. I'm afraid it'll break some other corner cases, and it
doesn't seem to be a huge problem in practice since no-one's ran into it
until now.

--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com