On Sun, Dec 12, 2010 at 4:49 PM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Dec 12, 2010 at 6:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Simon Riggs <simon@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
MySQL does in fact have this feature and it is used by mysqldump. This
feature is very useful.
--
Rob Wultsch
wultsch@gmail.com