From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | atzedus(at)gmail(dot)com |
Subject: | BUG #17361: Unique index constraint inconsistence |
Date: | 2022-01-10 22:29:06 |
Message-ID: | 17361-912697ebdbbf37e9@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 17361
Logged by: Роман Григорович
Email address: atzedus(at)gmail(dot)com
PostgreSQL version: 12.7
Operating system: Ubuntu 12.7-0ubuntu0.20.04.1
Description:
I has a table in database with this structure, and this table have a unique
complex index named "parts_stock_stat_lower_stock_type_id_idx":
db=# \d parts_stock_stat
Table "public.parts_stock_stat"
Column | Type | Collation | Nullable | Default
---------------+---------+-----------+----------+----------------------------------
id | integer | | not null | generated by default as
identity
name | text | | not null |
qty | integer | | not null |
stock_type_id | integer | | not null |
Indexes:
"parts_stock_stat_pkey" PRIMARY KEY, btree (id)
"parts_stock_stat_lower_stock_type_id_idx" UNIQUE, btree (lower(name),
stock_type_id)
Following reindex command failed:
db=# reindex table parts_stock_stat;
ERROR: could not create unique index
"parts_stock_stat_lower_stock_type_id_idx"
DETAIL: Key (lower(name), stock_type_id)=(ha_hr/50, 4) is duplicated.
I was try to find problem rows with following:
db=# SELECT name::bytea, id, name, stock_type_id FROM parts_stock_stat WHERE
lower(name) = 'ha_hr/50' AND stock_type_id = 4;
name | id | name | stock_type_id
--------------------+-------+----------+---------------
\x48415f48522f3530 | 12442 | HA_HR/50 | 4
\x48415f48522f3530 | 14052 | HA_HR/50 | 4
(2 rows)
As a result it found 2 rows that is truly duplicates.
The unique index existed before the table was populated with data.
But how it can be possible with unique index described above?
Is this a bug? If needed, i can upload full binary copy of database
(~136Mb).
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2022-01-11 00:08:24 | Re: BUG #17361: Unique index constraint inconsistence |
Previous Message | Peter Geoghegan | 2022-01-10 19:31:58 | Re: BUG #17255: Server crashes in index_delete_sort_cmp() due to race condition with vacuum |