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

Hello David,

As said earlier we are able to reproduce the pg_upgrade issue again. We actually have several indexes in our datastore. Surprisingly it seems only the following index(job_attr_idx) got corrupted. And I just applied reindex to this particular index and it solved whole of our problem. Please look into the following for more details.

Details of the table where we see the corruption
pbs_datastore=# \d pbs.job_attr;
Table "pbs.job_attr"
Column | Type | Modifiers
---------------+---------+-----------
ji_jobid | text | not null
attr_name | text | not null
attr_resource | text |
attr_value | text |
attr_flags | integer | not null
Indexes:
"job_attr_idx" btree (ji_jobid, attr_name, attr_resource)

Before applying reindex:
pbs_datastore=# select ji_jobid, attr_name from pbs.job_attr where ji_jobid='1.testdev-08-r7';
ji_jobid | attr_name | attr_resource | attr_value | attr_flags
----------+-----------+---------------+------------+------------
(0 rows)

pbs_datastore=# select ji_jobid, attr_name from pbs.job_attr where ji_jobid like '%1.testdev-08-r7';
ji_jobid | attr_name
------------------+-----------------------
1.testdev-08-r7 | job_state
1.testdev-08-r7 | mtime
1.testdev-08-r7 | substate
1.testdev-08-r7 | Job_Name
1.testdev-08-r7 | Job_Owner
<similar data follows>
Here the data is present in the table but the query works with only LIKE operator. EQUALS operator does not work.

Apply reindex
pbs_datastore=# reindex index pbs.job_attr_idx;
REINDEX

After the above reindex
pbs_datastore=# select ji_jobid, attr_name from pbs.job_attr where ji_jobid='1.testdev-08-r7';
ji_jobid | attr_name
-----------------+-----------------------
1.testdev-08-r7 | Checkpoint
1.testdev-08-r7 | Error_Path
1.testdev-08-r7 | Exit_status
1.testdev-08-r7 | Hold_Types
1.testdev-08-r7 | Job_Name
1.testdev-08-r7 | Job_Owner
<similar data follows>

Here EQUAS operator work.

Summary: It looks like only one index got corrupted and reindex on the particular index indeed solved our issue. We tried to reproduce this issue 4-5 times and all the time only one index i.e. pbs.job_attr_idx: got corrupted. So given this circumstance can we rely on reindex of corrupted index or at whole database level as a SOLUTION to the pg_upgrade that we are seeing.

It appears that pg_upgrade corrupted an index of an otherwise healthy source database during the upgrade process. Seems like pg_upgrade bug to me and could we know if it corrupts anything else ? or in other words what should be fixed in pg_upgrade.

Thanks in advance,
Suresh

From: Suresh Thelkar <suresh(dot)thelkar(at)altair(dot)com>
Date: Tuesday, 16 April 2019 at 4:33 PM
To: "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, "pgsql-bugs(at)lists(dot)postgresql(dot)org" <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Cc: subhasis bhattacharya <subhasis(dot)bhattacharya(at)altair(dot)com>, Nithin Johnson <nithin(dot)johnson(at)altair(dot)com>
Subject: 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.