Re: BUG #15755: After upgrading to 9.6.12 using pg_upgrade, select query does not return the expected results.

On Mon, Apr 15, 2019 at 1:03 AM PG Bug reporting form <
noreply(at)postgresql(dot)org> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 15755
> Logged by: pg_upgrade fails intermittently from version 9.3.12 to
> 9.6.12
> Email address: suresh(dot)thelkar(at)altair(dot)com
> PostgreSQL version: 9.6.12
> Operating system: RHEL 7.5
> [...]
> One of the workarounds suggested is to rebuild the indexes of the corrupted
> table. We tried this workaround, reindex at table level did not help us
>

You didn't actually succeed in forming valid and executable REINDEX command
so whether or not it would have worked at the table/index level is an
unsolved question. Given that it worked at the whole database level what I
suspect happens is the whole DB command succeeded and when it got to the
problem index it fixed the problem. Had the original table/index commands
been written correctly they would have also resulted in a fixed setup and
the need to reindex the whole database would have been mitigated (though
given the possibility of other indexes having problems a whole database
reindex was probably a worthwhile exercise anyway).

Appreciate your help in letting us know the following.
> 1. It looks like ONLY indexes are corrupted in our case and reindexing on
> whole database solved our issue. Just eager to know is there any
> possibility
> that similar corruption can happen for other database objects like tables,
> sequences apart from indexes?
>

Corruption is always a possibility though indexes, because they are
ordered, are the most vulnerable.

/* However REINDEX is not successful neither against the table nor the index
> */
>
> broken_db=# REINDEX TABLE xyz.job_attr;
> ERROR: syntax error at or near "QUERY"
> LINE 1: QUERY PLANREINDEX TABLE xyz.job_attr;
>
>
You got a syntax error "at or near QUERY"; something you did entering the
command (LINE 1: is the whole command the server saw) was problematic and
the error has no bearing on whether "REINDEX TABLE xyz.job_attr" would have
worked without the typo (it likely would have)

> broken_db=# REINDEX INDEX job_attr_idx;
> ERROR: relation "job_attr_idx" does not exist
>

The index was never found, in the search_path, and so no reindexing was
attempted. Whether it would have worked had you correctly added the
necessary schema to the identifier (or search_path) remains unknown (it
likely would have).

Its not enough to say/know that something "didn't work" - understanding
"why" it failed is necessary before drawing conclusions.

David J.