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

Thanks for the quick reply David. It looks like we tried the reindex commands at table or index level without schema identifier in a hurry. Thanks for catching that. We are in the process of trying to reproduce the issue and then try these reindex command at index and table level also and post you the results.

Thanks very much for your help on this.

Regards,
Suresh

From: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
Date: Tuesday, 16 April 2019 at 12:01 PM
To: Suresh Thelkar <suresh(dot)thelkar(at)altair(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: 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<mailto: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<mailto: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.