Horton

Thread

Commits

  1. Fix ALTER TABLE...SET STATS error message for included columns

  1. Fix error message when trying to alter statistics on included column

    Yugo Nagata <nagata@sraoss.co.jp> — 2018-06-28T09:28:03Z

    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>

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

    Robert Haas <robertmhaas@gmail.com> — 2018-07-02T18:23:09Z

    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

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

    Yugo Nagata <nagata@sraoss.co.jp> — 2018-07-06T07:09:28Z

    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>

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

    Andres Freund <andres@anarazel.de> — 2018-07-12T16:53:58Z

    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

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

    Alvaro Herrera <alvherre@2ndquadrant.com> — 2018-07-17T00:04:34Z

    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