Primary and unique key corruption

From: still Learner <stilllearner23(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Primary and unique key corruption
Date: 2020-12-14 09:42:50
Message-ID: CAB3gP9LECq+WN2KLHqG3Mk2wmt8bzWzcxKyfHU3SSfVw-0sipA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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?

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Magnus Hagander 2020-12-14 09:48:28 Re: Primary and unique key corruption
Previous Message Dischner, Anton 2020-12-14 09:40:23 AW: tools like innotop for PostgreSQL?