Fix error message when trying to alter statistics on included column

Started by Yugo Nagataover 7 years ago5 messages

nagata@sraoss.co.jp

over 7 years ago

1 attachment(s)

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 <nagata@sraoss.co.jp>

Attachments:

set_statistics_error_message.patchtext/x-diff; name=set_statistics_error_message.patchDownload

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;

Robert Haas

robertmhaas@gmail.com

over 7 years ago

In reply to: Yugo Nagata (#1)

Re: Fix error message when trying to alter statistics on included column

On Thu, Jun 28, 2018 at 5:28 AM, Yugo Nagata <nagata@sraoss.co.jp> 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

Yugo Nagata

nagata@sraoss.co.jp

over 7 years ago

In reply to: Robert Haas (#2)

Re: Fix error message when trying to alter statistics on included column

On Mon, 2 Jul 2018 14:23:09 -0400
Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Jun 28, 2018 at 5:28 AM, Yugo Nagata <nagata@sraoss.co.jp> 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 <nagata@sraoss.co.jp>

Andres Freund

andres@anarazel.de

over 7 years ago

In reply to: Yugo Nagata (#1)

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

Alvaro Herrera

alvherre@2ndquadrant.com

over 7 years ago

In reply to: Yugo Nagata (#1)

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 Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services