On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > Simon Riggs <si...@2ndquadrant.com> writes: >> On Sun, 2010-12-12 at 17:57 -0500, Tom Lane wrote: >>> Huh? It allows you to postpone the check until commit. That's far from >>> not enforcing it. > >> This clearly implies that un-enforced constraints are not checked at >> commit. > > [ shrug... ] I can't argue with you about what may or may not be in an > unpublished draft of an unratified version of the standard, since I > don't have a copy. But allow me to harbor doubts that they really > intend to allow someone to force a constraint to be considered valid > without any verification. This proposal strikes me as something mysql > would do, not the standards committee. (In particular, can a constraint > go from not-enforced to enforced state without getting checked at that > time?) > > Even if you're reading the draft correctly, and the wording makes it > into a released standard, the implementation you propose would break our > code. The incremental FK checks are designed on the assumption that the > constraint condition held before; they aren't likely to behave very > sanely if the data is bad. I'd want to see a whole lot more analysis of > the resulting behavior before even considering an idea like this.
Wow, you've managed to bash Simon, MySQL, and the SQL standards committee all in one email. I'm not going to argue that careful analysis isn't needed before doing something like this - and, in particular, if we ever get inner-join removal, which I'm still hoping to do at some point, a foreign key that isn't actually guaranteed to be valid might result in queries returning different answers depending on whether or not a join is removed. I guess we'd have to define that as the user's problem for alleging a foreign-key relationship that doesn't truly exist. On the other hand, there's clearly also a use case for this behavior. If a bulk load of prevalidated data forces an expensive revalidation of constraints that are already known to hold, there's a real chance the DBA will be backed into a corner where he simply has no choice but to not use foreign keys, even though he might really want to validate the foreign-key relationships on a going-forward basis. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
