public inbox for [email protected]
help / color / mirror / Atom feedCould not use index because of indcheckxmin is true
3+ messages / 2 participants
[nested] [flat]
* Could not use index because of indcheckxmin is true
@ 2023-10-12 07:06 Japin Li <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Japin Li @ 2023-10-12 07:06 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Could not use index because of indcheckxmin is true
@ 2023-10-12 14:30 Tom Lane <[email protected]>
parent: Japin Li <[email protected]>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2023-10-12 14:30 UTC (permalink / raw)
To: Japin Li <[email protected]>; +Cc: pgsql-sql
Japin Li <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Could not use index because of indcheckxmin is true
@ 2023-10-12 15:31 Japin Li <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 3+ messages in thread
From: Japin Li @ 2023-10-12 15:31 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-sql
On Thu, 12 Oct 2023 at 22:30, Tom Lane <[email protected]> wrote:
> Japin Li <[email protected]> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-12 15:31 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-12 07:06 Could not use index because of indcheckxmin is true Japin Li <[email protected]>
2023-10-12 14:30 ` Tom Lane <[email protected]>
2023-10-12 15:31 ` Japin Li <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox