Re: Trigger violates foreign key constraint

Aleksander Alekseev <aleksander(at)timescale(dot)com> writes:
>> I agree with documenting this hazard, but I think it'd be better
>> to do so in the "Triggers" chapter. There is no hazard unless
>> you are writing user-defined triggers, which is surely far fewer
>> people than use foreign keys. So I suggest something like the
>> attached.

> I don't mind changing the chapter, but I prefer the wording chosen in
> v3. The explanation in v4 is somewhat hard to follow IMO.

Well, I didn't like v3 because I thought it was wrong, or at least
fairly misleading. The fact that we use triggers rather than some
other mechanism to invoke referential updates isn't really relevant.
The issue is that the update actions fire user-defined triggers,
and it's those that are the (potential) problem.

Perhaps we should leave the system triggers out of the discussion
entirely? More or less like:

If a foreign key constraint specifies referential actions (that
is, cascading updates or deletes), those actions are performed via
ordinary SQL update or delete commands on the referencing table.
In particular, any triggers that exist on the referencing table
will be fired for those changes. If such a trigger modifies or
blocks the effect of one of these commands, the end result could
be to break referential integrity. It is the trigger programmer's
responsibility to avoid that.

regards, tom lane