Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SETSTATISTICS

Hello.

At Wed, 28 Nov 2018 14:41:40 +0900, Tatsuro Yamada <yamada.tatsuro@lab.ntt.co.jp> wrote in
<54bd214b-d0d3-8654-e71f-45e7b4f979f0@lab.ntt.co.jp>
> On 2018/11/28 13:14, Kyotaro HORIGUCHI wrote:
> > Hello.
> > At Wed, 28 Nov 2018 11:27:23 +0900, Tatsuro Yamada
> > <yamada.tatsuro@lab.ntt.co.jp> wrote in
> > <d677594b-101a-6236-7774-94a7c1a7b56b@lab.ntt.co.jp>
> >> Hi,
> >>
> >> On 2018/11/26 11:05, Tatsuro Yamada wrote:
> >> I couldn't write patches details on previous email, so I write
> >> more explanation for that on this email.
> >>
> >>
> >> * tab_completion_alter_index_set_statistics.patch
> >> =======
> >> There are two problems. You can use these DDL before testing.
> >>    #create table hoge (a integer, b integer);
> >>    #create index ind_hoge on hoge (a, (a + b), (a * b));
> >>
> >>    1) Can't get column names
> >>
> >>      # alter index ind_hoge alter column <tab!><tab!>... but can't
> >>      # complete.
> > Currently the only continueable rule to the rule is SET
> > STATISTICS so we usually expect the number of an expression
> > column there. Even though we actually name every expression
> > column in an index, users hardly see the names. The names are in
> > the index column number order in your example, but what if the
> > name of the first column were 'foo'?
> > =# alter index ind_hoge2 alter column
> > expr   expr1  foo
> > We could still *guess* what is expr or exrp1 but I don't think it
> > helps much. (Note: foo is not usable in this context as it's a
> > non-expression column.)
> 
> Thanks for your comment.
> We can get column name by using "\d index_name" like this:
> 
> # \d ind_hoge
>        Index "public.ind_hoge"
>  Column |  Type   | Key? | Definition
> --------+---------+------+------------
>  a      | integer | yes  | a
>  expr   | integer | yes  | (a + b)
>  expr1  | integer | yes  | (a * b)
> btree, for table "public.hoge"
> 
> So, I suppose that it's easy to understand what column is an
> expression column.

Yeah, actually we can find them there, but I don't think it's
popular. I suppose that most of people are unconcious of the
names since they didn't named them.  Moreover what makes me
uneasy with this is that it doesn't suggest attribute numbers,
which are firstly expected there. But anyway it is impossible
with readline since suggestions are sorted as strings. ("1",
"11", "12", "2" order, I don't think indexes often have that many
columns, though.)

If \d <table> showed the names of index columns, the completion
would be more convinsing. But I'm not sure it is useful..

\d hoge
...
Indexes:
    "ind_hoge" btree (a, (a + b) as expr, (a * b) as expr1)

By the way, for index expressions consists of just one function,
suggestions looks much sainer.

> Of course, user will get syntax error if user chose "a" column like a
> "foo" which is
> non-expression column as you mentioned.
> Probably, I will be able to fix the patch to get only expression
> columns from the index.
> Should I do that?

Nope. That's just too-much. We are already showing unusable
suggestions in other places, for example, exiting tables for
CREATE TABLE. (It is useful for me as it suggests what should be
placed there.)

> Other example, if user wants to use column number, I suppose that user
> have to check a
> definition of index and count the number of columns.
> ====
> # create table hoge2(a integer, b integer, foo integer);
> CREATE TABLE
> 
> # create index ind_hoge2 on hoge2((a+b), foo, (a*b));
> CREATE INDEX
> [local] postgres@postgres:9912=# \d ind_hoge2
>        Index "public.ind_hoge2"
>  Column |  Type   | Key? | Definition
> --------+---------+------+------------
>  expr   | integer | yes  | (a + b)
>  foo    | integer | yes  | foo
>  expr1  | integer | yes  | (a * b)
> btree, for table "public.hoge2"
> 
> # alter index ind_hoge2 alter column 1 set statistics 1;
> ALTER INDEX
> 
> # alter index ind_hoge2 alter column 2 set statistics 1;
> ERROR: cannot alter statistics on non-expression column "foo" of index
> "ind_hoge2"
> 
> # alter index ind_hoge2 alter column 3 set statistics 1;
> ALTER INDEX
> ====
> 
> I prefer to use column name instead column number because
> there is no column number on \d index_name and \d+ index_name.

Some people prefer names even if they are implicitly
given. Others (including myself) prefer numbers.

> >>    2) I expected column names for column numbers after "SET STATISTICS",
> >>    but
> >>       tab-completion gave schema names
> >>
> >>      # alter index ind_hoge alter column expr SET STATISTICS <tab!>
> >>      information_schema.  pg_catalog.  pg_temp_1.  pg_toast.
> >>      pg_toast_temp_1.  public.
> > This is the result of STATISTICS <things> completion. SET
> > STATISTICS always doesn't take statistics name so this is safe.
> 
> :)

So I think it is reasonable.

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center