From: Bossart, Nathan <bossartn@amazon.com>
To: Dilip Kumar <dilipbalaut@gmail.com>
To: Michael Paquier <michael@paquier.xyz>
Cc: Vik Fearing <vik@postgresfriends.org>
Cc: postgresql@zr40.nl <postgresql@zr40.nl>
Cc: 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 17:20:56 +0000
Message-ID: <678CF71A-934B-4FD0-BF63-E41D795E41C9@amazon.com> (raw)
In-Reply-To: <CAFiTN-t9JSHmCtOtB_28BmAXEantUC2=1OKV_irAPAoxnzPadA@mail.gmail.com>
References: <17220-15d684c6c2171a83@postgresql.org>
<9b91d241-4d05-e5a4-67da-5b8acdb9a2f1@postgresfriends.org>
<E29EF33B-CA1A-4146-BFCF-C8EC08FB91A8@amazon.com>
<YWZViGJFJkrgBKwk@paquier.xyz>
<CAFiTN-t9JSHmCtOtB_28BmAXEantUC2=1OKV_irAPAoxnzPadA@mail.gmail.com>
On 10/13/21, 1:31 AM, "Dilip Kumar" <dilipbalaut@gmail.com> wrote:
> On Wed, Oct 13, 2021 at 9:12 AM Michael Paquier <michael@paquier.xyz> wrote:
>> Anyway, specifying a column name for an ALTER INDEX is not right, no?
>> Just take for example the case of an expression which has a hardcoded
>> column name in pg_attribute. So these are not specific to indexes,
>> which is why we apply column numbers for the statistics case. I think
>> that we'd better just reject those cases until there is a proper
>> design done here. As far as I can see, I guess that we should do
>> things similarly to what we do for SET STATISTICS with column
>> numbers when it comes to indexes.
>
> +1 it should behave similarly to SET STATISTICS for the index and if
> someone tries to set with the column name then it should throw an
> error.
Good point. I agree that rejecting this case is probably the best
option for now. In addition to the bug mentioned in this thread, this
functionality doesn't even work for supported options currently.
postgres=> create table test (a tsvector);
CREATE TABLE
postgres=> create index on test using gist (a);
CREATE INDEX
postgres=> alter index test_a_idx alter column a set (siglen = 100);
ERROR: unrecognized parameter "siglen"
AFAICT the fact that these commands can succeed at all seems to be
unintentional, and I wonder if modifying these options requires extra
steps such as rebuilding the index.
I've attached new patch that just adds an ERROR.
Nathan
Attachments:disallow_alter_index_column_set.patch (application/octet-stream, 1.9K) downloaddiff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 1c2ebe1bf6..eb3c146f25 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -8041,6 +8041,12 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options,
bool repl_null[Natts_pg_attribute];
bool repl_repl[Natts_pg_attribute];
+ if (rel->rd_rel->relkind == RELKIND_INDEX ||+ rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot alter options for index column")));+
attrelation = table_open(AttributeRelationId, RowExclusiveLock);
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.outindex bc113a70b4..f80303ae3d 100644--- a/src/test/regress/expected/btree_index.out+++ b/src/test/regress/expected/btree_index.out@@ -329,3 +329,7 @@ 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 (n_distinct=100);+ERROR: cannot alter options for index column+drop index btree_tall_tbl_id_idx;diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sqlindex c60312db2d..3519d80696 100644--- a/src/test/regress/sql/btree_index.sql+++ b/src/test/regress/sql/btree_index.sql@@ -172,3 +172,7 @@ 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 (n_distinct=100);+drop index btree_tall_tbl_id_idx;
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, dilipbalaut@gmail.com, michael@paquier.xyz, 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: <678CF71A-934B-4FD0-BF63-E41D795E41C9@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