Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UfwPR-0003tk-3n for pgsql-docs@arkaria.postgresql.org; Fri, 24 May 2013 18:08:15 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1UfwPH-0005ae-51 for pgsql-docs@arkaria.postgresql.org; Fri, 24 May 2013 18:07:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1UfwPG-0005aY-5n for pgsql-docs@postgresql.org; Fri, 24 May 2013 18:07:58 +0000 Received: from mail-bk0-x232.google.com ([2a00:1450:4008:c01::232]) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1UfwP7-00068u-IO for pgsql-docs@postgresql.org; Fri, 24 May 2013 18:07:57 +0000 Received: by mail-bk0-f50.google.com with SMTP id ik8so1714046bkc.37 for ; Fri, 24 May 2013 11:07:48 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:subject :content-type:x-gm-message-state; bh=GzF5roGjjVAcvehHxBi1MwhZ6pogJ6uVXuWkJZBTYAk=; b=c6j8ZV9VAnSXUWFnZKuekr0cFxn/b5Rg5IXy5p8i04ZUhff7lTVU50NTJhwYmqgm1C Hjm37h1FW88+WERdEjsXDsWn4ta/wEDAjxvyLpcUsF9+8RHcHV65LE4o0yj/HUUukU45 7jdD+iO7HDQ0BHhOmhM0i8Ajv9zMWw6ikuDIviBsAvUCxixbYuetpmCP4A0s0XT+VrR3 xwX84hQRYIsKgdkr1uZ/+k3kLbs2eDteRG/Ll4Gje5rxaHUCuEOxfz4mnmN2rU3eyL3E yAs7GQqwRQJihOx2ErqdTnjZPbtZ+9Z4+bntqHfOegO269epFwVPiHMAuKLqbtDEEXBS n7SQ== X-Received: by 10.204.63.1 with SMTP id z1mr402193bkh.148.1369418868599; Fri, 24 May 2013 11:07:48 -0700 (PDT) Received: from [192.168.1.25] (gateway.falter.at. [62.116.9.170]) by mx.google.com with ESMTPSA id iy11sm4763101bkb.11.2013.05.24.11.07.46 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Fri, 24 May 2013 11:07:47 -0700 (PDT) Message-ID: <519FAC78.7090603@falter.at> Date: Fri, 24 May 2013 20:07:52 +0200 From: Erwin Brandstetter User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:17.0) Gecko/20130509 Thunderbird/17.0.6 MIME-Version: 1.0 To: pgsql-docs@postgresql.org Subject: Contradicting information for DEFERRABLE constraints Content-Type: multipart/alternative; boundary="------------030407080806050301080505" X-Gm-Message-State: ALoCoQlSEUPr5tkPbbN9EOBXMS2kcA39YhVYhUtqaPdZaxJVMqyCOazMPS7DvdFICspbQofZNcw5 X-Pg-Spam-Score: -1.9 (-) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-docs Precedence: bulk Sender: pgsql-docs-owner@postgresql.org This is a multi-part message in MIME format. --------------030407080806050301080505 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit Hi! The manual states here: http://www.postgresql.org/docs/current/static/sql-createtable.html > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately *after every command*. > Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS > command). NOT DEFERRABLE is the default. Currently, only UNIQUE, > PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable. > > INITIALLY IMMEDIATE > INITIALLY DEFERRED > > If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is > checked *after each statement*. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. > The constraint check time can be altered with the SET CONSTRAINTS command. > This directly contradicts the information further down: > > Non-deferred Uniqueness Constraints > > When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately *whenever a row is inserted or modified*. > The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, *a single > command* updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY > IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking. > Bold emphasis mine. My tests on Postgres 9.1 and 9.2 seem to confirm that the check for non-deferrable constraints happens *after every row*, not after every command. So it should be: > DEFERRABLE > NOT DEFERRABLE > > This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every inserted or > modified row. Checking of constraints that are deferrable happens after every statement and can be postponed until the end of the transaction > (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. > Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are > not deferrable. > Also, this important difference is completely ignored in the documentation of SET CONSTRAINTS, where the behaviour of non-deferrable constraints is treated as being equal to DEFERRABLE IMMEDATE (which it is not): http://www.postgresql.org/docs/current/interactive/sql-set-constraints.html > The third class is always IMMEDIATE. Here is a test case to play with: CREATE TEMP TABLE t1 ( id integer ,CONSTRAINT t_pkey PRIMARY KEY (id) ); CREATE TEMP TABLE t2 ( id integer ,CONSTRAINT t2_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE ); CREATE TEMP TABLE t3 ( id integer ,CONSTRAINT t3_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED ); INSERT INTO t1 VALUES (1), (2); INSERT INTO t2 VALUES (1), (2); INSERT INTO t3 VALUES (1), (2); UPDATE t1 SET id = tx.id FROM t1 tx WHERE t1.id <> tx.id; -- Fails. Contradicts manual. UPDATE t2 SET id = tx.id FROM t2 tx WHERE t2.id <> tx.id; -- Succeeds UPDATE t3 SET id = tx.id FROM t3 tx WHERE t3.id <> tx.id; -- Succeeds I presented my case on stackoverflow in greater detail some time ago. Upon revisiting I found the issue still unresolved. http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred Regards Erwin --------------030407080806050301080505 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit Hi!

The manual states here:
http://www.postgresql.org/docs/current/static/sql-createtable.html

DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

INITIALLY IMMEDIATE
INITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.


This directly contradicts the information further down:

Non-deferred Uniqueness Constraints

When a UNIQUE or PRIMARY KEY constraint is not deferrable, PostgreSQL checks for uniqueness immediately whenever a row is inserted or modified. The SQL standard says that uniqueness should be enforced only at the end of the statement; this makes a difference when, for example, a single command updates multiple key values. To obtain standard-compliant behavior, declare the constraint as DEFERRABLE but not deferred (i.e., INITIALLY IMMEDIATE). Be aware that this can be significantly slower than immediate uniqueness checking.


Bold emphasis mine. My tests on Postgres 9.1 and 9.2 seem to confirm that the check for non-deferrable constraints happens after every row, not after every command. So it should be:

DEFERRABLE
NOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every inserted or modified row. Checking of constraints that are deferrable happens after every statement and can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.


Also, this important difference is completely ignored in the documentation of SET CONSTRAINTS, where the behaviour of non-deferrable constraints is treated as being equal to DEFERRABLE IMMEDATE (which it is not):
http://www.postgresql.org/docs/current/interactive/sql-set-constraints.html

> The third class is always IMMEDIATE.


Here is a test case to play with:

CREATE TEMP TABLE t1 (
  id  integer
 ,CONSTRAINT t_pkey PRIMARY KEY (id)
);

CREATE TEMP TABLE t2 (
  id  integer
 ,CONSTRAINT t2_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY IMMEDIATE
);

CREATE TEMP TABLE t3 (
  id  integer
 ,CONSTRAINT t3_pkey PRIMARY KEY (id) DEFERRABLE INITIALLY DEFERRED
);

INSERT INTO t1 VALUES (1), (2);
INSERT INTO t2 VALUES (1), (2);
INSERT INTO t3 VALUES (1), (2);

UPDATE t1
SET    id = tx.id
FROM   t1 tx
WHERE  t1.id <> tx.id;    -- Fails. Contradicts manual.

UPDATE t2
SET    id = tx.id
FROM   t2 tx
WHERE  t2.id <> tx.id;    -- Succeeds

UPDATE t3
SET    id = tx.id
FROM   t3 tx
WHERE  t3.id <> tx.id;    -- Succeeds


I presented my case on stackoverflow in greater detail some time ago. Upon revisiting I found the issue still unresolved.
http://stackoverflow.com/questions/10032272/constraint-defined-deferrable-initially-immediate-is-still-deferred


Regards
Erwin
 --------------030407080806050301080505--