Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fnrL3-0004rX-3H for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Aug 2018 20:15:17 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fnrL0-0004fj-SF for pgsql-hackers@arkaria.postgresql.org; Thu, 09 Aug 2018 20:15:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fnrL0-0004fZ-HY for pgsql-hackers@lists.postgresql.org; Thu, 09 Aug 2018 20:15:14 +0000 Received: from mail-qt0-x243.google.com ([2607:f8b0:400d:c0d::243]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fnrKx-0000d9-7K for pgsql-hackers@postgresql.org; Thu, 09 Aug 2018 20:15:14 +0000 Received: by mail-qt0-x243.google.com with SMTP id h4-v6so7984958qtj.7 for ; Thu, 09 Aug 2018 13:15:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:in-reply-to:references:from:date:message-id:subject:to :cc; bh=AbVVww9fzBTzXcqaXtC5aM5JPROiEhvKLyDRjaYX928=; b=PIS3gMikNseK7Rl6fghL9fJxDeYMA+qQvKvUBXBpGYfIqaNs67jK1LcSVLw7eAogtj G3aMeL2iNLyA01wvz9k4+sQ0sZQX/xn6yToNbGcSKjNkTwkEjUsFjfUQN+xKH4KK3l2H VHPJkhhQf0EtQaNBdPbSinF/7FIEajBaggFt8iTnHeuiyfY5bZuGVX19hgg4fWUmQdnt 3TwY9kAJSY+dXVddi4/uFNAFq/jaT0qU34jqbkjpFDxv9poB152IVttiF+9RJeoB0PUL FF7AEQr6osITGKkSB8nw2scrrIiPRF1r4dRVF1wHhDeBc5iEAZQffH942CZRTvb3pOdA Jfeg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:in-reply-to:references:from:date :message-id:subject:to:cc; bh=AbVVww9fzBTzXcqaXtC5aM5JPROiEhvKLyDRjaYX928=; b=JyJS8QyOvWPtEUXmWeaC7QdckzN+hbzowi8lZanR4t4+xsdiBsrEdGEEN36Pv8n+OW BF56szpuM09A75XK5d1OF0rwMPuj3iAlmT4UEC93cI16qQMyeC0POWC2ZhLKmw4eqFuq R0MEHaX2xUFwVElkMNop/wy+HYgXTCRP1jCIhdS2akVKD9Duoddxdw1x6QjtWRv3o+Fj NU6J0gcJnlzCJE63djhAEmdk7pKwC/w/yAAdZV0kHDvv5o1yw9D8QJxGmcFDTNlTBdm1 p9KFJd8Y/dJFCeuZbjkURSV1741/KfJsPEK03PjUsxQPd5bk4Yf1+urypyCHT2LhLyb1 +bSg== X-Gm-Message-State: AOUpUlFbcJdWJkKp7idRSOFCluIUvTW5kUqPMOAtb25J9VF1Ol38CcQ6 I80IUcSC37Ui1Z/N81vaQg6COVKpM6ntJ7kA1bA= X-Google-Smtp-Source: AA+uWPzIhg6BNC1Rp6JETKf9cgG5cRv6x9vNyR1wMexQbwXJTwpQd6GduHObNO+8L5IVzYWR+NCpLjMZsrU5ZWcs7bs= X-Received: by 2002:ac8:728a:: with SMTP id v10-v6mr3499145qto.7.1533845709040; Thu, 09 Aug 2018 13:15:09 -0700 (PDT) MIME-Version: 1.0 Received: by 2002:a0c:c60c:0:0:0:0:0 with HTTP; Thu, 9 Aug 2018 13:15:08 -0700 (PDT) In-Reply-To: <25606.1533843069@sss.pgh.pa.us> References: <153121527691.1408.5686988620817799073@wrigleys.postgresql.org> <20180807173659.GD7297@momjian.us> <31616681533725753@sas1-d856b3d759c7.qloud-c.yandex.net> <20180808130040.GA2611@momjian.us> <12107.1533736288@sss.pgh.pa.us> <37593701533809465@sas1-87f9feb8d943.qloud-c.yandex.net> <20180809190913.GB14011@momjian.us> <25606.1533843069@sss.pgh.pa.us> From: "David G. Johnston" Date: Thu, 9 Aug 2018 13:15:08 -0700 Message-ID: Subject: Re: Typo in doc or wrong EXCLUDE implementation To: Tom Lane Cc: Bruce Momjian , KES , PostgreSQL-development Content-Type: multipart/mixed; boundary="000000000000a0e16a057306488c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000a0e16a057306488c Content-Type: multipart/alternative; boundary="000000000000a0e168057306488a" --000000000000a0e168057306488a Content-Type: text/plain; charset="UTF-8" On Thu, Aug 9, 2018 at 12:31 PM, Tom Lane wrote: > I think the OP is reading "equivalent" literally, as meaning that > an EXCLUDE with operators that act like equality is treated as being > the same as UNIQUE for *every* purpose. We're not going there, IMO, > so probably we need to tweak the doc wording a little. Perhaps > writing "functionally equivalent" would be better? Or instead of > "is equivalent to", write "imposes the same restriction as"? > Maybe something like: diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index d936de3f23..7c31fe853b 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -928,12 +928,10 @@ WITH ( MODULUS numeric_literal, REM The EXCLUDE clause defines an exclusion constraint, which guarantees that if any two rows are compared on the specified column(s) or - expression(s) using the specified operator(s), not all of these - comparisons will return TRUE. If all of the - specified operators test for equality, this is equivalent to a - UNIQUE constraint, although an ordinary unique constraint - will be faster. However, exclusion constraints can specify - constraints that are more general than simple equality. + expression(s) using the specified operator(s), at least one of the + comparisons will return FALSE. + Exclusion constraints can (and should) be used to specify + expressions that do not involve simple equality. For example, you can specify a constraint that no two rows in the table contain overlapping circles (see ) by using the @@ -968,6 +966,14 @@ WITH ( MODULUS numeric_literal, REM exclusion constraint on a subset of the table; internally this creates a partial index. Note that parentheses are required around the predicate. + + + PostgreSQL does not consider an exclusion + constraint to be a valid unique constraint for purposes of determining the + validity of a foreign key constraint. For this reason, in addition to performance, + an exclusion constraint defined using only equality operators should be defined + as a UNIQUE constraint. + --000000000000a0e168057306488a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Thu, Au= g 9, 2018 at 12:31 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
I think the OP = is reading "equivalent" literally, as meaning that
an EXCLUDE with operators that act like equality is treated as being
the same as UNIQUE for *every* purpose.=C2=A0 We're not going there, IM= O,
so probably we need to tweak the doc wording a little.=C2=A0 Perhaps
writing "functionally equivalent" would be better?=C2=A0 Or inste= ad of
"is equivalent to", write "imposes the same restriction as&q= uot;?

Maybe something like:

diff --git a/doc/src/sg= ml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index d936de3f23..7c31fe853b 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -928,12 +928,10 @@ WITH ( MODULUS <replaceable class= =3D"parameter">numeric_literal</replaceable>, REM
<= div class=3D"gmail_default">=C2=A0 =C2=A0 =C2=A0 =C2=A0The <literal>E= XCLUDE</literal> clause defines an exclusion
=C2=A0 =C2=A0 =C2=A0 =C2=A0constraint, which guarantees that if
=C2=A0 =C2=A0 =C2=A0 =C2=A0any two rows ar= e compared on the specified column(s) or
= -=C2=A0 =C2=A0 =C2=A0 expression(s) using the specified operator(s), not al= l of these
-=C2=A0 =C2=A0 =C2=A0 comparis= ons will return <literal>TRUE</literal>.=C2=A0 If all of the
-=C2=A0 =C2=A0 =C2=A0 specified operators t= est for equality, this is equivalent to a
-=C2=A0 =C2=A0 =C2=A0 <literal>UNIQUE</literal> constraint, al= though an ordinary unique constraint
-=C2= =A0 =C2=A0 =C2=A0 will be faster.=C2=A0 However, exclusion constraints can = specify
-=C2=A0 =C2=A0 =C2=A0 constraints= that are more general than simple equality.
+=C2=A0 =C2=A0 =C2=A0 expression(s) using the specified operator(s), at= least one of the
+=C2=A0 =C2=A0 =C2=A0 c= omparisons will return <literal>FALSE<literal/>.
+=C2=A0 =C2=A0 =C2=A0 Exclusion constraints can (and sh= ould) be used to specify
+=C2=A0 =C2=A0 = =C2=A0 expressions that do not involve simple equality.
=C2=A0 =C2=A0 =C2=A0 =C2=A0For example, you can specify a co= nstraint that
=C2=A0 =C2=A0 =C2=A0 =C2=A0= no two rows in the table contain overlapping circles
=C2=A0 =C2=A0 =C2=A0 =C2=A0(see <xref linkend=3D"dataty= pe-geometric"/>) by using the
@@ = -968,6 +966,14 @@ WITH ( MODULUS <replaceable class=3D"parameter&qu= ot;>numeric_literal</replaceable>, REM
=C2=A0 =C2=A0 =C2=A0 =C2=A0exclusion constraint on a subset of the t= able; internally this creates a
=C2=A0 = =C2=A0 =C2=A0 =C2=A0partial index. Note that parentheses are required aroun= d the predicate.
=C2=A0 =C2=A0 =C2=A0 <= ;/para>
+
+=C2=A0 =C2=A0 =C2=A0<para>
+= =C2=A0 =C2=A0 =C2=A0 <productname>PostgreSQL</productname> does= not consider an exclusion
+=C2=A0 =C2=A0= =C2=A0 constraint to be a valid unique constraint for purposes of determin= ing the
+=C2=A0 =C2=A0 =C2=A0 validity of= a foreign key constraint.=C2=A0 For this reason, in addition to performanc= e,
+=C2=A0 =C2=A0 =C2=A0 an exclusion con= straint defined using only equality operators should be defined
+=C2=A0 =C2=A0 =C2=A0 as a <literal>UNIQUE<= literal/> constraint.
+=C2=A0 =C2=A0 = =C2=A0</para>
=C2=A0 =C2=A0 =C2=A0&= lt;/listitem>
=C2=A0 =C2=A0 </varli= stentry>
=C2=A0

--000000000000a0e168057306488a-- --000000000000a0e16a057306488c Content-Type: application/octet-stream; name="create-table-exclude-doc.diff" Content-Disposition: attachment; filename="create-table-exclude-doc.diff" Content-Transfer-Encoding: base64 X-Attachment-Id: f_jkn02ux10 ZGlmZiAtLWdpdCBhL2RvYy9zcmMvc2dtbC9yZWYvY3JlYXRlX3RhYmxlLnNnbWwgYi9kb2Mvc3Jj L3NnbWwvcmVmL2NyZWF0ZV90YWJsZS5zZ21sCmluZGV4IGQ5MzZkZTNmMjMuLjdjMzFmZTg1M2Ig MTAwNjQ0Ci0tLSBhL2RvYy9zcmMvc2dtbC9yZWYvY3JlYXRlX3RhYmxlLnNnbWwKKysrIGIvZG9j L3NyYy9zZ21sL3JlZi9jcmVhdGVfdGFibGUuc2dtbApAQCAtOTI4LDEyICs5MjgsMTAgQEAgV0lU SCAoIE1PRFVMVVMgPHJlcGxhY2VhYmxlIGNsYXNzPSJwYXJhbWV0ZXIiPm51bWVyaWNfbGl0ZXJh bDwvcmVwbGFjZWFibGU+LCBSRU0KICAgICAgIFRoZSA8bGl0ZXJhbD5FWENMVURFPC9saXRlcmFs PiBjbGF1c2UgZGVmaW5lcyBhbiBleGNsdXNpb24KICAgICAgIGNvbnN0cmFpbnQsIHdoaWNoIGd1 YXJhbnRlZXMgdGhhdCBpZgogICAgICAgYW55IHR3byByb3dzIGFyZSBjb21wYXJlZCBvbiB0aGUg c3BlY2lmaWVkIGNvbHVtbihzKSBvcgotICAgICAgZXhwcmVzc2lvbihzKSB1c2luZyB0aGUgc3Bl Y2lmaWVkIG9wZXJhdG9yKHMpLCBub3QgYWxsIG9mIHRoZXNlCi0gICAgICBjb21wYXJpc29ucyB3 aWxsIHJldHVybiA8bGl0ZXJhbD5UUlVFPC9saXRlcmFsPi4gIElmIGFsbCBvZiB0aGUKLSAgICAg IHNwZWNpZmllZCBvcGVyYXRvcnMgdGVzdCBmb3IgZXF1YWxpdHksIHRoaXMgaXMgZXF1aXZhbGVu dCB0byBhCi0gICAgICA8bGl0ZXJhbD5VTklRVUU8L2xpdGVyYWw+IGNvbnN0cmFpbnQsIGFsdGhv dWdoIGFuIG9yZGluYXJ5IHVuaXF1ZSBjb25zdHJhaW50Ci0gICAgICB3aWxsIGJlIGZhc3Rlci4g IEhvd2V2ZXIsIGV4Y2x1c2lvbiBjb25zdHJhaW50cyBjYW4gc3BlY2lmeQotICAgICAgY29uc3Ry YWludHMgdGhhdCBhcmUgbW9yZSBnZW5lcmFsIHRoYW4gc2ltcGxlIGVxdWFsaXR5LgorICAgICAg ZXhwcmVzc2lvbihzKSB1c2luZyB0aGUgc3BlY2lmaWVkIG9wZXJhdG9yKHMpLCBhdCBsZWFzdCBv bmUgb2YgdGhlCisgICAgICBjb21wYXJpc29ucyB3aWxsIHJldHVybiA8bGl0ZXJhbD5GQUxTRTxs aXRlcmFsLz4uCisgICAgICBFeGNsdXNpb24gY29uc3RyYWludHMgY2FuIChhbmQgc2hvdWxkKSBi ZSB1c2VkIHRvIHNwZWNpZnkKKyAgICAgIGV4cHJlc3Npb25zIHRoYXQgZG8gbm90IGludm9sdmUg c2ltcGxlIGVxdWFsaXR5LgogICAgICAgRm9yIGV4YW1wbGUsIHlvdSBjYW4gc3BlY2lmeSBhIGNv bnN0cmFpbnQgdGhhdAogICAgICAgbm8gdHdvIHJvd3MgaW4gdGhlIHRhYmxlIGNvbnRhaW4gb3Zl cmxhcHBpbmcgY2lyY2xlcwogICAgICAgKHNlZSA8eHJlZiBsaW5rZW5kPSJkYXRhdHlwZS1nZW9t ZXRyaWMiLz4pIGJ5IHVzaW5nIHRoZQpAQCAtOTY4LDYgKzk2NiwxNCBAQCBXSVRIICggTU9EVUxV UyA8cmVwbGFjZWFibGUgY2xhc3M9InBhcmFtZXRlciI+bnVtZXJpY19saXRlcmFsPC9yZXBsYWNl YWJsZT4sIFJFTQogICAgICAgZXhjbHVzaW9uIGNvbnN0cmFpbnQgb24gYSBzdWJzZXQgb2YgdGhl IHRhYmxlOyBpbnRlcm5hbGx5IHRoaXMgY3JlYXRlcyBhCiAgICAgICBwYXJ0aWFsIGluZGV4LiBO b3RlIHRoYXQgcGFyZW50aGVzZXMgYXJlIHJlcXVpcmVkIGFyb3VuZCB0aGUgcHJlZGljYXRlLgog ICAgICA8L3BhcmE+CisKKyAgICAgPHBhcmE+CisgICAgICA8cHJvZHVjdG5hbWU+UG9zdGdyZVNR TDwvcHJvZHVjdG5hbWU+IGRvZXMgbm90IGNvbnNpZGVyIGFuIGV4Y2x1c2lvbgorICAgICAgY29u c3RyYWludCB0byBiZSBhIHZhbGlkIHVuaXF1ZSBjb25zdHJhaW50IGZvciBwdXJwb3NlcyBvZiBk ZXRlcm1pbmluZyB0aGUKKyAgICAgIHZhbGlkaXR5IG9mIGEgZm9yZWlnbiBrZXkgY29uc3RyYWlu dC4gIEZvciB0aGlzIHJlYXNvbiwgaW4gYWRkaXRpb24gdG8gcGVyZm9ybWFuY2UsCisgICAgICBh biBleGNsdXNpb24gY29uc3RyYWludCBkZWZpbmVkIHVzaW5nIG9ubHkgZXF1YWxpdHkgb3BlcmF0 b3JzIHNob3VsZCBiZSBkZWZpbmVkCisgICAgICBhcyBhIDxsaXRlcmFsPlVOSVFVRTxsaXRlcmFs Lz4gY29uc3RyYWludC4KKyAgICAgPC9wYXJhPgogICAgIDwvbGlzdGl0ZW0+CiAgICA8L3Zhcmxp c3RlbnRyeT4KIAo= --000000000000a0e16a057306488c--