Subject: Typo in doc or wrong EXCLUDE implementation

From: PG Doc comments form <[email-masked]>
Date: Tue, 10 Jul 2018 09:34:36 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/10/static/sql-createtable.html
Description:

Hi.

https://www.postgresql.org/docs/current/static/sql-createtable.html#sql-createtable-exclude
If all of the specified operators test for equality, this is equivalent to a
UNIQUE constraint

Exclusion constraints are implemented using an index

ALTER TABLE person
  add constraint person_udx_person_id2
  EXCLUDE USING gist (
    person_id WITH = 
  )                                                 
;

tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT
"person_x_person_fk_parent_person_id"
tucha->   FOREIGN KEY ("parent_person_id")
tucha->   REFERENCES "person" ("person_id")
tucha->   ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE;
ERROR:  there is no unique constraint matching given keys for referenced
table "person"

because gist does not support unique indexes, I try with 'btree'

ALTER TABLE person
  add constraint person_udx_person_id2
  EXCLUDE USING btree (
    person_id WITH =
  )
;

\d person
...
"person_udx_person_id2" EXCLUDE USING btree (person_id WITH =)

tucha=> ALTER TABLE "person_x_person" ADD CONSTRAINT
"person_x_person_fk_parent_person_id"
tucha->   FOREIGN KEY ("parent_person_id")
tucha->   REFERENCES "person" ("person_id")
tucha->   ON DELETE CASCADE ON UPDATE NO ACTION DEFERRABLE;
ERROR:  there is no unique constraint matching given keys for referenced
table "person"

Why postgres does not add unique flag. Despite on: "this is equivalent to a
UNIQUE constraint"
I thought it should be:
"person_udx_person_id2" UNIQUE EXCLUDE USING btree (person_id WITH =)

PS. 
> For example, you can specify a constraint that no two rows in the table
contain overlapping circles (see Section 8.8) by using the && operator.

Also I expect that this:
ALTER TABLE person
  add constraint person_udx_person_id
  EXCLUDE USING gist (
    person_id WITH =,
    tstzrange(valid_from, valid_till, '[)' ) WITH &&
  )

also should raise UNIQUE flag for exclusion thus we can use it in FK

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 7 Aug 2018 13:36:59 -0400

This email was sent to docs, but I think it is a hackers issue.  The
person is asking why exclusion constraints aren't marked as UNIQUE
indexes that can be used for referential integrity.  I think the reason
is that non-equality exclusion constraints, like preventing overlap, but
don't uniquely identify a specific value, and I don't think we want to
auto-UNIQUE just for equality exclusion constraints.

---------------------------------------------------------------------------

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

---

From: KES <[email-masked]>
Date: Wed, 08 Aug 2018 13:55:53 +0300

I do not know many internals and maybe wrong.

But from my point of view with my current knowledge. 
If such exclusion constraint would be marked as UNIQUE we can use it for FK while implementing temporal/bi-temporal tables.

And this will be simplify relationing while implementing them.

07.08.2018, 20:37, "Bruce Momjian" <[email-masked]>:
>>  > For example, you can specify a constraint that no two rows in the table

---

From: Bruce Momjian <[email-masked]>
Date: Wed, 8 Aug 2018 09:00:40 -0400

> And this will be simplify relationing while implementing them.

Yes, it would work, but doing that only for equality would be surprising
to many people because exclusion constraints are more general than
equality comparisons.

---

From: Tom Lane <[email-masked]>
Date: Wed, 08 Aug 2018 09:51:28 -0400

> to many people because exclusion constraints are more general than

In general, we should be discouraging people from using EXCLUDE syntax
with simple equality operators, not encouraging them to do so.  It's
less efficient and less portable than a regular btree-based uniqueness
constraint.  So I think this proposal is a bad idea regardless of
whether it'd be technically feasible or not.

			regards, tom lane

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 8 Aug 2018 11:02:39 -0400

> And this will be simplify relationing while implementing them.

I think what you're looking for is "inclusion constraints" from Jeff
Davis:

https://postgr.es/m/1423354088.12308.117.camel@jeff-desktop

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

---

From: KES <[email-masked]>
Date: Thu, 09 Aug 2018 13:11:05 +0300

>Yes, it would work, but doing that only for equality would be surprising
 to many people 

Why surprising? It is [documented](https://www.postgresql.org/docs/current/static/sql-createtable.html#sql-createtable-exclude):
>If all of the specified operators test for equality, this is equivalent to a UNIQUE constraint, although an ordinary unique constraint will be faster.

Thus the UNIQUE constraint is just particular case of exclusion constraint, is not?

Tom
>It's less efficient (1) and less portable
Yes, portability has matter, but more general SQL would be more efficient at developer hours to support such application in compare to writing many particular SQL's (one SQL expression is better than two which do same job). Personally I would close the eyes on portability in favor of using modern features (looking forward for inclusion constraint)

For speed efficiency (1) this particular case of exclusion constraint can be implemented via btree-based uniqueness. (like uniqueness is implemented via indexes under the hood. but the implementaion details have no matter as for me)

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 9 Aug 2018 15:09:13 -0400

On Thu, Aug 9, 2018 at 01:11:05PM +0300, KES wrote:
> Why surprising? It is
> [documented](https://www.postgresql.org/docs/current/static/sql-create
> table.html#sql-createtable-exclude):
> >If all of the specified operators test for equality, this is
> >equivalent to a UNIQUE constraint, although an ordinary unique
> >constraint will be faster.
>

Well, for me a UNIQUE constraint guarantees each discrete value is
unique, while exclusion constraint says discrete or ranges or geometric
types don't overlap.  I realize equality is a special case of discrete,
but having such cases be marked as UNIQUE seems too confusing.

---

From: Tom Lane <[email-masked]>
Date: Thu, 09 Aug 2018 15:31:09 -0400

> but having such cases be marked as UNIQUE seems too confusing.

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"?

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Thu, 9 Aug 2018 13:15:08 -0700

> "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 <replaceable
class="parameter">numeric_literal</replaceable>, REM
       The <literal>EXCLUDE</literal> 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 <literal>TRUE</literal>.  If all of the
-      specified operators test for equality, this is equivalent to a
-      <literal>UNIQUE</literal> 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 <literal>FALSE<literal/>.
+      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 <xref linkend="datatype-geometric"/>) by using the
@@ -968,6 +966,14 @@ WITH ( MODULUS <replaceable
class="parameter">numeric_literal</replaceable>, REM
       exclusion constraint on a subset of the table; internally this
creates a
       partial index. Note that parentheses are required around the
predicate.
      </para>
+
+     <para>
+      <productname>PostgreSQL</productname> 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 <literal>UNIQUE<literal/> constraint.
+     </para>
     </listitem>
    </varlistentry>

---

From: Vik Fearing <[email-masked]>
Date: Thu, 9 Aug 2018 23:35:56 +0200

> but having such cases be marked as UNIQUE seems too confusing.

One of the things I'm currently trying to implement is the WITHOUT
OVERLAPS for UNIQUE constraints.

See SQL:2016 section 11.7 <unique constraint definition>
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

---

From: KES <[email-masked]>
Date: Fri, 10 Aug 2018 12:10:47 +0300

huh, maybe you are right, I missread that. English is not my native language.
Actually I come there from FK constraints. 

Would it be sufficient for FK require not UNIQUEs, but **allow** "EXCLUDE with operators that act like equality"?