Re: To-Do item: skip table scan for adding column with provable check constraints
| От | Craig Ringer |
|---|---|
| Тема | Re: To-Do item: skip table scan for adding column with provable check constraints |
| Дата | |
| Msg-id | CAMsr+YFEbrAc9XT1ypUHh28ABe_pP=_6Mqe5w3_U2LwmJFuQpg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: To-Do item: skip table scan for adding column with provable check constraints (Tom Lane <tgl@sss.pgh.pa.us>) |
| Ответы |
Re: To-Do item: skip table scan for adding column with
provable check constraints
|
| Список | pgsql-hackers |
On 25 May 2016 at 06:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
В списке pgsql-hackers по дате отправления: