ilmari@ilmari.org (Dagfinn Ilmari Mannsåker) writes: > Tom Lane <tgl@sss.pgh.pa.us> writes: >> ... and if the CHECK expression is immutable ...
> Doesn't it have to be already?
AFAIK we don't insist on that currently. You could imagine useful checks that are not, for example CHECK(write_timestamp <= now()).
That seems like abuse of CHECK to me, and a job for a trigger. If anyone proposed allowing that and it wasn't already allowed (or at least not prohibited explicitly) it'd get shot down in flames.
If we wanted checks that apply only on row insert/update a CHECK WRITE or similar would seem suitable; something that implies that it's an _action_ taken on write and doesn't stop the constraint later becoming violated by unrelated changes. Like a trigger. Such a check could be allowed to use subqueries, reference other tables, call functions and all the other fun stuff you're not meant to do in a CHECK constraint. Like a trigger.
Or we could use triggers.
> Otherwise a value accepted at one point in time could suddenly violate > the constraint later, even though it never changed.
True. If you use a non-immutable check, it's on your head whether or not this is a problem. But the database shouldn't perform optimizations that just assume it's immutable without checking.
I agree.
Personally I wish CHECK constraints could be immutable. There are a few annoyances around time zone, tseach dictionaries etc that'd make some checks harder to write, but nothing drastic.
