Re: default sorting behavior for index

Zhihong Yu <zyu(at)yugabyte(dot)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