Thread: BUG #17220: ALTER INDEX ALTER COLUMN SET (..) with an optionless opclass makes index and table unusable
BUG #17220: ALTER INDEX ALTER COLUMN SET (..) with an optionless opclass makes index and table unusable
From
PG Bug reporting form
Date:
The following bug has been logged on the website:
Bug reference: 17220
Logged by: Matthijs van der Vleuten
Email address: postgresql@zr40.nl
PostgreSQL version: 14.0
Operating system: Debian sid
Description:
User 'musttu' on IRC reported the following bug: After running "ALTER INDEX
some_idx ALTER COLUMN expr SET (n_distinct=100)", the index and table become
unusable. All further statements involving the table result in: "ERROR:
operator class text_ops has no options".
They reported this on the RDS version of 13.3, but I've been able to
reproduce this on Debian with 13.4 and 14.0. It does not reproduce on 12.8,
all statements succeed on that version.
As a workaround, I've suggested the following catalog change in order to be
able to drop the index:
UPDATE pg_attribute SET attoptions = NULL WHERE attrelid =
'tbl_col_idx'::regclass;
However, they were not able to do this, since RDS does not expose a true
superuser.
Reproduction:
zr40@[local]:5432 ~=# select version();
version
──────────────────────────────────────────────────────────────────────────────────────────────────────────────────
PostgreSQL 14.0 (Debian 14.0-1.pgdg+1) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 10.3.0-11) 10.3.0, 64-bit
(1 row)
zr40@[local]:5432 ~=# create table test (col text);
CREATE TABLE
zr40@[local]:5432 ~=# create index on test (col);
CREATE INDEX
zr40@[local]:5432 ~=# alter index test_col_idx alter column col set
(n_distinct=100);
ALTER INDEX
zr40@[local]:5432 ~=# alter index test_col_idx alter column col reset
(n_distinct);
ERROR: 22023: operator class text_ops has no options
LOCATION: index_opclass_options, indexam.c:971
zr40@[local]:5432 ~=# drop index test_col_idx;
ERROR: 22023: operator class text_ops has no options
LOCATION: index_opclass_options, indexam.c:971
zr40@[local]:5432 ~=# drop table test;
ERROR: 22023: operator class text_ops has no options
LOCATION: index_opclass_options, indexam.c:971
Re: BUG #17220: ALTER INDEX ALTER COLUMN SET (..) with an optionless opclass makes index and table unusable
From
Vik Fearing
Date:
On 10/11/21 5:25 PM, PG Bug reporting form wrote: > > User 'musttu' on IRC reported the following bug: After running "ALTER INDEX > some_idx ALTER COLUMN expr SET (n_distinct=100)", the index and table become > unusable. All further statements involving the table result in: "ERROR: > operator class text_ops has no options". > > They reported this on the RDS version of 13.3, but I've been able to > reproduce this on Debian with 13.4 and 14.0. It does not reproduce on 12.8, > all statements succeed on that version. This was broken by 911e702077 (Implement operator class parameters). -- Vik Fearing