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

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
Description:

When we upgraded Postgres from 9.3.12 to 9.6.12 we observed that queries
like the following are no longer giving the expected results.
select * from xyz.job_attr where jobid = 'foo';
Whereas when we modified the query to contain 'like' operator instead of
equals operator as given below it works perfectly fine.
select * from xyz.job_attr where jobid like '%foo';

We requested the pgsql mailing group for the help. Thanks to the respective
people for their prompt reply. Attaching the email communication regarding
the same towards the end of this ticket description for more details.

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
whereas reindex at the whole database level using the following command
solved our above problem.
broken_db=> REINDEX DATABASE broken_db;

We informed the pgsql group that the above workaround solved our issue and
then we are asked to create a bug for pg_upgrade in order to find out where
the problem is coming from and hence we logged the current bug.

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?
2. Is it fine to proceed with this workaround as solution for these kind of
problems during the upgrade.

Snapshot of Email communication that we had
******************************************************

I can use reindexing as a work around. Thanks!

Great!

If this is not something expected and if you need any more information on
this issue please let me know.

No, this is not the expected behaviour, you can open a bug for pg_upgrade in
order to find out where the problem comes from.

Regards,

Juan José Santamaría Flech

Thanks for the response Juan. Please find the requested details below:
All the steps are performed on the broken db after pg_upgrade

broken_db=# select * from xyz.job_attr where jobid = 'foo';
jobid | attr_name | attr_resource | attr_value | attr_flags
----------+-----------+---------------+------------+------------
(0 rows)

/* The query does not returns any error code and neither in the pg_log. But
it returns zero number of rows */
/* where as a query with trim returns 59 rows with same text match */

broken_db=# select count(*) from xyz.job_attr where trim(jobid) = 'foo';
count
-------
59
(1 row)

/* explain analyse results */

broken_db=# explain analyze select * from xyz.job_attr where jobid =
'foo';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on job_attr (cost=4.73..19.47 rows=59 width=57) (actual
time=0.005..0.005 rows=0 loops=1)
Recheck Cond: (jobid = 'foo'::text)
-> Bitmap Index Scan on job_attr_idx (cost=0.00..4.72 rows=59 width=0)
(actual time=0.005..0.005 rows=0 loops=1)
Index Cond: (jobid = 'foo'::text)
Planning time: 0.035 ms
Execution time: 0.025 ms
(6 rows)

broken_db=# explain analyze select * from xyz.job_attr where trim(jobid) =
'foo';
QUERY PLAN
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Seq Scan on job_attr (cost=0.00..28.20 rows=5 width=57) (actual
time=0.117..0.148 rows=59 loops=1)
Filter: (btrim(jobid) = 'foo'::text)
Rows Removed by Filter: 888
Planning time: 0.026 ms
Execution time: 0.158 ms
(5 rows)

/* table definition */

broken_db=# \d xyz.job_attr;
Table "xyz.job_attr"
Column | Type | Modifiers
---------------+---------+-----------
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 (jobid, attr_name, attr_resource)

/* 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;

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

/* then tried reindexing system tables, and it worked */

[root(at)sys workspace]# export PGOPTIONS="-P"
[root(at)sys workspace]# psql -d broken_db
psql (9.6.11)
Type "help" for help.
broken_db=# REINDEX DATABASE broken_db;
REINDEX

broken_db=# select count(*) from xyz.job_attr where jobid = 'foo';
count
-------
59
(1 row)


I can use reindexing as a work around. Thanks!

If this is not something expected and if you need any more information on
this issue please let me know.


Best Regards,
Nithin.

From: Juan José Santamaría Flecha <juanjo(dot)santamaria(at)gmail(dot)com>
Sent: 14 April 2019 01:04
To: Nithin Johnson <nithin(dot)johnson(at)altair(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org; Suresh Thelkar
<suresh(dot)thelkar(at)altair(dot)com>; Subhasis Bhattacharya
<subhasis(dot)bhattacharya(at)altair(dot)com>
Subject: Re: SELECT query fails after pg_upgrade as the conditional operator
fails

The following query fails:
SELECT * from table where jobid = 'foo';

Whereas the following is passing:
SELECT * from table where jobid LIKE '%foo';
SELECT * from table where trim(jobid) = 'foo';

We were suspecting some leading invisible characters before 'foo'. but the
following queries went well:
SELECT left(jobid, 1) from table where jobid LIKE '%foo';
SELECT ascii(jobid) from table where jobid LIKE '%foo';

Please post the error code, also the plan for the different queries and the
description of the table.

Can you check if rebuilding the table's indexes solves the issue? If so,
send output of that operation.

Regards,

Juan José Santamaría Flecha