On 5/24/16 9:56 PM, Craig Ringer wrote:
On 25 May 2016 at 06:56, Tom Lane <[email protected] <mailto:[email protected]>> wrote:[email protected] <mailto:[email protected]> (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes: > Tom Lane <[email protected] <mailto:[email protected]>> 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 -- Sent via pgsql-hackers mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
