Обсуждение: To-Do item: skip table scan for adding column with provable check constraints

I recently had to run something like:

alter table pgbench_accounts add locked text check (locked != 'unlocked');

And was surprised that it took several minutes to complete as it
scanned the whole table.

The new column is going to start out as NULL in every row, so there is
no need to validate the check constraint by reading the table as it
can be proven from first principles.  Correct?

Is there a reason such an improvement would be unwanted or not
feasible?  If not, I will add as a To-Do item.

Cheers,

Jeff

On Thu, May 19, 2016 at 7:33 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
> I recently had to run something like:
>
> alter table pgbench_accounts add locked text check (locked != 'unlocked');
>
> And was surprised that it took several minutes to complete as it
> scanned the whole table.
>
> The new column is going to start out as NULL in every row, so there is
> no need to validate the check constraint by reading the table as it
> can be proven from first principles.  Correct?

Right.  If there were a DEFAULT on the new column that would of course
be different, and you can also do thinks like CHECK (a != b) here.
However, if the CHECK constraint does not reference any column other
than the newly-added one, and if the new column will have the same
value for every row either because there is no default or because the
default is a constant, then we can test the CHECK constraint just once
against the value that all new rows will have instead of testing it
once per row.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:
> Right.  If there were a DEFAULT on the new column that would of course
> be different, and you can also do thinks like CHECK (a != b) here.
> However, if the CHECK constraint does not reference any column other
> than the newly-added one, and if the new column will have the same
> value for every row either because there is no default or because the
> default is a constant,

... and if the CHECK expression is immutable ...

> then we can test the CHECK constraint just once
> against the value that all new rows will have instead of testing it
> once per row.

I'm not entirely sure that this case occurs often enough to be worth the
trouble.  Although your formulation suggests that we could just execute
the expression once, rather than attempting to prove it true which is
what I think Jeff had in mind.  That would expand the scope of usefulness
considerably.
        regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Robert Haas <robertmhaas@gmail.com> writes:
>> Right.  If there were a DEFAULT on the new column that would of course
>> be different, and you can also do thinks like CHECK (a != b) here.
>> However, if the CHECK constraint does not reference any column other
>> than the newly-added one, and if the new column will have the same
>> value for every row either because there is no default or because the
>> default is a constant,
>
> ... and if the CHECK expression is immutable ...

Doesn't it have to be already?  Otherwise a value accepted at one point
in time could suddenly violate the constraint later, even though it
never changed.


ilmari

-- 
"A disappointingly low fraction of the human race is,at any given time, on fire." - Stig Sandbeck Mathisen

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

> 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.
        regards, tom lane

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