ALTER DOMAIN ADD CONSTRAINT goes to some effort to verify that existing
stored data of the domain type meets the new constraint. (It's not
bulletproof, because it can't see uncommitted data, but at least it
tries.) However, what if the user tries to change the behavior of
an existing constraint clause? Nothing, of course, since we have
no idea that anything has changed.
This issue occurred to me while thinking about this buglet:
regression=# create function sqlcheck(int) returns bool as
regression-# 'select $1 > 0' language sql;
CREATE FUNCTION
regression=# create domain checkedint as int check(sqlcheck(value));
CREATE DOMAIN
regression=# select 1::checkedint; -- ok
checkedint
------------
1
(1 row)
regression=# select 0::checkedint; -- fail
ERROR: value for domain checkedint violates check constraint "checkedint_check"
regression=# create or replace function sqlcheck(int) returns bool as
'select $1 <= 0' language sql;
CREATE FUNCTION
regression=# select 1::checkedint; -- fail?
checkedint
------------
1
(1 row)
regression=# select 0::checkedint; -- ok?
ERROR: value for domain checkedint violates check constraint "checkedint_check"
The reason this isn't behaving as-expected is that typcache.c has cached a
version of the domain's check constraint that sqlcheck() has been inlined
into, so the old behavior continues to apply until something happens to
cause the typcache entry to be flushed.
I'd started to work on some code changes to make the typcache react more
promptly, but then it occurred to me that the example is really dubious
anyway because any stored data of the domain type won't be rechecked.
And fixing *that* seems entirely impractical.
So what I'm thinking we should do is document that the behavior of a
domain CHECK constraint is expected to be immutable, and it's on the
user's head to preserve consistency if it isn't. We could recommend
that any attempt to change a constraint's behavior be implemented by
dropping and re-adding the constraint, which is a case that the system
does know what to do with.
Actually, the same goes for table CHECK constraints ...
Thoughts?
regards, tom lane
