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