Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS

On 2018/11/28 13:14, Kyotaro HORIGUCHI wrote:
> Hello.
>
> At Wed, 28 Nov 2018 11:27:23 +0900, Tatsuro Yamada <yamada(dot)tatsuro(at)lab(dot)ntt(dot)co(dot)jp> wrote in <d677594b-101a-6236-7774-94a7c1a7b56b(at)lab(dot)ntt(dot)co(dot)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.
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?

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(at)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.

>> 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.

:)

Thanks,
Tatsuro Yamada
NTT Open Source Software Center