Re: DEFERABLE vs. NOT DEFERABLE constraints

On Tue, 24 Jul 2007, Claude Chaloux wrote:

> I will be switching from PostgreSQL 7.4 to 8.2 operationally. So far,
> the transition goes smoothly but one thing is giving me headache when
> trying to make some pieces of code work on my newer version of PSQL,
> especially the code of one very simple function.
>
> Basically, the function inserts a row in a table (in the database I'm
> testing with) that has a reference (foreign key set) to another table.
> Under 7.4, I have no problem inserting the rows in the two tables even
> when inserting in the table which column is being referenced first. On
> 8.2 however, I get an error message "constraint violation".

In 7.4, a not deferred trigger caused by a statement in a function was
still delayed until the end of the statement that called the function. As
of 8.0, a not deferred trigger is called at the end of the statement in
the function.

I think the beginning of the following thread describes part of the
reasoning:
 http://archives.postgresql.org/pgsql-hackers/2004-09/msg00140.php