BUG #17188: Multiple rows are present with same values on a unique column

Looping ++Saroj Sir

On Fri, Sep 10, 2021 at 3:36 PM PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17188
Logged by: DBA TEAM
Email address: devops@getfareye.com
PostgreSQL version: 9.6.22
Operating system: Ubuntu 20.04.2 LTS
Description:

We are having a table Users in which multiple columns named emp_code and
company_id have a unique constraint applied to them.But for multiple rows
there are values which are equal for both of the columns.

Users table definition
Table "public.users"
Column | Type | Collation |
Nullable | Default | Storage | Stats target |
Description

-----------------------------+-----------------------------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | |
not
null | nextval('users_id_seq'::regclass) | plain | |
login | character varying(50) | |

| | extended | |
password | character varying(100) | |

| | extended | |
first_name | character varying(50) | |

| NULL::character varying | extended | |
last_name | character varying(50) | |

| NULL::character varying | extended | |
email | text | |

| NULL::character varying | extended | |
activated | boolean | |

| false | plain | |
lang_key | character varying(5) | |

| | extended | |
activation_key | character varying(20) | |

| | extended | |
created_by | character varying(50) | |
not
null | 'system'::character varying | extended | |
created_date | timestamp without time zone | |
not
null | now() | plain | |
last_modified_by | character varying(50) | |

| | extended | |
last_modified_date | timestamp without time zone | |

| | plain | |
emp_code | character varying(64) | |

| | extended | |
mobile | character varying(16) | |

| NULL::character varying | extended | |
company_id | bigint | |

| 1 | plain | |
user_type_id | bigint | |

| 1 | plain | |
salt | character varying(255) | |

| | extended | |
city_id | bigint | |

| | plain | |
hub_id | bigint | |

| | plain | |
last_login_time | timestamp without time zone | |

| | plain | |
merchants | character varying(255) | |

| | extended | |
is_auto_push_mail_activated | boolean | |

| false | plain | |
wrong_attempts | integer | |

| 0 | plain | |
locked | boolean | |

| false | plain | |
locked_date_time | timestamp without time zone | |

| | plain | |
is_logged_in | boolean | |

| false | plain | |
service_provider_code | character varying(64) | |

| NULL::character varying | extended | |
profile_image | character varying(256) | |

| | extended | |
icdr | boolean | |

| false | plain | |
routing_json | text | |

| | extended | |
auth_user_id | integer | |

| | plain | |
user_id | character varying(255) | |

| | extended | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_company_id_emp_code_key" UNIQUE CONSTRAINT, btree (company_id,
emp_code)
"users_company_id_user_type_id_city_id_hub_id_activation_key_cre" btree
(company_id, user_type_id, city_id, hub_id, activation_key, created_date,
login)
"users_login_key" UNIQUE CONSTRAINT, btree (login)

Here is the sample data
select id,company_id,emp_code from users where
emp_code='4742_bpl-14_bpl';

id | company_id | emp_code
------+------------+-----------------
5065 | 8 | 4742_bpl-14_bpl
1594 | 8 | 4742_bpl-14_bpl

--
*Regards,*
*Ashish *
*Sr. Database Administrator*
*+91-8699382848*

On Fri, Sep 10, 2021 at 7:36 PM Ashish Kumar
<ashish.kumar2@getfareye.com> wrote:

On Fri, Sep 10, 2021 at 3:36 PM PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 17188
Logged by: DBA TEAM
Email address: devops@getfareye.com
PostgreSQL version: 9.6.22
Operating system: Ubuntu 20.04.2 LTS
Description:

We are having a table Users in which multiple columns named emp_code and
company_id have a unique constraint applied to them.But for multiple rows
there are values which are equal for both of the columns.

Users table definition
Table "public.users"
[...]
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"users_company_id_emp_code_key" UNIQUE CONSTRAINT, btree (company_id,
emp_code)

Here is the sample data
select id,company_id,emp_code from users where
emp_code='4742_bpl-14_bpl';

id | company_id | emp_code
------+------------+-----------------
5065 | 8 | 4742_bpl-14_bpl
1594 | 8 | 4742_bpl-14_bpl

It looks like an index corruption. Is there any chance you can try
amcheck (it should be available with ppgdg packages, but if needed:
https://github.com/petergeoghegan/amcheck) and verify the
users_company_id_emp_code_key (and possibly the other) index?