Re: BUG #5654: Deferred Constraints don't work

Thank you Tom for your clear and swift reply.
In case others need it, I'll briefly explain why this issue came about and how I eventually solved it.
I am working on a web application which uses postgres as a back end database.  For unit testing purposes I have set up a test database and a test user.  The tests require the database to be reset to a predefined state (database fixtures).  I do not want the scripts that handle resetting the database to need to know in which order to delete data from tables before reloading the fixture data.  I thought that if all my foreign-key constraints were deferrable, and I ran the whole thing in a transaction with constraints deferred, then I would be able to delete and add the data in any order I wanted, provided it was all referentially correct at the end.
However, because of the behavior you explained, the scripts were failing when they tried to delete a rows with foreign key constraints.
One proposed solution was to run the tests as a superuser, and disable all table triggers, then enable at the end.  I rejected this because firstly running tests as a superuser is asking for trouble, and I was also worried what state it would leave the database in if the supplied data was not referentially correct.
A better solution in my view is to use the postgres TRUNCATE command, instead of DELETE to remove the rows.
Documentation for TRUNCATE:TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.
If you issue the command "TRUNCATE tablename CASCADE" then the data in the table is removed without doing the referential integrity checks.  It is safe to do this, because if there are any foreign key constraints, then the dependent tables are truncated too.
This is perfect for my situation.  Not only can I safely remove the data in preparation for a unit test, but I can do so more quickly than using DELETE.
After that, I can safely insert the data in any order because of the described behavior of SET CONSTRAINTS DEFERRED;
My transaction now looks like this:
BEGIN;SET CONSTRAINTS ALL DEFERRED;TRUNCATE table1 CASCADE;TRUNCATE table2 CASCADE;  ....etcINSERT INTO table1 VALUES blah blah ...INSERT INTO table2 VALUES blah blah ...etcCOMMIT;
Best regards, Daniel

--- On Mon, 13/9/10, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Subject: Re: [BUGS] BUG #5654: Deferred Constraints don't work
To: "Daniel Howard" <cheeserolls(at)yahoo(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Date: Monday, 13 September, 2010, 16:08

"Daniel Howard" <cheeserolls(at)yahoo(dot)com> writes:
> The command
> SET CONSTRAINTS ALL DEFERRED
> seems to have no effect.

Yes it does.  For instance, in your example setting the mode to deferred
will allow you to insert an items row that doesn't match any users row:

regression=# insert into items(user_id) values(42);
ERROR:  insert or update on table "items" violates foreign key constraint "items_user_id_fkey"
DETAIL:  Key (user_id)=(42) is not present in table "users".
regression=# begin;
BEGIN
regression=# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
regression=# insert into items(user_id) values(42);
INSERT 0 1
regression=# commit;
ERROR:  insert or update on table "items" violates foreign key constraint "items_user_id_fkey"
DETAIL:  Key (user_id)=(42) is not present in table "users".
regression=#

What you wrote is

> CREATE TABLE items (id serial PRIMARY KEY, user_id integer NOT NULL
> REFERENCES users ON DELETE RESTRICT DEFERRABLE, itemname text);

The ON DELETE RESTRICT part is a "referential action", not a constraint
as such.  Our reading of the SQL standard is that referential actions
happen immediately regardless of deferrability of the constraint part.
So that's why you get an error on deletion of a users row.

            regards, tom lane