SELECT query fails after pg_upgrade as the conditional operator fails

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

SELECT query fails after pg_upgrade as the conditional operator fails

Nithin Johnson

We are seeing this intermittent problem after we upgrade (using pg_upgrade) from postgres 9.3.12 to 9.6.12

Querying  few of the rows in the table using a TEXT field is failing.

 

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';

 

We have tried upgrading to other versions like 9.5.16 and 9.4.21 but faced with same issue.

We are able to reproduce it consistently using the old database from 9.3.12.

Observing issues with same rows every time (not random)

Ecoding: SQL_ASCII

Platform: RHEL 7.5

 

Any help would be appreciated.

 

 

Best Regards,

Nithin.

 

Reply | Threaded
Open this post in threaded view
|

Re: SELECT query fails after pg_upgrade as the conditional operator fails

Andreas Kretschmer-3


Am 13.04.19 um 11:22 schrieb Nithin Johnson:
>
> We are seeing this intermittent problem after we upgrade (using
> pg_upgrade) from postgres 9.3.12 to 9.6.12
>
> Querying  few of the rows in the table using a TEXT field is failing.
>

sounds like a corrupt index, can you show us the complete error message
and the table definition?


Regards, Andreas

--
2ndQuadrant - The PostgreSQL Support Company.
www.2ndQuadrant.com