From: Марат Гасанян <marat_gasanyan@mail.ru>
To: pgsql-bugs@lists.postgresql.org
Subject: Problem with constraint unique.
Date: Wed, 11 Dec 2024 17:47:23 +0300
Message-ID: <1733928443.950047988@f125.i.mail.ru> (raw)
I apologies for bothering your team, before send this message we google the problem and was not able to find any tips about it. Our project encounter with quite strange situation where we found violation of unique constraint of primary key. We have a table that has an attribute ID as PK. The table name enerstorymain_task_info. Surprisingly the query
select id, count(*) from enerstroymain_task_info group by 1 having count(*) > 1
return a lot of rows. Than we rechecked PK it is ok. So we supposed that some how we had dropped the PK and then created duplicates and than rebuild the PK, but in this case PK would not rebuild. To avoid any weird settings I run the query from PGAdmin and DBView from different computers. Also I doubt that any kind problem like repeatable red could be the reason due to insert operation is done only a server without any complicated query. I mean simple INSERT INTO ….
The only strange thing is that the table is really width.
In the file i upload some rows that i get using the query:
select * from enerstroymain_task_info eti where id in (
select id from enerstroymain_task_info group by id having count (*)> 1
) limit 6
Here is DDL of the table
CREATE TABLE public . enerstroymain_task_info (
id uuid NOT NULL ,
"version" int4 NOT NULL ,
create_ts timestamp NULL ,
created_by varchar ( 50 ) NULL ,
update_ts timestamp NULL ,
updated_by varchar ( 50 ) NULL ,
delete_ts timestamp NULL ,
deleted_by varchar ( 50 ) NULL ,
appartment varchar ( 20 ) NULL ,
longitude float8 NULL ,
latitude float8 NULL ,
pu_affiliation varchar ( 255 ) NULL ,
ex_pu_montage_place varchar ( 255 ) NULL ,
subscriber_type varchar ( 50 ) NULL ,
ex_pu_num varchar ( 255 ) NULL ,
ex_pu_stamp varchar ( 100 ) NULL ,
ex_pu_montage_date date NULL ,
iik_stamp varchar ( 100 ) NULL ,
tt_koef varchar ( 50 ) NULL ,
last_measure_date date NULL ,
t_sum float8 NULL ,
t1 float8 NULL ,
t2 float8 NULL ,
t3 float8 NULL ,
book_num int4 NULL ,
abonent_num int4 NULL ,
contract_num varchar ( 100 ) NULL ,
plate_type varchar ( 50 ) NULL ,
aiiskue_id varchar ( 100 ) NULL ,
tariff int4 NULL ,
voltage varchar ( 50 ) NULL ,
subscriber_fio varchar ( 255 ) NULL ,
point_name varchar ( 255 ) NULL ,
ex_pu_type varchar ( 255 ) NULL ,
ex_pu_year_manuf int4 NULL ,
ex_pu_phase varchar ( 50 ) NOT NULL ,
pillar_type_id uuid NULL ,
line_type_id uuid NULL ,
input_type varchar ( 50 ) NULL ,
original_address varchar ( 1000 ) NULL ,
entrance int4 NULL ,
floor_ int4 NULL ,
flat_type varchar ( 50 ) NULL ,
customer_id varchar ( 255 ) NULL ,
subscriber_obj_name varchar ( 255 ) NULL ,
accounting_num varchar ( 100 ) NULL ,
task_source varchar ( 50 ) NULL ,
registration_num varchar ( 100 ) NULL ,
task_reason varchar ( 50 ) NULL ,
upload_date date NULL ,
customer_comment text NULL ,
tech_condition_num varchar ( 100 ) NULL ,
tp_contract_num varchar ( 100 ) NULL ,
maximal_power int4 NULL ,
energy_company_name varchar ( 255 ) NULL ,
client_office_name varchar ( 255 ) NULL ,
scheme_byt varchar ( 100 ) NULL ,
network_company_name varchar ( 255 ) NULL ,
network_filial varchar ( 255 ) NULL ,
ps varchar ( 150 ) NULL ,
ps_feeder varchar ( 150 ) NULL ,
tp_name varchar ( 150 ) NULL ,
tp_feeder varchar ( 150 ) NULL ,
pillar_num varchar ( 150 ) NULL ,
address_table_id uuid NULL ,
send_to_asumb_status varchar ( 50 ) NULL ,
territorial_office varchar ( 100 ) NULL ,
pillar_latitude float8 NULL ,
pillar_longitude float8 NULL ,
console_location varchar ( 50 ) NULL ,
ex_mac varchar ( 255 ) NULL ,
contractor_manager varchar ( 255 ) NULL ,
responsible_tel varchar ( 255 ) NULL ,
enforce_address varchar ( 255 ) NULL ,
territorial_branch_id int4 NULL ,
odpu_management_company_id int4 NULL ,
original_fias uuid NULL ,
electric_networks_enterprise uuid NULL ,
regional_electric_network_id uuid NULL ,
CONSTRAINT enerstroymain_task_info_pkey PRIMARY KEY ( id )
) ;
CREATE INDEX idx_abonentnum ON public . enerstroymain_task_info USING btree ( abonent_num ) ;
CREATE INDEX idx_accountingnum ON public . enerstroymain_task_info USING btree ( accounting_num ) ;
CREATE INDEX idx_appartment ON public . enerstroymain_task_info USING btree ( appartment ) ;
CREATE INDEX idx_booknum ON public . enerstroymain_task_info USING btree ( book_num ) ;
CREATE INDEX idx_enerstroymain_task_info_address_table ON public . enerstroymain_task_info USING btree ( address_table_id ) ;
CREATE INDEX idx_enerstroymain_task_info_electric_networks_enterprise ON public . enerstroymain_task_info USING btree ( electric_networks_enterprise ) ;
CREATE INDEX idx_enerstroymain_task_info_line_type ON public . enerstroymain_task_info USING btree ( line_type_id ) ;
CREATE INDEX idx_enerstroymain_task_info_odpu_management_company ON public . enerstroymain_task_info USING btree ( odpu_management_company_id ) ;
CREATE INDEX idx_enerstroymain_task_info_pillar_type ON public . enerstroymain_task_info USING btree ( pillar_type_id ) ;
CREATE INDEX idx_enerstroymain_task_info_regional_electric_network ON public . enerstroymain_task_info USING btree ( regional_electric_network_id ) ;
CREATE INDEX idx_enerstroymain_task_info_territorial_branch ON public . enerstroymain_task_info USING btree ( territorial_branch_id ) ;
CREATE INDEX idx_original_address ON public . enerstroymain_task_info USING btree ( original_address ) ;
CREATE INDEX idx_task_info_update_ts ON public . enerstroymain_task_info USING btree ( update_ts ) ;
CREATE INDEX index_customer_id_task_ingo ON public . enerstroymain_task_info USING btree ( customer_id ) ;
-- public.enerstroymain_task_info foreign keys
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_address_table FOREIGN KEY ( address_table_id ) REFERENCES public . enerstroymain_address_table ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_electric_networks_enterprise FOREIGN KEY ( electric_networks_enterprise ) REFERENCES public . enerstroymain_energy_company ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_line_type FOREIGN KEY ( line_type_id ) REFERENCES public . enerstroymain_line_type_smr ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_odpu_management_company FOREIGN KEY ( odpu_management_company_id ) REFERENCES public . odpu_management_company ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_pillar_type FOREIGN KEY ( pillar_type_id ) REFERENCES public . enerstroymain_pillar_type ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_regional_electric_network FOREIGN KEY ( regional_electric_network_id ) REFERENCES public . enerstroymain_energy_company ( id ) ;
ALTER TABLE public . enerstroymain_task_info ADD CONSTRAINT fk_enerstroymain_task_info_territorial_branch FOREIGN KEY ( territorial_branch_id ) REFERENCES public . territorial_branch ( id ) ;
--
Марат Гасанян
Отправлено из Почты Mail
Attachments:
Rows.xlsx (application/vnd.openxmlformats-officedocument.spreadsheetml.sheet, 12.0K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: marat_gasanyan@mail.ru, pgsql-bugs@lists.postgresql.org
Subject: Re: Problem with constraint unique.
In-Reply-To: <1733928443.950047988@f125.i.mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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