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@sss.pgh.pa.us
> <mailto:tgl@sss.pgh.pa.us>> wrote:
>
>     ilmari@ilmari.org <mailto:ilmari@ilmari.org> (Dagfinn Ilmari
>     Mannsåker) writes:
>     > Tom Lane <tgl@sss.pgh.pa.us <mailto: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.

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