Re: Index corruption / planner issue with one table in my pg 11.6 instance

Jerry Sievers <gsievers19(at)comcast(dot)net> writes:
> I suspect this was due to indcheckxmin=true for the involved index and
> the documented (but IMO confusing) interplay w/broken hot-chains and
> visibility.

Yeah. The reported behavior can mostly be explained if we assume
that there's some HOT chain in the table that involves an update
of this particular column, so that if we build an index on that
column we see a broken HOT chain, but building an index on some
other column doesn't have a problem.

The thing this doesn't easily explain is that the behavior persists
across repeated index rebuilds. A broken HOT chain is only broken
as long as the older entry is still visible-to-somebody, so that
such situations ought to be self-healing as time passes. If it
fails repeatedly, this theory requires assuming that either

1. You've got some extremely old open transactions (maybe forgotten
prepared transactions?), or

2. Your workload is constantly generating new broken HOT chains of
the same sort, so that there's usually a live one when you try
to build an index.

The fact that you even notice the indcheckxmin restriction indicates
that you do tend to have long-running transactions in the system,
else the index would come free for use fairly quickly. So #1 isn't
as implausible as I might otherwise think. But #2 seems probably
more likely on the whole. OTOH, neither point is exactly within
the offered evidence.

regards, tom lane