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