Обсуждение: Could not use index because of indcheckxmin is true

Hi,

When I create an index using CREATE INDEX (without concurrently), I get
an index which indcheckxmin is true.  According to the docs[1], it cannot
be used with indcheckxmin is ture, how can I fix this?

> indcheckxmin bool
>     If true, queries must not use the index until the xmin of this pg_index
>     row is below their TransactionXmin event horizon, because the table may
>     contain broken HOT chains with incompatible rows that they can see

[1] https://www.postgresql.org/docs/current/catalog-pg-index.html

-- 
Regrads,
Japin Li

Japin Li <japinli@hotmail.com> writes:
> When I create an index using CREATE INDEX (without concurrently), I get
> an index which indcheckxmin is true.  According to the docs[1], it cannot
> be used with indcheckxmin is ture, how can I fix this?

There is nothing to fix; you just wait a little bit.

>> indcheckxmin bool
>> If true, queries must not use the index until the xmin of this pg_index
>> row is below their TransactionXmin event horizon, because the table may
>> contain broken HOT chains with incompatible rows that they can see

Note the "until" clause.  Once transactions that ran concurrently
with the CREATE INDEX are done, the index is available for use.
There is no need for anyone to clear the indcheckxmin flag, and
AFAIR there is no code that does so.

            regards, tom lane

On Thu, 12 Oct 2023 at 22:30, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Japin Li <japinli@hotmail.com> writes:
>> When I create an index using CREATE INDEX (without concurrently), I get
>> an index which indcheckxmin is true.  According to the docs[1], it cannot
>> be used with indcheckxmin is ture, how can I fix this?
>
> There is nothing to fix; you just wait a little bit.
>
>>> indcheckxmin bool
>>> If true, queries must not use the index until the xmin of this pg_index
>>> row is below their TransactionXmin event horizon, because the table may
>>> contain broken HOT chains with incompatible rows that they can see
>
> Note the "until" clause.  Once transactions that ran concurrently
> with the CREATE INDEX are done, the index is available for use.
> There is no need for anyone to clear the indcheckxmin flag, and
> AFAIR there is no code that does so.
>

Thanks for your explanation. After wait a moment, the index can be used.

-- 
Regrads,
Japin Li