Re: BEFORE triggers that return NULL can circumvent referential integrity

Jim Finnerty <jfinnert@amazon.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