Обсуждение: The exact timing at which CHECK constraints are checked

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

Takahiro Noda <noda.takahiro.47m@st.kyoto-u.ac.jp> writes:
> I'm new to PostgreSQL and having been learning SQL. I'm uncertain
> about the exact timing at which CHECK constraints are checked.

They're checked at the instant that a row is inserted or updated.
If you really need a deferred check, you'll need to build it yourself
using an AFTER ROW trigger.

            regards, tom lane

On Thu, Dec 15, 2011 at 3:41 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> They're checked at the instant that a row is inserted or updated.
> If you really need a deferred check, you'll need to build it yourself
> using an AFTER ROW trigger.

Thank you for taking time to answer my novice question.
The AFTER ROW trigger version is what I expected.

    CREATE TABLE foos (
      bar INTEGER
    );

    CREATE FUNCTION check_foo_cardinality() RETURNS trigger AS $$
      BEGIN
        IF (SELECT count(*) FROM foos) < 1 THEN
          RAISE EXCEPTION 'at least one row required';
        END IF;
        RETURN NULL;
      END
    $$ LANGUAGE plpgsql;

    CREATE CONSTRAINT TRIGGER check_cardinality
      AFTER DELETE ON foos
      INITIALLY DEFERRED
      FOR EACH ROW
      EXECUTE PROCEDURE check_foo_cardinality();

    INSERT INTO foos VALUES (1);
    DELETE FROM foos;  -- => ERROR:  at least one row required

Best,