Re: BEFORE triggers that return NULL can circumvent referential integrity

Jim Finnerty <jfinnert(at)amazon(dot)com> writes:
> The non compliance issue reported previously (concerning the potential
> firing of user-defined AFTER triggers prior to firing immediate-mode foreign
> key constraint triggers) still guaranteed that the database would be left in
> a consistent state at the end of each statement. Even though you could run
> some SQL inside of a trigger that had access to inconsistent state, if that
> state was inconsistent at the end of the statement the statement would still
> fail; however, exploiting a PostgreSQL feature of BEFORE triggers allows
> users to run SQL statements that do not fail or rollback, and that can
> commit the database in a state that is inconsistent with the foreign key
> constraints.

Yes. This is known, and documented, and we don't really intend to change
it.

> This is clearly not the
> intended behavior according to the PostgreSQL documentation

What documentation? If there's a contrary promise somewhere, we need
to fix that. The CREATE TRIGGER page is pretty clear about it though:

There is also nonstandard behavior if BEFORE triggers modify rows or
prevent updates during an update that is caused by a referential
action. This can lead to constraint violations or stored data that
does not honor the referential constraint.

regards, tom lane