Обсуждение: Constraint validation

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi,

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

and there is no CHECK constraint on field3. I compared my observations
with what I saw by running the same experiments in Oracle, and they seem
to verify constraints selectively.

Two questions: is there a reason for this behavior in PostgreSQL? and,
does this also hold for other constraints such as PRIMARY KEYs, UNIQUEs,
and FOREIGN KEYs?

Thanks,

Demian
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org

iD8DBQFF53LXWs7G5iIp9akRAiQHAJ4nxqKt/gH2SVqDYxS+8bcTpHIt6gCeL/HH
ZhpTHmGPQ9qaylyBZiBaBw4=
=pnf6
-----END PGP SIGNATURE-----

Demian Lessa <demian@lessa.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