Обсуждение: default sorting behavior for index
Hi,
I was looking at this check in src/backend/parser/parse_utilcmd.c w.r.t. constraint:
if (indclass->values[i] != defopclass ||
attform->attcollation != index_rel->rd_indcollation[i] ||
attoptions != (Datum) 0 ||
index_rel->rd_indoption[i] != 0)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("index \"%s\" column number %d does not have default sorting behavior", index_name, i + 1),
errdetail("Cannot create a primary key or unique constraint using such an index."),
attform->attcollation != index_rel->rd_indcollation[i] ||
attoptions != (Datum) 0 ||
index_rel->rd_indoption[i] != 0)
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
errmsg("index \"%s\" column number %d does not have default sorting behavior", index_name, i + 1),
errdetail("Cannot create a primary key or unique constraint using such an index."),
It seems this first came in via `Indexes with INCLUDE columns and their support in B-tree`
If the index has DESC sorting order, why it cannot be used to back a constraint ?
Some concrete sample would help me understand this.
Thanks
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