Re: Constraint validation

Demian Lessa <demian(at)lessa(dot)org> writes:
> After browsing the source, and running some experiments, it seems like
> PostgreSQL blindly verifies all CHECK constraints for an update, even if
> the update COULDN'T possibly be violated by the specified update (for
> instance
> UPDATE table SET field3=value WHERE condition

We keep hearing people propose that we optimize on the assumption that
an UPDATE "can't change fields it doesn't assign to". This falls down
on the fact that a BEFORE UPDATE trigger can change the row arbitrarily.

Possibly we could do something in cases where there isn't any trigger or
it doesn't replace the row; but it would have to be a decision taken at
runtime in the guts of the executor, which considerably limits the scope
of what can be done.

In any case I'm not at all excited about trying to detect which fields a
CHECK is based on --- for typical simple check conditions it's probably
cheaper to just do the check. If you have an expensive condition you
might consider enforcing it in a trigger, which can test for itself
whether the relevant fields have changed.

regards, tom lane