Typo in doc or wrong EXCLUDE implementation

Started by PG Doc comments formover 7 years ago12 messages

noreply@postgresql.org

over 7 years ago

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 =)

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

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: PG Doc comments form (#1)

Re: Typo in doc or wrong EXCLUDE implementation

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.

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

On Tue, Jul 10, 2018 at 09:34:36AM +0000, PG Doc comments form wrote:

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

--
Bruce Momjian <bruce@momjian.us> 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 +

KES

kes-kes@yandex.ru

over 7 years ago

In reply to: Bruce Momjian (#2)

Re: Typo in doc or wrong EXCLUDE implementation

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" <bruce@momjian.us>:

Show quoted text

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.

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

On Tue, Jul 10, 2018 at 09:34:36AM +0000, PG Doc comments form wrote:

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

--
  Bruce Momjian <bruce@momjian.us> 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 +

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: KES (#3)

Re: Typo in doc or wrong EXCLUDE implementation

On Wed, Aug 8, 2018 at 01:55:53PM +0300, KES wrote:

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.

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.

--
Bruce Momjian <bruce@momjian.us> 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 +

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Bruce Momjian (#4)

Re: Typo in doc or wrong EXCLUDE implementation

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Aug 8, 2018 at 01:55:53PM +0300, KES wrote:

If such exclusion constraint would be marked as UNIQUE we can use it for FK while implementing temporal/bi-temporal tables.

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.

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

Alvaro Herrera

alvherre@2ndquadrant.com

over 7 years ago

In reply to: KES (#3)

Re: Typo in doc or wrong EXCLUDE implementation

On 2018-Aug-08, KES wrote:

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.

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

/messages/by-id/1423354088.12308.117.camel@jeff-desktop

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

KES

kes-kes@yandex.ru

over 7 years ago

In reply to: Tom Lane (#5)

Re: Typo in doc or wrong EXCLUDE implementation

Bruce:

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)

08.08.2018, 16:51, "Tom Lane" <tgl@sss.pgh.pa.us>:

Show quoted text

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Aug 8, 2018 at 01:55:53PM +0300, KES wrote:

If such exclusion constraint would be marked as UNIQUE we can use it for FK while implementing temporal/bi-temporal tables.

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.

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

Bruce Momjian

bruce@momjian.us

over 7 years ago

In reply to: KES (#7)

Re: Typo in doc or wrong EXCLUDE implementation

On Thu, Aug 9, 2018 at 01:11:05PM +0300, KES wrote:

Bruce:

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-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.

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

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.

--
Bruce Momjian <bruce@momjian.us> 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 +

Tom Lane

tgl@sss.pgh.pa.us

over 7 years ago

In reply to: Bruce Momjian (#8)

Re: Typo in doc or wrong EXCLUDE implementation

Bruce Momjian <bruce@momjian.us> writes:

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.

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

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.

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

#10

David G. Johnston

david.g.johnston@gmail.com

over 7 years ago

In reply to: Tom Lane (#9)

1 attachment(s)

Re: Typo in doc or wrong EXCLUDE implementation

On Thu, Aug 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. 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 <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>

Attachments:

create-table-exclude-doc.diffapplication/octet-stream; name=create-table-exclude-doc.diffDownload

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>

#11

Vik Fearing

vik.fearing@2ndquadrant.com

over 7 years ago

In reply to: Bruce Momjian (#8)

Re: Typo in doc or wrong EXCLUDE implementation

On 09/08/18 21:09, Bruce Momjian wrote:

On Thu, Aug 9, 2018 at 01:11:05PM +0300, KES wrote:

Bruce:

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-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.

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

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.

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

#12

KES

kes-kes@yandex.ru

over 7 years ago

In reply to: Tom Lane (#9)

Re: Typo in doc or wrong EXCLUDE implementation

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

09.08.2018, 22:31, "Tom Lane" <tgl@sss.pgh.pa.us>:

Show quoted text

Bruce Momjian <bruce@momjian.us> writes:

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.

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

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.

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