Zhihong Yu <zyu@yugabyte.com> writes:
> I was looking at this check in src/backend/parser/parse_utilcmd.c w.r.t.
> constraint:
> ...
> If the index has DESC sorting order, why it cannot be used to back a
> constraint ?
> Some concrete sample would help me understand this.
Please read the nearby comments, particularly
* Insist on default opclass, collation, and sort options.
* While the index would still work as a constraint with
* non-default settings, it might not provide exactly the same
* uniqueness semantics as you'd get from a normally-created
* constraint; and there's also the dump/reload problem
* mentioned above.
The "mentioned above" refers to this:
* Insist on it being a btree. That's the only kind that supports
* uniqueness at the moment anyway; but we must have an index that
* exactly matches what you'd get from plain ADD CONSTRAINT syntax,
* else dump and reload will produce a different index (breaking
* pg_upgrade in particular).
The concern about whether the uniqueness semantics are the same probably
mostly applies to just the opclass and collation properties. However,
rd_indoption contains AM-specific options, and we have little ability
to be sure in this code exactly what those bits might do. In any case
we'd definitely have a risk of things breaking during pg_upgrade if we
ignore rd_indoption.
regards, tom lane