Re: Tab completion for ALTER INDEX|TABLE ALTER COLUMN SET STATISTICS - Mailing list pgsql-hackers

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