From fb5710f092da6ebc0c1ebd6b9a2a67d3cc5717d1 Mon Sep 17 00:00:00 2001 From: Michael Paquier Date: Fri, 29 Oct 2021 13:54:07 +0900 Subject: [PATCH v1] Add support for ALTER INDEX ALTER COLUMN {SET,RESET} --- src/backend/commands/tablecmds.c | 56 +++++++++++++++++------ src/backend/parser/gram.y | 32 +++++++++++++ src/test/regress/expected/btree_index.out | 9 ++-- src/test/regress/expected/tsearch.out | 22 +++++++++ src/test/regress/sql/btree_index.sql | 3 +- src/test/regress/sql/tsearch.sql | 8 ++++ doc/src/sgml/indices.sgml | 2 + doc/src/sgml/ref/alter_index.sgml | 25 ++++++++++ 8 files changed, 138 insertions(+), 19 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 857cc5ce6e..0111d6d0eb 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -447,7 +447,8 @@ static ObjectAddress ATExecDropExpression(Relation rel, const char *colName, boo static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, int16 colNum, Node *newValue, LOCKMODE lockmode); static ObjectAddress ATExecSetOptions(Relation rel, const char *colName, - Node *options, bool isReset, LOCKMODE lockmode); + int16 colNum, Node *options, + bool isReset, LOCKMODE lockmode); static ObjectAddress ATExecSetStorage(Relation rel, const char *colName, Node *newValue, LOCKMODE lockmode); static void ATPrepDropColumn(List **wqueue, Relation rel, bool recurse, bool recursing, @@ -4528,7 +4529,7 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ - ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW | ATT_FOREIGN_TABLE); + ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_MATVIEW | ATT_INDEX | ATT_PARTITIONED_INDEX | ATT_FOREIGN_TABLE); /* This command never recurses */ pass = AT_PASS_MISC; break; @@ -4909,10 +4910,10 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, address = ATExecSetStatistics(rel, cmd->name, cmd->num, cmd->def, lockmode); break; case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ - address = ATExecSetOptions(rel, cmd->name, cmd->def, false, lockmode); + address = ATExecSetOptions(rel, cmd->name, cmd->num, cmd->def, false, lockmode); break; case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ - address = ATExecSetOptions(rel, cmd->name, cmd->def, true, lockmode); + address = ATExecSetOptions(rel, cmd->name, cmd->num, cmd->def, true, lockmode); break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ address = ATExecSetStorage(rel, cmd->name, cmd->def, lockmode); @@ -8059,8 +8060,8 @@ ATExecSetStatistics(Relation rel, const char *colName, int16 colNum, Node *newVa * Return value is the address of the modified column */ static ObjectAddress -ATExecSetOptions(Relation rel, const char *colName, Node *options, - bool isReset, LOCKMODE lockmode) +ATExecSetOptions(Relation rel, const char *colName, int16 colNum, + Node *options, bool isReset, LOCKMODE lockmode) { Relation attrelation; HeapTuple tuple, @@ -8075,15 +8076,40 @@ ATExecSetOptions(Relation rel, const char *colName, Node *options, bool repl_null[Natts_pg_attribute]; bool repl_repl[Natts_pg_attribute]; + /* + * We allow referencing columns by numbers only for indexes, since table + * column numbers could contain gaps if columns are later dropped. + */ + if (rel->rd_rel->relkind != RELKIND_INDEX && + rel->rd_rel->relkind != RELKIND_PARTITIONED_INDEX && + !colName) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot refer to non-index column by number"))); + attrelation = table_open(AttributeRelationId, RowExclusiveLock); - tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName); + if (colName) + { + tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName); + + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + } + else + { + tuple = SearchSysCacheAttNum(RelationGetRelid(rel), colNum); + + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column number %d of relation \"%s\" does not exist", + colNum, RelationGetRelationName(rel)))); + } - if (!HeapTupleIsValid(tuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_COLUMN), - errmsg("column \"%s\" of relation \"%s\" does not exist", - colName, RelationGetRelationName(rel)))); attrtuple = (Form_pg_attribute) GETSTRUCT(tuple); attnum = attrtuple->attnum; @@ -8100,7 +8126,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/backend/parser/gram.y b/src/backend/parser/gram.y index d0eb80e69c..0122862aeb 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -2290,6 +2290,22 @@ alter_table_cmd: n->def = (Node *) $5; $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] SET ( column_parameter = value [, ... ] */ + | ALTER opt_column Iconst SET reloptions + { + AlterTableCmd *n = makeNode(AlterTableCmd); + + if ($3 <= 0 || $3 > PG_INT16_MAX) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("column number must be in range from 1 to %d", PG_INT16_MAX), + parser_errposition(@3))); + + n->subtype = AT_SetOptions; + n->num = (int16) $3; + n->def = (Node *) $5; + $$ = (Node *)n; + } /* ALTER TABLE ALTER [COLUMN] RESET ( column_parameter [, ... ] ) */ | ALTER opt_column ColId RESET reloptions { @@ -2299,6 +2315,22 @@ alter_table_cmd: n->def = (Node *) $5; $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] RESET ( column_parameter = value [, ... ] */ + | ALTER opt_column Iconst RESET reloptions + { + AlterTableCmd *n = makeNode(AlterTableCmd); + + if ($3 <= 0 || $3 > PG_INT16_MAX) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("column number must be in range from 1 to %d", PG_INT16_MAX), + parser_errposition(@3))); + + n->subtype = AT_ResetOptions; + n->num = (int16) $3; + n->def = (Node *) $5; + $$ = (Node *)n; + } /* ALTER TABLE ALTER [COLUMN] SET STORAGE */ | ALTER opt_column ColId SET STORAGE ColId { diff --git a/src/test/regress/expected/btree_index.out b/src/test/regress/expected/btree_index.out index c43e023716..716583c830 100644 --- a/src/test/regress/expected/btree_index.out +++ b/src/test/regress/expected/btree_index.out @@ -330,16 +330,15 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i; create index on btree_tall_tbl (id int4_ops(foo=1)); ERROR: operator class int4_ops has no options -- Test case of ALTER INDEX with abuse of column names for indexes. --- This grammar is not officially supported, but the parser allows it. +-- This grammar is allowed by the parser, but table parameters are not +-- supported by indexes. CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id); ALTER INDEX btree_tall_idx2 ALTER COLUMN id SET (n_distinct=100); -ERROR: ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_tall_idx2" -DETAIL: This operation is not supported for indexes. +ERROR: operator class int4_ops has no options DROP INDEX btree_tall_idx2; -- Partitioned index CREATE TABLE btree_part (id int4) PARTITION BY RANGE (id); CREATE INDEX btree_part_idx ON btree_part(id); ALTER INDEX btree_part_idx ALTER COLUMN id SET (n_distinct=100); -ERROR: ALTER action ALTER COLUMN ... SET cannot be performed on relation "btree_part_idx" -DETAIL: This operation is not supported for partitioned indexes. +ERROR: operator class int4_ops has no options DROP TABLE btree_part; diff --git a/src/test/regress/expected/tsearch.out b/src/test/regress/expected/tsearch.out index 45b92a6338..e09c7627de 100644 --- a/src/test/regress/expected/tsearch.out +++ b/src/test/regress/expected/tsearch.out @@ -536,6 +536,28 @@ Indexes: "wowidx" gist (a) "wowidx2" gist (a tsvector_ops (siglen='1')) +ALTER INDEX wowidx2 ALTER COLUMN 1 RESET (siglen); +\d test_tsvector + Table "public.test_tsvector" + Column | Type | Collation | Nullable | Default +--------+----------+-----------+----------+--------- + t | text | | | + a | tsvector | | | +Indexes: + "wowidx" gist (a) + "wowidx2" gist (a) + +ALTER INDEX wowidx2 ALTER COLUMN 1 SET (siglen=2); +\d test_tsvector + Table "public.test_tsvector" + Column | Type | Collation | Nullable | Default +--------+----------+-----------+----------+--------- + t | text | | | + a | tsvector | | | +Indexes: + "wowidx" gist (a) + "wowidx2" gist (a tsvector_ops (siglen='2')) + DROP INDEX wowidx; EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; QUERY PLAN diff --git a/src/test/regress/sql/btree_index.sql b/src/test/regress/sql/btree_index.sql index c34502249f..8fd49dfc0d 100644 --- a/src/test/regress/sql/btree_index.sql +++ b/src/test/regress/sql/btree_index.sql @@ -174,7 +174,8 @@ INSERT INTO delete_test_table SELECT i, 1, 2, 3 FROM generate_series(1,1000) i; create index on btree_tall_tbl (id int4_ops(foo=1)); -- Test case of ALTER INDEX with abuse of column names for indexes. --- This grammar is not officially supported, but the parser allows it. +-- This grammar is allowed by the parser, but table parameters are not +-- supported by indexes. CREATE INDEX btree_tall_idx2 ON btree_tall_tbl (id); ALTER INDEX btree_tall_idx2 ALTER COLUMN id SET (n_distinct=100); DROP INDEX btree_tall_idx2; diff --git a/src/test/regress/sql/tsearch.sql b/src/test/regress/sql/tsearch.sql index d929210998..7fdc2bb06c 100644 --- a/src/test/regress/sql/tsearch.sql +++ b/src/test/regress/sql/tsearch.sql @@ -140,6 +140,14 @@ CREATE INDEX wowidx2 ON test_tsvector USING gist (a tsvector_ops(siglen=1)); \d test_tsvector +ALTER INDEX wowidx2 ALTER COLUMN 1 RESET (siglen); + +\d test_tsvector + +ALTER INDEX wowidx2 ALTER COLUMN 1 SET (siglen=2); + +\d test_tsvector + DROP INDEX wowidx; EXPLAIN (costs off) SELECT count(*) FROM test_tsvector WHERE a @@ 'wr|qh'; diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml index 56fbd45178..9a8ec977ac 100644 --- a/doc/src/sgml/indices.sgml +++ b/doc/src/sgml/indices.sgml @@ -1316,6 +1316,8 @@ SELECT target FROM tests WHERE subject = 'some-subject' AND success; class for each column of an index. CREATE INDEX name ON table (column opclass [ ( opclass_options ) ] sort options , ...); +ALTER INDEX name ALTER [ COLUMN ] column_number SET ( opclass_parameter = value , ... ) +ALTER INDEX name ALTER [ COLUMN ] column_number RESET ( opclass_parameter , ... ) The operator class identifies the operators to be used by the index for that column. For example, a B-tree index on the type int4 diff --git a/doc/src/sgml/ref/alter_index.sgml b/doc/src/sgml/ref/alter_index.sgml index e26efec064..ddab7bee8f 100644 --- a/doc/src/sgml/ref/alter_index.sgml +++ b/doc/src/sgml/ref/alter_index.sgml @@ -27,6 +27,8 @@ ALTER INDEX name ATTACH PARTITION < ALTER INDEX name [ NO ] DEPENDS ON EXTENSION extension_name ALTER INDEX [ IF EXISTS ] name SET ( storage_parameter [= value] [, ... ] ) ALTER INDEX [ IF EXISTS ] name RESET ( storage_parameter [, ... ] ) +ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number SET ( opclass_parameter = value [, ... ] ) +ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number RESET ( opclass_parameter [, ... ] ) ALTER INDEX [ IF EXISTS ] name ALTER [ COLUMN ] column_number SET STATISTICS integer ALTER INDEX ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ] @@ -139,6 +141,18 @@ ALTER INDEX ALL IN TABLESPACE name + + ALTER [ COLUMN ] column_number SET ( opclass_parameter = value [, ... ] ) + ALTER [ COLUMN ] column_number RESET ( opclass_parameter [, ... ] ) + + + This form sets or resets per-attribute operator class parameters. + Since expressions lack a unique name, we refer to them using the + ordinal number of the index column. + + + + ALTER [ COLUMN ] column_number SET STATISTICS integer @@ -207,6 +221,17 @@ ALTER INDEX ALL IN TABLESPACE name + + opclass_parameter + + + The name of an operator class parameter. See + and + for details. + + + + tablespace_name -- 2.33.0