The exact timing at which CHECK constraints are checked - Mailing list pgsql-novice

Hi,

I'm new to PostgreSQL and having been learning SQL. I'm uncertain
about the exact timing at which CHECK constraints are checked.
I've assumed that PostgreSQL's CHECK constraints shall be checked
after the end of each statement, since the documents state as follows.

    DEFERRABLE
    NOT DEFERRABLE

      [...] NOT NULL and CHECK constraints are not deferrable. [...]

    INITIALLY IMMEDIATE
    INITIALLY DEFERRED

      [...] If the constraint is INITIALLY IMMEDIATE, it is checked after
      each statement. [...]

    -- PostgreSQL: Documentation: Manuals: CREATE TABLE
    -- at http://www.postgresql.org/docs/9.1/static/sql-createtable.html

However, I found cases that CHECK constraints seemed to be checked
before each statement. I wrote an example to demonstrate one.

     0: -- foo.sql
     1: SELECT VERSION();
     2: CREATE TABLE foos (
     3:    bar integer
     4: );
     5: CREATE FUNCTION count_foos() RETURNS bigint AS $$
     6:   SELECT COUNT(*) FROM foos;
     7: $$ LANGUAGE SQL;
     8: ALTER TABLE foos ADD CONSTRAINT cardinality_chk
     9:   CHECK (count_foos() > 0);
    10: INSERT INTO foos VALUES (1); -- causes error

I think this SQL is ok, but it causes error.

    $ createdb demo
    $ psql demo < foo.sql
     version
    ---------------(snip)
     PostgreSQL 9.1.2 on x86_64-apple-darwin10.8.0, compiled by
i686-apple-darwin10-llvm-gcc-4.2 (GCC) 4.2.1 (Based on Apple Inc.
build 5658) (LLVM build 2335.9), 64-bit
    (1 row)

    CREATE TABLE
    CREATE FUNCTION
    ALTER TABLE
    ERROR:  new row for relation "foos" violates check constraint
"cardinality_chk"

After the INSERT statement at line 10, the foos table has one row.
So it should satisfy the cardinality_chk constraint at line 7 that
checks the number of rows in the foos table is more than zero.
But it couldn't. It seems that the CHECK constraint was checked before
the INSERT statement, or I can't just find why the INSERT statement
at line 10 fails.

--
Takahiro Noda