pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #17188: Multiple rows are present with same values on a unique column
3+ messages / 3 participants
[nested] [flat]
* BUG #17188: Multiple rows are present with same values on a unique column
@ 2021-09-10 10:05 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2021-09-10 10:05 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: devops@getfareye.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17188: Multiple rows are present with same values on a unique column
@ 2021-09-10 10:07 Ashish Kumar <ashish.kumar2@getfareye.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 3+ messages in thread
From: Ashish Kumar @ 2021-09-10 10:07 UTC (permalink / raw)
To: Fareye DevOps <devops@getfareye.com>; pgsql-bugs@lists.postgresql.org; +Cc: Saroj K Mohapatra <saroj.mohapatra@getfareye.com>
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*
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #17188: Multiple rows are present with same values on a unique column
@ 2021-09-10 11:47 Julien Rouhaud <rjuju123@gmail.com>
parent: Ashish Kumar <ashish.kumar2@getfareye.com>
0 siblings, 0 replies; 3+ messages in thread
From: Julien Rouhaud @ 2021-09-10 11:47 UTC (permalink / raw)
To: Ashish Kumar <ashish.kumar2@getfareye.com>; +Cc: Fareye DevOps <devops@getfareye.com>; PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>; Saroj K Mohapatra <saroj.mohapatra@getfareye.com>
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?
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2021-09-10 11:47 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-09-10 10:05 BUG #17188: Multiple rows are present with same values on a unique column PG Bug reporting form <noreply@postgresql.org>
2021-09-10 10:07 ` Ashish Kumar <ashish.kumar2@getfareye.com>
2021-09-10 11:47 ` Julien Rouhaud <rjuju123@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox