Обсуждение: 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