run check constraints only when affected columns are changed?

On 01/08/2012 03:42 PM, Peter Eisentraut 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?

triggers?

cheers

andrew

On Sun, Jan 8, 2012 at 8:42 PM, Peter Eisentraut <peter_e@gmx.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