Date: Tue, 7 Aug 2018 13:36:59 -0400
From: Bruce Momjian <bruce@momjian.us>
To: kes-kes@yandex.ru,
	PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: Typo in doc or wrong EXCLUDE implementation
Message-ID: <20180807173659.GD7297@momjian.us>
References: <153121527691.1408.5686988620817799073@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <153121527691.1408.5686988620817799073@wrigleys.postgresql.org>
User-Agent: Mutt/1.5.23 (2014-03-12)
Precedence: bulk


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 +