Обсуждение: 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