Subject: Could not use index because of indcheckxmin is true

From: Japin Li <[email-masked]>
Date: Thu, 12 Oct 2023 15:06:17 +0800

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 12 Oct 2023 10:30:02 -0400

> be used with indcheckxmin is ture, how can I fix this?

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

>> 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

---

From: Japin Li <[email-masked]>
Date: Thu, 12 Oct 2023 23:31:26 +0800

> AFAIR there is no code that does so.

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

-- 
Regrads,
Japin Li