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

The following bug has been logged online:

Bug reference:      5654
Logged by:          Daniel Howard
Email address:      cheeserolls@yahoo.com
PostgreSQL version: 8.4.4
Operating system:   Linux (Ubuntu 10.04.1)
Description:        Deferred Constraints don't work
Details:

The command
SET CONSTRAINTS ALL DEFERRED
seems to have no effect.

According to the manual here:
http://www.postgresql.org/docs/8.4/interactive/sql-set-constraints.html
If a constraint is defined as deferrable, then you can instruct postgres to
wait until the end of a transaction block before checking the constraint.
This is supposed to work for foreign key constraints.
The simple test case below demonstrates that postgres ignores the set
constraint command and checks the constraint in the middle of a
transaction.


-- Setup two tables, users and items.  One user can have many items.
CREATE TABLE users (id serial PRIMARY KEY, name text NOT NULL);
--NOTICE:  CREATE TABLE will create implicit sequence "users_id_seq" for
serial column "users.id"
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"users_pkey" for table "users"
--CREATE TABLE
INSERT INTO users (id, name) VALUES (1,'Daniel');
--INSERT 0 1
CREATE TABLE items (id serial PRIMARY KEY, user_id integer NOT NULL
REFERENCES users ON DELETE RESTRICT DEFERRABLE, itemname text);
--NOTICE:  CREATE TABLE will create implicit sequence "items_id_seq" for
serial column "items.id"
--NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
"items_pkey" for table "items"
--CREATE TABLE
INSERT INTO items (user_id, itemname) VALUES (1,'hat');
--INSERT 0 1
--
-- Expect the following to fail because of the foreign key constraint
DELETE FROM users;
--ERROR:  update or delete on table "users" violates foreign key constraint
"items_user_id_fkey" on table "items"
--DETAIL:  Key (id)=(1) is still referenced from table "items".
--
-- Try it in a transaction with the constraint deferred
BEGIN;
--BEGIN
SET CONSTRAINTS ALL DEFERRED;
--SET CONSTRAINTS
-- This time it should work, because the constraint shouldn't be checked
until the end of the transaction
DELETE FROM users;
--ERROR:  update or delete on table "users" violates foreign key constraint
"items_user_id_fkey" on table "items"
--DETAIL:  Key (id)=(1) is still referenced from table "items".
ROLLBACK;
--ROLLBACK

"Daniel Howard" <cheeserolls@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

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=