RE: SELECT query fails after pg_upgrade as the conditional operatorfails

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@sys workspace]# export PGOPTIONS="-P"

[root@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.santamaria@gmail.com>
Sent: 14 April 2019 01:04
To: Nithin Johnson <nithin.johnson@altair.com>
Cc: pgsql-admin@lists.postgresql.org; Suresh Thelkar <suresh.thelkar@altair.com>; Subhasis Bhattacharya <subhasis.bhattacharya@altair.com>
Subject: Re: SELECT query fails after pg_upgrade as the conditional operator fails