Re: Typo in doc or wrong EXCLUDE implementation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Bruce Momjian

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  <[hidden email]>        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 +

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

kes-kes
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" <[hidden email]>:

> 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 <[hidden email]> 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 +

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Bruce Momjian
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  <[hidden email]>        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 +

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Tom Lane-2
Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Alvaro Herrera-9
In reply to this post by kes-kes
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:

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

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

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

kes-kes
In reply to this post by Tom Lane-2
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" <[hidden email]>:

> Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Bruce Momjian
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  <[hidden email]>        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 +

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Tom Lane-2
Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

David G Johnston
On Thu, Aug 9, 2018 at 12:31 PM, Tom Lane <[hidden email]> 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>
 


create-table-exclude-doc.diff (2K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

Vik Fearing-4
In reply to this post by Bruce Momjian
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

Reply | Threaded
Open this post in threaded view
|

Re: Typo in doc or wrong EXCLUDE implementation

kes-kes
In reply to this post by Tom Lane-2
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" <[hidden email]>:

> Bruce Momjian <[hidden email]> 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

Previous Thread Next Thread