Обсуждение: default sorting behavior for index

Поиск
Список
Период
Сортировка

default sorting behavior for index

От
Zhihong Yu
Дата:
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."),

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

Re: default sorting behavior for index

От
Tom Lane
Дата:
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