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