Subject: Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS
References: <4cf42ab8-1c67-90f8-1f8b-b9f9a53d11f6@lab.ntt.co.jp>
 <d677594b-101a-6236-7774-94a7c1a7b56b@lab.ntt.co.jp>
 <20181128.131428.84607836.horiguchi.kyotaro@lab.ntt.co.jp>
From: Tatsuro Yamada <yamada.tatsuro@lab.ntt.co.jp>
Message-ID: <54bd214b-d0d3-8654-e71f-45e7b4f979f0@lab.ntt.co.jp>
Date: Wed, 28 Nov 2018 14:41:40 +0900
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.0
MIME-Version: 1.0
In-Reply-To: <20181128.131428.84607836.horiguchi.kyotaro@lab.ntt.co.jp>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
Cc: pgsql-hackers@lists.postgresql.org
Precedence: bulk

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