SELECT query fails after pg_upgrade as the conditional operator fails

classic Classic list List threaded Threaded
5 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 from postgres 9.3.12 to 9.6.12

We failed to query few of the rows in the table using a TEXT field.

 

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.

Old datastore from 9.3.12 has saved so we are able to reproduce it consistently.

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

Juan José Santamaría Flecha

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
Reply | Threaded
Open this post in threaded view
|

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

Nithin Johnson

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 <[hidden email]>
Sent: 14 April 2019 01:04
To: Nithin Johnson <[hidden email]>
Cc: [hidden email]; Suresh Thelkar <[hidden email]>; Subhasis Bhattacharya <[hidden email]>
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

Reply | Threaded
Open this post in threaded view
|

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

Juan José Santamaría Flecha

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 Flecha
Reply | Threaded
Open this post in threaded view
|

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

Suresh Thelkar

Thanks very much Juan for your quick reply.  It helped us a lot. As suggested we have logged the following bug for pg_upgrade.

 

https://www.postgresql.org/message-id/15755-a5d9a0bea7ca931a@...

 

Please look into it and let us know if any further details/info is needed.

 

Regards,

Suresh

 

From: Juan José Santamaría Flecha <[hidden email]>
Date: Sunday, 14 April 2019 at 3:48 PM
To: Nithin Johnson <[hidden email]>
Cc: "[hidden email]" <[hidden email]>, Suresh Thelkar <[hidden email]>, subhasis bhattacharya <[hidden email]>
Subject: Re: SELECT query fails after pg_upgrade as the conditional operator fails

 

 

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 Flecha