CHECK constraints inconsistencies - Mailing list pgsql-hackers

In a recent discussion on IRC, some anomalies concerning CHECK 
constraints were brought to light, in that in some cases they do not 
guarantee that the data within the table satisfies them. For example 
(against 7.4.1),

test=# create table foo (
test(# foo_stamp timestamptz not null,
test(# foo_int   integer     not null,
test(# check (foo_stamp > current_timestamp)
test(# );
CREATE TABLE
test=#
test=# insert into foo values (now() + '20 seconds'::interval, 3);
INSERT 647207 1
test=# \d foo;                Table "public.foo"  Column   |           Type           | Modifiers
-----------+--------------------------+----------- foo_stamp | timestamp with time zone | not null foo_int   | integer
               | not null
 
Check constraints:    "$1" CHECK (foo_stamp > 'now'::text::timestamp(6) with time zone)

test=# select foo_stamp, foo_int, now() as now_stamp from foo;          foo_stamp           | foo_int |
now_stamp
------------------------------+---------+------------------------------ 2004-03-01 21:38:35.54846+09 |       3 |
2004-03-0121:39:02.91338+09
 
(1 row)

test=# update foo set foo_int = 4;
ERROR:  new row for relation "foo" violates check constraint "$1"
test=# insert into foo values (now() - '10 seconds'::interval,3);
ERROR:  new row for relation "foo" violates check constraint "$1"

The CHECK constraint ( foo_stamp > current_timestamp ) is only checked 
on INSERT and UPDATE (and fails appropriately in such cases). In the 
case of the SELECT statement, it's clear that the data within the table 
no longer satisfies the CHECK constraint.

Another example, using an admittedly strange CHECK constraint:

test=# create table f (a float, check (a < random()));
CREATE TABLE
test=# insert into f values (0.02);
INSERT 647211 1
test=# insert into f values (0.03);
INSERT 647212 1
test=# insert into f values (0.04);
INSERT 647213 1
test=# insert into f values (0.99);
ERROR:  new row for relation "f" violates check constraint "$1"
test=# select * from f;  a
------ 0.02 0.03 0.04
(3 rows)

While it may make sense under certain conditions to test against a 
random number at a specific time, what does it mean for the data to 
always be less than random(), as the CHECK constraint implies?

In both cases, the CHECK constraint uses a function that is stable or 
volatile. It was suggested that functions used in CHECK constraints be 
restricted to immutable, as are functions used in indexes on 
expressions, at least until PostgreSQL can guarantee that the CHECK 
constraints will hold at times beyond INSERT and UPDATE.

Similar functionality can be attained using ON INSERT and ON UPDATE 
trigger functions, which in the case of stable or volatile functions is 
a more accurate description of what actually is protected.

If functions such as CURRENT_TIMESTAMP are allowed in CHECK constraints 
and they are evaluated on SELECT as well as on INSERT or UPDATE, 
another thing to consider is what the proper behavior would be when 
rows are found to be in violation of the constraint. Should the 
offending rows be deleted?

Michael Glaesemann
grzm myrealbox com