Re: Primary and unique key corruption

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/>