Thread: Mutability of domain CHECK constraints

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

On 06/12/2018 15:41, Tom Lane wrote:
> 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 ...

I got annoyed several years ago that CHECK constraints aren't required
to be immutable.  I don't understand why that's the case but there's a
regression test specifically for it so I never did anything about it.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

Vik Fearing <vik.fearing@2ndquadrant.com> writes:
> On 06/12/2018 15:41, Tom Lane wrote:
>> 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 ...

> I got annoyed several years ago that CHECK constraints aren't required
> to be immutable.  I don't understand why that's the case but there's a
> regression test specifically for it so I never did anything about it.

Well, there *are* use cases for it.  A simple example is

create table ...
    event_time timestamptz check (event_time <= now()),

if you want to ensure that no "in the future" event gets inserted.
The real restriction is not so much immutability as that the constraint
must get monotonically weaker, ie it must never reject a row it
previously accepted.  But I dunno that we want to go into that in
the documentation.  People who need such things can probably figure
it out for themselves.

            regards, tom lane