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