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

On 2018/12/20 10:47, Tatsuro Yamada wrote:
> On 2018/12/20 10:38, Michael Paquier wrote:
>> On Thu, Dec 20, 2018 at 10:05:30AM +0900, Tatsuro Yamada wrote:
>>> Alright, I'll create new patches including these:
>>>
>>>    - No completion after "ALTER TABLE/INDEX SET STATISTICS" instead of schema names
>>>    - Complete "ALTER INDEX foo ALTER COLUMN SET" with STATISTICS by
>>>    using *column_numbers*
>>
>> Thanks for considering it!
>
> My pleasure, Neo. :)
> Please wait for new WIP patches.

Attached file is a WIP patch.

*Example of after patching
========================================================
create table hoge (a integer, b integer, c integer);
create index ind_hoge on hoge(a, b, c, (c*1), (c*2), (c*3), (c*4), (c*5), (c*6), (c*7), (c*8), (c*9));

# \d+ ind_hoge
Index "public.ind_hoge"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
a | integer | yes | a | plain |
b | integer | yes | b | plain |
c | integer | yes | c | plain |
expr | integer | yes | (c * 1) | plain |
expr1 | integer | yes | (c * 2) | plain |
expr2 | integer | yes | (c * 3) | plain |
expr3 | integer | yes | (c * 4) | plain |
expr4 | integer | yes | (c * 5) | plain |
expr5 | integer | yes | (c * 6) | plain |
expr6 | integer | yes | (c * 7) | plain |
expr7 | integer | yes | (c * 8) | plain |
expr8 | integer | yes | (c * 9) | plain |
btree, for table "public.hoge"

# alter index ind_hoge alter column <tab!>
1 10 11 12 2 3 4 5 6 7 8 9

# alter index ind_hoge alter column 1 <tab!>
1 10 11 12

# alter index ind_hoge alter column 10 SET STATISTICS <tab!>
<no completion!>

# alter index ind_hoge alter column 10 SET STATISTICS 100;
ALTER INDEX

# \d+ ind_hoge
Index "public.ind_hoge"
Column | Type | Key? | Definition | Storage | Stats target
--------+---------+------+------------+---------+--------------
a | integer | yes | a | plain |
b | integer | yes | b | plain |
c | integer | yes | c | plain |
expr | integer | yes | (c * 1) | plain |
expr1 | integer | yes | (c * 2) | plain |
expr2 | integer | yes | (c * 3) | plain |
expr3 | integer | yes | (c * 4) | plain |
expr4 | integer | yes | (c * 5) | plain |
expr5 | integer | yes | (c * 6) | plain |
expr6 | integer | yes | (c * 7) | plain | 100
expr7 | integer | yes | (c * 8) | plain |
expr8 | integer | yes | (c * 9) | plain |
btree, for table "public.hoge"
========================================================

As you know above completed 1, 2 and 3 are not expression columns,
so it might better to remove these from the completion.
However, I didn't do that because a query for getting more suitable
attnum of index are became complicated.

Then, the patch includes new query to get attribute_numbers like this:

========================================================
+#define Query_for_list_of_attribute_numbers \
+"SELECT attnum "\
+" FROM pg_catalog.pg_attribute a, "\
+" pg_catalog.pg_class c "\
+" WHERE c.oid = a.attrelid "\
+" AND a.attnum > 0 "\
+" AND NOT a.attisdropped "\
+" /* %d %s */" \
+" AND a.attrelid = (select oid from pg_catalog.pg_class where relname = '%s') "\
+" AND pg_catalog.pg_table_is_visible(c.oid) "\
+"order by a.attnum asc "
========================================================

I have a question.
I read following comment of _complete_from_query(), however I'm not sure whether "%d" is
needed or not in above query. Any advices welcome!

========================================================
* 1. A simple query which must contain a %d and a %s, which will be replaced
* by the string length of the text and the text itself. The query may also
* have up to four more %s in it; the first two such will be replaced by the
* value of completion_info_charp, the next two by the value of
* completion_info_charp2.
========================================================

Thanks,
Tatsuro Yamada