Обсуждение: BUG #11107: UPDATE violates table check constraint

The following bug has been logged on the website:

Bug reference:      11107
Logged by:          Jesse Denardo
Email address:      jesse.denardo@myfarms.com
PostgreSQL version: 9.3.5
Operating system:   Arch Linux x64
Description:

Version:

PostgreSQL 9.3.5 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.9.1,
64-bit



Description:

After creating two test tables and a table check constraint on one of them,
I use an UPDATE statement to update one of the rows. This update causes the
row to violate the check constraint, but the update succeeds anyways.
Dropping and re-adding the check constraint then fails because the
constraint is violated.



Test script:

DROP SCHEMA IF EXISTS test CASCADE;
CREATE SCHEMA test;
SET search_path TO test;

CREATE TABLE a (
    id SERIAL PRIMARY KEY,
    num integer NOT NULL
);

CREATE TABLE b (
    id SERIAL PRIMARY KEY,
    a_id integer,
    num integer NOT NULL
);
ALTER TABLE ONLY b
    ADD CONSTRAINT b_fk FOREIGN KEY (a_id) REFERENCES a(id);

-- Check function to use as constraint
CREATE OR REPLACE FUNCTION fn_chk_constraint (
    b_id integer,
    a_id integer
) RETURNS boolean AS $$
    SELECT 0 = (
        SELECT count(*)
        FROM a
        JOIN b ON b.a_id = a.id AND b.num <> a.num
        WHERE a.id = a_id
        AND b.id = b_id
    )
$$ LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

-- Insert valid test data
INSERT INTO a (num) VALUES (10);
INSERT INTO b (a_id, num) VALUES (NULL, 11);

-- Add constraint to table b
ALTER TABLE b ADD CONSTRAINT chk_constraint CHECK(fn_chk_constraint(id,
a_id));

-- Make sure no rows violate the constraint...this returns no rows, which is
good
SELECT * FROM b WHERE fn_chk_constraint(id, a_id) = FALSE;

-- Update row in table b...I expect this to fail because it violates the
check constraint, but it doesn't
UPDATE b SET a_id = 1;

-- Check the constraint again...this now returns one row
SELECT * FROM b WHERE fn_chk_constraint(id, a_id) = FALSE;

-- Check my sanity. Remove the constraint and try to add it again.
ALTER TABLE b DROP CONSTRAINT chk_constraint;
ALTER TABLE b ADD CONSTRAINT chk_constraint CHECK(fn_chk_constraint(id,
a_id)); -- error!



Test script output:

DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
CREATE TABLE
ALTER TABLE
CREATE FUNCTION
INSERT 0 1
INSERT 0 1
ALTER TABLE
 id | a_id | num
----+------+-----
(0 rows)

UPDATE 1  # Unexpected success
 id | a_id | num
----+------+-----
  1 |    1 |  11
(1 row)

ALTER TABLE
psql:constraint_test.sql:50: ERROR:  check constraint "chk_constraint" is
violated by some row

jesse.denardo@myfarms.com writes:
> After creating two test tables and a table check constraint on one of them,
> I use an UPDATE statement to update one of the rows. This update causes the
> row to violate the check constraint, but the update succeeds anyways.
> Dropping and re-adding the check constraint then fails because the
> constraint is violated.

Sorry, but this check constraint has entirely undefined behavior, as does
any check constraint that refers to data rows other than the one that is
being checked.

> -- Check function to use as constraint
> CREATE OR REPLACE FUNCTION fn_chk_constraint (
>     b_id integer,
>     a_id integer
> ) RETURNS boolean AS $$
>     SELECT 0 = (
>         SELECT count(*)
>         FROM a
>         JOIN b ON b.a_id = a.id AND b.num <> a.num
>         WHERE a.id = a_id
>         AND b.id = b_id
>     )
> $$ LANGUAGE SQL IMMUTABLE RETURNS NULL ON NULL INPUT;

The "immutable" marking of this function is a lie on its face; not that
a proper marking would have helped.

The reason the constraint doesn't fail is that Postgres checks it before
storing the updated row, not after.  But even without that, there would be
no way to guarantee proper maintenance of the constraint --- what if
somebody changes table a, or if there are two concurrent changes to
table b that individually are still consistent but not when taken
together?  (I've not tried to work out whether the latter is possible
given this specific condition, but in general it's a problem if a
check constraint on b tries to examine other rows in b.)

This is why we don't allow sub-SELECTs in check conditions.  You
can escape that syntactic constraint by embedding the sub-SELECT
in a function as you've done here, but that isn't going to make
it work.

            regards, tom lane

Tom Lane-2 wrote
> jesse.denardo@

>  writes:
>> After creating two test tables and a table check constraint on one of
>> them,
>> I use an UPDATE statement to update one of the rows. This update causes
>> the
>> row to violate the check constraint, but the update succeeds anyways.
>> Dropping and re-adding the check constraint then fails because the
>> constraint is violated.
>
> Sorry, but this check constraint has entirely undefined behavior, as does
> any check constraint that refers to data rows other than the one that is
> being checked.

Which is why PostgreSQL has CREATE TRIGGER functionality...

David J.




--
View this message in context:
http://postgresql.1045698.n5.nabble.com/BUG-11107-UPDATE-violates-table-check-constraint-tp5813528p5813541.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.

On 8/1/14 5:59 PM, David G Johnston wrote:
> Tom Lane-2 wrote
>> Sorry, but this check constraint has entirely undefined behavior, as does
>> any check constraint that refers to data rows other than the one that is
>> being checked.
>
> Which is why PostgreSQL has CREATE TRIGGER functionality...

Which does absolutely nothing to get rid of the race conditions.


.marko

On Fri, Aug 1, 2014 at 10:01 AM, Marko Tiikkaja <marko@joh.to> wrote:

> On 8/1/14 5:59 PM, David G Johnston wrote:
>
>> Tom Lane-2 wrote
>>
>>> Sorry, but this check constraint has entirely undefined behavior, as do=
es
>>> any check constraint that refers to data rows other than the one that i=
s
>>> being checked.
>>>
>>
>> Which is why PostgreSQL has CREATE TRIGGER functionality...
>>
>
> Which does absolutely nothing to get rid of the race conditions.
>
>
=E2=80=8BTrue enough - but at least it would theoretically work in their ab=
sence=E2=80=8B.

Two more useful model designs to consider:

1) Make the FK composite
2) Remove the duplicate PK dependent data from the foreign table

Though given that we only have a toy model to work, and no problem/domain
description, all suggestions are thought-provoking only.

Lacking a model change you could encapsulate both constraints and races
within a function and disallow direct updates to the key-related columns.

David J.