Bug reference: 15755 Logged by: pg_upgrade fails intermittently from version 9.3.12 to 9.6.12 Email address: suresh.thelkar@altair.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.
