BUG #17361: Unique index constraint inconsistence

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).

Responses

Browse pgsql-bugs by date

  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