Re: Primary and unique key corruption

No, I haven't upgraded.

On Mon, Dec 14, 2020 at 3:18 PM Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> On Mon, Dec 14, 2020 at 10:45 AM still Learner <stilllearner23(at)gmail(dot)com>
> wrote:
>
>> Hi Team,
>>
>> The primary key seems corrupted on this table. I have a similar unique
>> index issue with another table in the same database.
>>
>> Also one more wired thing is the duplicity is shown only when the "
>> *ilike*" command and not with the like or = command.
>>
>> I will remove the duplicates and rebuild, but how come the issue happened
>> in a couple of tables?
>>
>>
>> \d contactdtls
>> Table "xxxxx.contactdtls"
>> Column | Type | Collation | Nullable |
>> Default
>>
>> -------------------------+-----------------------+-----------+----------+---------
>> user_name | character varying(25) | | not null |
>> role_id | numeric(2,0) | | not null |
>> mobilenumber | numeric(10,0) | | |
>> emailid | character varying(40) | | |
>> Indexes:
>> "contactdtls_pkey" PRIMARY KEY, btree (user_name, role_id)
>>
>>
>> select count(1) from (select user_name,role_id,count(1) from contactdtls
>> group by 1,2 having count(1)>1)as a;
>> count
>> -------
>> 45
>> (1 row)
>>
>> select user_name,role_id from contactdtls where user_name ilike 'AVR-01';
>> user_name | role_id
>> -----------+---------
>> AVR-01 | 1
>> AVR-01 | 1
>> (2 rows)
>>
>> select user_name,role_id from contactdtls where user_name like 'AVR-01';
>> user_name | role_id
>> -----------+---------
>> AVR-01 | 1
>> (1 row)
>>
>> select version();
>> version
>>
>>
>> --------------------------------------------------------------------------------------------------------
>> PostgreSQL 11.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1
>> 20190507 (Red Hat 8.3.1-4), 64-bit
>> (1 row)
>>
>> Also, is there any other way to find all index corruptions of a database,
>> in any stats table or so?
>>
>
> Did you by any chance upgrade this machine from a pre-redhat 8 system at
> some point, without reindexing? This sounds exactlylike the issues you'd
> get with the problems outlined in
> https://wiki.postgresql.org/wiki/Locale_data_changes.
>
> In that case, your solution is to REINDEX all indexes on text based
> fields. (And of course clean up the duplicates that got in while you were
> running with incompatible locales)
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/>
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
>