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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
ilmari@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
On 25 May 2016 at 06:56, Tom Lane <tgl@sss.pgh.pa.us> wrote:
ilmari@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) 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
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
=?utf-8?Q?Manns=C3=A5ker?=) 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers