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

On 5/24/16 9:56 PM, Craig Ringer wrote:
> On 25 May 2016 at 06:56, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
>
> ilmari(at)ilmari(dot)org <mailto: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 <mailto: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.

Yeah, non-IMMUTABLE checks are a really bad idea, especially because
they will only trip you up well after the fact (like when restoring from
a dump).

> 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.

Rather than creating new CHECK syntax, I'd rather have a notion of
"check triggers" that simply evaluate a boolean expression (and don't
require defining a function).
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461