From: Bossart, Nathan <bossartn@amazon.com>
To: Vik Fearing <vik@postgresfriends.org>
To: postgresql@zr40.nl <postgresql@zr40.nl>
To: Alexander Korotkov <aekorotkov@gmail.com>
Cc: pgsql-hackers@postgresql.org <pgsql-hackers@postgresql.org>
Subject: Re: BUG #17220: ALTER INDEX ALTER COLUMN SET (..) with an optionless opclass makes index and table unusable
Date: Wed, 13 Oct 2021 00:06:32 +0000
Message-ID: <E29EF33B-CA1A-4146-BFCF-C8EC08FB91A8@amazon.com> (raw)
In-Reply-To: <9b91d241-4d05-e5a4-67da-5b8acdb9a2f1@postgresfriends.org>
References: <17220-15d684c6c2171a83@postgresql.org>
<9b91d241-4d05-e5a4-67da-5b8acdb9a2f1@postgresfriends.org>
On 10/11/21, 11:03 AM, "Vik Fearing" <vik@postgresfriends.org> wrote:
> 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).
Moving to pgsql-hackers@.
At first glance, it looks like ALTER INDEX .. ALTER COLUMN ... SET
uses the wrong validation function. I've attached a patch where I've
attempted to fix that and added some tests.
Nathan
Attachments:alter_index_column_set_validation.patch (application/octet-stream, 4.1K) downloaddiff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 1c2ebe1bf6..f68e86467b 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -8066,7 +8066,11 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
castNode(List, options), NULL, NULL,
false, isReset);
/* Validate new options */
- (void) attribute_reloptions(newOptions, true);+ if (rel->rd_rel->relkind == RELKIND_INDEX ||+ rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)+ (void) index_opclass_options(rel, attnum, newOptions, true);+ else+ (void) attribute_reloptions(newOptions, true);
/* Build new tuple. */
memset(repl_null, false, sizeof(repl_null));
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.outindex bc113a70b4..fc5f8b5fb1 100644--- a/src/test/regress/expected/btree_index.out+++ b/src/test/regress/expected/btree_index.out@@ -329,3 +329,6 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
ERROR: operator class int4_ops has no options
+create index on btree_tall_tbl (id);+alter index btree_tall_tbl_id_idx alter column id set (ndistinct=100);+ERROR: operator class int4_ops has no optionsdiff --git a/src/test/regress/expected/tsearch.out b/src/test/regress/expected/tsearch.outindex 45b92a6338..48561d4853 100644--- a/src/test/regress/expected/tsearch.out+++ b/src/test/regress/expected/tsearch.out@@ -513,6 +513,21 @@ SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D';
(1 row)
-- Test siglen parameter of GiST tsvector_ops
+CREATE INDEX wowidx1 ON test_tsvector USING gist (a);+ALTER INDEX wowidx1 ALTER COLUMN a SET (foo=1);+ERROR: unrecognized parameter "foo"+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=0);+ERROR: value 0 out of bounds for option "siglen"+DETAIL: Valid values are between "1" and "2024".+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=2048);+ERROR: value 2048 out of bounds for option "siglen"+DETAIL: Valid values are between "1" and "2024".+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=100,foo='bar');+ERROR: unrecognized parameter "foo"+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=100, siglen = 200);+ERROR: parameter "siglen" specified more than once+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=1);+DROP INDEX wowidx1;
CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(foo=1));
ERROR: unrecognized parameter "foo"
CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=0));
diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sqlindex c60312db2d..7552c39a01 100644--- a/src/test/regress/sql/btree_index.sql+++ b/src/test/regress/sql/btree_index.sql@@ -172,3 +172,5 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i;
-- Test unsupported btree opclass parameters
create index on btree_tall_tbl (id int4_ops(foo=1));
+create index on btree_tall_tbl (id);+alter index btree_tall_tbl_id_idx alter column id set (ndistinct=100);diff --git a/src/test/regress/sql/tsearch.sql b/src/test/regress/sql/tsearch.sqlindex d929210998..96998fdcf6 100644--- a/src/test/regress/sql/tsearch.sql+++ b/src/test/regress/sql/tsearch.sql@@ -130,6 +130,15 @@ SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:A';
SELECT count(*) FROM test_tsvector WHERE a @@ '!wd:D';
-- Test siglen parameter of GiST tsvector_ops
+CREATE INDEX wowidx1 ON test_tsvector USING gist (a);+ALTER INDEX wowidx1 ALTER COLUMN a SET (foo=1);+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=0);+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=2048);+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=100,foo='bar');+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=100, siglen = 200);+ALTER INDEX wowidx1 ALTER COLUMN a SET (siglen=1);+DROP INDEX wowidx1;+
CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(foo=1));
CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=0));
CREATE INDEX wowidx1 ON test_tsvector USING gist (a tsvector_ops(siglen=2048));
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: bossartn@amazon.com, vik@postgresfriends.org, postgresql@zr40.nl, aekorotkov@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: BUG #17220: ALTER INDEX ALTER COLUMN SET (..) with an optionless opclass makes index and table unusable
In-Reply-To: <E29EF33B-CA1A-4146-BFCF-C8EC08FB91A8@amazon.com>
* 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