Re: Fix error message when trying to alter statistics on included column
On 2018-Jun-28, Yugo Nagata wrote: > According to the error message, it is not allowed to alter statistics on > included column because this is "non-expression column". > > postgres=# create table test (i int, d int); > CREATE TABLE > postgres=# create index idx on test(i) include (d); > CREATE INDEX > postgres=# alter index idx alter column 2 set statistics 10; > ERROR: cannot alter statistics on non-expression column "d" of index "idx" > HINT: Alter statistics on table column instead. > > However, I think this should be forbidded in that this is not a key column > but a included column. Even if we decide to support expressions in included > columns in future, it is meaningless to do this because any statistics on > included column is never used by the planner. I agree with this reasoning, so I pushed this patch. Thanks! I added a couple of lines in the regress file for this feature also. Teodor, Alexander, now would be the time to express dissent :-) -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Re: Fix error message when trying to alter statistics on included column
Hi Alexander, Teodor, On 2018-06-28 18:28:03 +0900, Yugo Nagata wrote: > According to the error message, it is not allowed to alter statistics on > included column because this is "non-expression column". > > postgres=# create table test (i int, d int); > CREATE TABLE > postgres=# create index idx on test(i) include (d); > CREATE INDEX > postgres=# alter index idx alter column 2 set statistics 10; > ERROR: cannot alter statistics on non-expression column "d" of index "idx" > HINT: Alter statistics on table column instead. Is either of you going to take care of this one? IIRC Teodor committed the underlying patch, and Alexander wrote parts of it? Greetings, Andres Freund
Re: Fix error message when trying to alter statistics on included column
On Mon, 2 Jul 2018 14:23:09 -0400 Robert Haas wrote: > On Thu, Jun 28, 2018 at 5:28 AM, Yugo Nagata wrote: > > According to the error message, it is not allowed to alter statistics on > > included column because this is "non-expression column". > > > > postgres=# create table test (i int, d int); > > CREATE TABLE > > postgres=# create index idx on test(i) include (d); > > CREATE INDEX > > postgres=# alter index idx alter column 2 set statistics 10; > > ERROR: cannot alter statistics on non-expression column "d" of index "idx" > > HINT: Alter statistics on table column instead. > > > > However, I think this should be forbidded in that this is not a key column > > but a included column. Even if we decide to support expressions in included > > columns in future, it is meaningless to do this because any statistics on > > included column is never used by the planner. > > > > Attached is the patch to fix the error message. In this fix, column number > > is checked first. After applying this, the message is changed as below; > > > > postgres=# alter index idx alter column 2 set statistics 10; > > ERROR: cannot alter statistics on included column "d" of index "idx" > > I think you should add an open item for this. I was about to add this to the wiki, but someone has already done this. Thanks! https://wiki.postgresql.org/wiki/PostgreSQL_11_Open_Items > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company > -- Yugo Nagata
Re: Fix error message when trying to alter statistics on included column
On Thu, Jun 28, 2018 at 5:28 AM, Yugo Nagata wrote: > According to the error message, it is not allowed to alter statistics on > included column because this is "non-expression column". > > postgres=# create table test (i int, d int); > CREATE TABLE > postgres=# create index idx on test(i) include (d); > CREATE INDEX > postgres=# alter index idx alter column 2 set statistics 10; > ERROR: cannot alter statistics on non-expression column "d" of index "idx" > HINT: Alter statistics on table column instead. > > However, I think this should be forbidded in that this is not a key column > but a included column. Even if we decide to support expressions in included > columns in future, it is meaningless to do this because any statistics on > included column is never used by the planner. > > Attached is the patch to fix the error message. In this fix, column number > is checked first. After applying this, the message is changed as below; > > postgres=# alter index idx alter column 2 set statistics 10; > ERROR: cannot alter statistics on included column "d" of index "idx" I think you should add an open item for this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Fix error message when trying to alter statistics on included column
Hi,
According to the error message, it is not allowed to alter statistics on
included column because this is "non-expression column".
postgres=# create table test (i int, d int);
CREATE TABLE
postgres=# create index idx on test(i) include (d);
CREATE INDEX
postgres=# alter index idx alter column 2 set statistics 10;
ERROR: cannot alter statistics on non-expression column "d" of index "idx"
HINT: Alter statistics on table column instead.
However, I think this should be forbidded in that this is not a key column
but a included column. Even if we decide to support expressions in included
columns in future, it is meaningless to do this because any statistics on
included column is never used by the planner.
Attached is the patch to fix the error message. In this fix, column number
is checked first. After applying this, the message is changed as below;
postgres=# alter index idx alter column 2 set statistics 10;
ERROR: cannot alter statistics on included column "d" of index "idx"
Regards,
--
Yugo Nagata
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 7c0cf0d..4beb160 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -6504,14 +6504,21 @@ ATExecSetStatistics(Relation rel, const char *colName, int16 colNum, Node *newVa
errmsg("cannot alter system column \"%s\"",
colName)));
- if ((rel->rd_rel->relkind == RELKIND_INDEX ||
- rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX) &&
- rel->rd_index->indkey.values[attnum - 1] != 0)
- ereport(ERROR,
-(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
- errmsg("cannot alter statistics on non-expression column \"%s\" of index \"%s\"",
- NameStr(attrtuple->attname), RelationGetRelationName(rel)),
- errhint("Alter statistics on table column instead.")));
+ if (rel->rd_rel->relkind == RELKIND_INDEX ||
+ rel->rd_rel->relkind == RELKIND_PARTITIONED_INDEX)
+ {
+ if (attnum > rel->rd_index->indnkeyatts)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter statistics on included column \"%s\" of index \"%s\"",
+ NameStr(attrtuple->attname), RelationGetRelationName(rel;
+ else if (rel->rd_index->indkey.values[attnum - 1] != 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter statistics on non-expression column \"%s\" of index \"%s\"",
+ NameStr(attrtuple->attname), RelationGetRelationName(rel)),
+ errhint("Alter statistics on table column instead.")));
+ }
attrtuple->attstattarget = newtarget;
