Thread: 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 +
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>: > 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 +
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 +
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
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
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 uniqueconstraint 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 applicationin compare to writing many particular SQL's (one SQL expression is better than two which do same job). PersonallyI 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. (likeuniqueness 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>: > 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-temporaltables. > >> 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
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 +
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
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>
Attachment
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
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>: > 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