Re: SQL:2011 application time

On Wed, 13 Nov 2024, 11:11 Peter Eisentraut, <peter(at)eisentraut(dot)org> wrote:
> This conditional is really hard to understand:
>
> + /*
> + * The AM must support uniqueness, and the index must in fact be
> unique.
> + * If we have a WITHOUT OVERLAPS constraint (identified by
> uniqueness +
> + * exclusion), we can use that too.
> + */
> + if ((!indexRel->rd_indam->amcanunique ||
> + !indexRel->rd_index->indisunique) &&
> + !(indexRel->rd_index->indisunique &&
> indexRel->rd_index->indisexclusion))
>
> Why can we have a indisunique index when the AM is not amcanunique? Are
> we using the fields wrong?

I called this issue out earlier this year: amcanunique implies
btree-style uniqueness, and allows CREATE UNIQUE INDEX. However, that
IndexAmRoutine field seems to be ignored for indexes that are created
to back temporal unique constraints, which thus get
indrel->indisunique = true. This causes interesting issues when you
look at the index catalog and errors: there are indexes with
indisunique using gist, but CREATE UNIQUE INDEX USING gist (...)
throws the nice "access method "gist" does not support unique indexes"
error.

It'd be nice if there was a better internal API to describe what types
of uniqueness each index supports, so CREATE UNIQUE INDEX could work
with gist for WITHOUT OVERLAPS, and these WITHOUT OVERLAPS unique
indexes could be attached to primary keys without taking O(>=
tablesize) of effort.

Kind regards,

Matthias van de Meent
Neon (https://neon.tech)