Thread: BUG #17188: Multiple rows are present with same values on a unique column
BUG #17188: Multiple rows are present with same values on a unique column
From
PG Bug reporting form
Date:
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
Re: BUG #17188: Multiple rows are present with same values on a unique column
From
Ashish Kumar
Date:
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
Re: BUG #17188: Multiple rows are present with same values on a unique column
From
Julien Rouhaud
Date:
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?