Typo in doc or wrong EXCLUDE implementation

From: PG Doc comments form <noreply(at)postgresql(dot)org>
To: pgsql-docs(at)lists(dot)postgresql(dot)org
Cc: kes-kes(at)yandex(dot)ru
Subject: Typo in doc or wrong EXCLUDE implementation
Date: 2018-07-10 09:34:36
Message-ID: 153121527691.1408.5686988620817799073@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-docs pgsql-hackers

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

Responses

Browse pgsql-docs by date

  From Date Subject
Next Message PG Doc comments form 2018-07-10 15:57:52 Multivariate statistics
Previous Message David G. Johnston 2018-07-07 15:26:49 Re: \i and \ir separated by \if now...

Browse pgsql-hackers by date

  From Date Subject
Next Message Andrey V. Lepikhov 2018-07-10 09:41:30 Re: [PATCH] Timestamp for a XLOG_BACKUP_END WAL-record
Previous Message Peter Eisentraut 2018-07-10 09:33:20 Re: Changing WAL Header to reduce contention during ReserveXLogInsertLocation()