Re: Possible corrupt index?

On 4/16/19 10:16 AM, Zahir Lalani wrote:
>>Which version? What are the queries you are running which give unexpected behavior? Have your run explain analyze on
thoseto check >what plan is being used? Have your reindexed all or only the  one you suspect?
 
> 
> Hi Michael
> 
> Version: PostgreSQL 9.6.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
> 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

Is this the same for the other environments?

What does:

SHOW lc_collate;

produce in each environment?

Are you doing the below through Navicat or psql?

If through Navicat, what happens if you use psql?

> 
> LIVE – production environment (as opposed to Dev and UAT)
> 
> Query: select id from briefs_master where ext_system_ref = '12345'
> 
> Explain:
> 
> Seq Scan on briefs_master  (cost=0.00..2937.90 rows=1 width=4) (actual 
> time=18.082..18.082 rows=0 loops=1)
> 
>    Filter: ((ext_system_ref)::text = '12345'::text)
> 
>    Rows Removed by Filter: 31235
> 
> Planning time: 0.242 ms
> 
> Execution time: 18.096 ms
> 
> Reindex was done initially on the primary and then on all in the table.
> 
> So when we reset the data into the ext_system_ref field, the next query 
> returns fine. However, the issue is that since the system thinks there 
> is no primary, we are seeing this value get over-written with a null 
> several minutes later as other rows are added
> 
> Z
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com