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 databas=
e. =A0For unit testing purposes I have set up a test database and a test us=
er. =A0The tests require the database to be reset to a predefined state (da=
tabase fixtures). =A0I do not want the scripts that handle resetting the da=
tabase to need to know in which order to delete data from tables before rel=
oading the fixture data. =A0I thought that if all my foreign-key constraint=
s were deferrable, and I ran the whole thing in a transaction with constrai=
nts 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 wh=
en 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. =A0I 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 refe=
rentially correct.
A better solution in my view is to use the postgres TRUNCATE command, inste=
ad 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 re=
claims disk space immediately, rather than requiring a subsequent VACUUM op=
eration. 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. =A0It is s=
afe to do this, because if there are any foreign key constraints, then the =
dependent tables are truncated too.
This is perfect for my situation. =A0Not only can I safely remove the data =
in preparation for a unit test, but I can do so more quickly than using DEL=
ETE.
After that, I can safely insert the data in any order because of the descri=
bed behavior of SET CONSTRAINTS DEFERRED;
My transaction now looks like this:
BEGIN;SET CONSTRAINTS ALL DEFERRED;TRUNCATE table1 CASCADE;TRUNCATE table2 =
CASCADE; =A0....etcINSERT INTO table1 VALUES blah blah ...INSERT INTO table=
2 VALUES blah blah ...etcCOMMIT;
Best regards, Daniel


--- On Mon, 13/9/10, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

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

Yes it does.=A0 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=3D# insert into items(user_id) values(42);
ERROR:=A0 insert or update on table "items" violates foreign key constraint=
 "items_user_id_fkey"
DETAIL:=A0 Key (user_id)=3D(42) is not present in table "users".
regression=3D# begin;
BEGIN
regression=3D# SET CONSTRAINTS ALL DEFERRED;
SET CONSTRAINTS
regression=3D# insert into items(user_id) values(42);
INSERT 0 1
regression=3D# commit;
ERROR:=A0 insert or update on table "items" violates foreign key constraint=
 "items_user_id_fkey"
DETAIL:=A0 Key (user_id)=3D(42) is not present in table "users".
regression=3D#=20

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.=A0 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.

=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 regards, tom lane



=20=20=20=20=20=20=