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  (Jim Nasby <Jim.Nasby@BlueTreble.com>)
Список 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. 

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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Kouhei Kaigai
Дата:
Сообщение: Does people favor to have matrix data type?
Следующее
От: Stephen Frost
Дата:
Сообщение: Re: Error during restore - dump taken with pg_dumpall -c option