Re: To-Do item: skip table scan for adding column with provable check constraints

On 25 May 2016 at 06:56, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> ilmari(at)ilmari(dot)org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes:
> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.

--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services