Обсуждение: BUG #17361: Unique index constraint inconsistence
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).
PG Bug reporting form <noreply@postgresql.org> writes: > 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. How long have you had this database? If it's older than your last OS upgrade, it's possible that the index has become corrupt as a result of OS-level collation changes. See https://wiki.postgresql.org/wiki/Locale_data_changes regards, tom lane
Thank you very much. It looks like this is exactly my problem.
My database is very old, and was created many years ago.
OS upgrades were twice ubuntu 16 -> 18 -> 20.
OS upgrades were twice ubuntu 16 -> 18 -> 20.
Database also upgraded twice 9.x -> 10.x -> 12.x.
If I understand correctly, will the 'REINEX SYSTEM' command help to avoid such problems in the future OS upgrades?
If I understand correctly, will the 'REINEX SYSTEM' command help to avoid such problems in the future OS upgrades?
вт, 11 янв. 2022 г. в 03:08, Tom Lane <tgl@sss.pgh.pa.us>:
PG Bug reporting form <noreply@postgresql.org> writes:
> 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.
How long have you had this database? If it's older than your last
OS upgrade, it's possible that the index has become corrupt as a
result of OS-level collation changes. See
https://wiki.postgresql.org/wiki/Locale_data_changes
regards, tom lane
On Tue, Jan 11, 2022 at 3:15 AM Роман Григорович <atzedus@gmail.com> wrote: > If I understand correctly, will the 'REINEX SYSTEM' command help to avoid such problems in the future OS upgrades? No. It will only fix the problem after the fact. The only way to prevent the problem is to make sure that your libc/OS collations never change (except perhaps in a very controlled way). OS upgrades are typically involved in cases where a change in the behavior of a collation leads to index corruption. If you want to do an OS upgrade (and cannot do an online upgrade using logical replication), then you must account for the possibility that the underlying collations will change. I suggest that you use contrib/pageinspect to find any B-Tree indexes that have problems. It's a contrib extension, so you must first run "CREATE EXTENSION amcheck;". From there, you can run a query like the following (you may want to customize this): SELECT bt_index_check(index => c.oid, heapallindexed => true), c.relname, c.relpages FROM pg_index i JOIN pg_opclass op ON i.indclass[0] = op.oid JOIN pg_am am ON op.opcmethod = am.oid JOIN pg_class c ON i.indexrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE am.amname = 'btree' -- Don't check temp tables, which may be from another session: AND c.relpersistence != 't' -- Function may throw an error when this is omitted: AND c.relkind = 'i' AND i.indisready AND i.indisvalid ORDER BY c.relpages DESC; The query will throw errors when it finds corruption. That should give you a general idea of the extent of the problem. Maybe the only corruption is the corruption that you know about already, but it's more likely that other indexes are also affected. If this query takes too long to complete you may find it useful to add something to limit the indexes check, such as: AND n.nspname = 'public' -- that change to the SQL will make the query just test indexes from the public schema. Do "SET client_min_messages=DEBUG1 " to get a kind of rudimentary progress indicator, if that seems useful to you. The docs have further information on what this bt_index_check function does, should you need it: https://www.postgresql.org/docs/12/amcheck.html -- Peter Geoghegan