Re: run check constraints only when affected columns are changed?

On Sun, Jan 8, 2012 at 8:42 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:

> Currently, check constraints are verified whenever a table row is
> updated at all.  It seems to me that we could possibly make this quite a
> bit more efficient if we only ran the check constraint expression when
> the update changes a column that is referenced by the constraint
> expression.  Through dependency tracking, we have that information, and
> we already have the catalog infrastructure to store this information
> from primary and foreign keys.  We'd just need to do some tweaking in
> the executor.  Any thoughts on that?  Possible pitfalls?

It would be sensible and useful to be able to track columns that have
changed during an update as we move through execution. Initially that
would be columns in the SET, but could be reduced from there to actual
changed columns.

We have column level permissions, HOT and various other optimisations
possible if that information was generally accessible.

Storing a column dependency bitmap for a CHECK constraint would be a
further use.

We also discussed once knowing ahead of time that an UPDATE doesn't
reference any indexed columns so we have no need to examine actual
column changes to decide on HOT updatability.

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