From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: atzedus@gmail.com
Subject: BUG #17361: Unique index constraint inconsistence
Date: Mon, 10 Jan 2022 22:29:06 +0000
Message-ID: <17361-912697ebdbbf37e9@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17361
Logged by: Роман Григорович
Email address: atzedus@gmail.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).
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: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, atzedus@gmail.com
Subject: Re: BUG #17361: Unique index constraint inconsistence
In-Reply-To: <17361-912697ebdbbf37e9@postgresql.org>
* 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