Thread: Fix error message when trying to alter statistics on includedcolumn
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>
Attachment
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
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>
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
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