Re: Improving psql autocompletion for SET LOCAL / SET SESSION

Re: Improving psql autocompletion for SET LOCAL / SET SESSION

Hi,

> I am thinking maybe we could split the cases as
>     /* Complete "SET LOCAL" */
>     else if (Matches("SET", "LOCAL"))
>         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
>                                           "TIME ZONE",
>                                           "SCHEMA",
>                                           "NAMES");
>     /* Complete "SET SESSION" */
>     else if (Matches("SET", "SESSION"))
>         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
>                                           "AUTHORIZATION",
>                                           "CHARACTERISTICS AS TRANSACTION",
>                                           "TIME ZONE",
>                                           "SCHEMA",
>                                           "NAMES");
>
> and remove the now-redundant later Matches("SET", "SESSION") block
>
> Worth considering whether ROLE should be in both lists too, since SET LOCAL ROLE … and SET SESSION ROLE … are both valid.
>
> Regards,
> Surya Poondla

I tried applying the suggestion and it seems to fix the issue (see
attached), although the special syntax seems to be hidden by the long
list of variables. If we expand the list of variables it shows up
though. I haven't found a way to make them more obvious.

These are the modifications from the previous version of the patch:
- SESSION can actually be a modifier for SET SESSION AUTHORIZATION, so
SET SESSION SESSION AUTHORIZATION ... is legal syntax. Looking at
gram.y, it seems llike this is the case for SESSION CHARACTERISTICS
... too, although this doesn't seem properly reflected in the
documentation: https://www.postgresql.org/docs/18/sql-set-transaction.html
(perhaps an opportunity for a follow-up patch).
- I added the ROLE syntax.
- I removed the SCHEMA and NAMES options. I tried changing the
documentation for them in a different thread and Tom Lane let me know
that these are there only for compatibility with the standard and we
don't want to make them very obvious in the documentation. I assume we
don't want to make them very obvious in autocompletion either.

Thanks for the comments,
Álvaro

On Fri, Jun 12, 2026 at 12:46 PM solai v  wrote:
>
> Hi,
>
>
> > I am thinking maybe we could split the cases as
> >     /* Complete "SET LOCAL" */
> >     else if (Matches("SET", "LOCAL"))
> >         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
> >                                           "TIME ZONE",
> >                                           "SCHEMA",
> >                                           "NAMES");
> >     /* Complete "SET SESSION" */
> >     else if (Matches("SET", "SESSION"))
> >         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
> >                                           "AUTHORIZATION",
> >                                           "CHARACTERISTICS AS TRANSACTION",
> >                                           "TIME ZONE",
> >                                           "SCHEMA",
> >                                           "NAMES");
> >
> > and remove the now-redundant later Matches("SET", "SESSION") block
> >
> > Worth considering whether ROLE should be in both lists too, since SET LOCAL ROLE … and SET SESSION ROLE … are both valid.
> >
> > Regards,
> > Surya Poondla
>
> I tested the patch and was able to reproduce the regression that Surya
> pointed out.
>
> Before applying the patch:
>    SET LOCAL  completed to TO.
>    SET SESSION  suggested AUTHORIZATION and CHARACTERISTICS AS TRANSACTION.
>
> After applying the patch:
>     SET LOCAL  correctly shows configuration variable completions
> along with TIME ZONE, SCHEMA, and NAMES.
>     SET SESSION  also shows the configuration variable list, but
> no longer suggests AUTHORIZATION or CHARACTERISTICS AS TRANSACTION.
>
> I also experimented with separating the SET LOCAL and SET SESSION
> completion rules, similar to the approach suggested by Surya, and
> tried a few variations while testing. However, I was not able to
> restore the previous SET SESSION completion behavior in my
> environment.
> So I can reproduce both the original issue and the regression, but I
> do not yet have a verified fix.
> Are there any additional ideas or approaches that I should investigate
> to preserve the existing SET SESSION completions while keeping the new
> SET LOCAL behavior?
>
> regards
> Solai

Improving psql autocompletion for SET LOCAL / SET SESSION

Re: Improving psql autocompletion for SET LOCAL / SET SESSION

Hi,


> I am thinking maybe we could split the cases as
>     /* Complete "SET LOCAL" */
>     else if (Matches("SET", "LOCAL"))
>         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
>                                           "TIME ZONE",
>                                           "SCHEMA",
>                                           "NAMES");
>     /* Complete "SET SESSION" */
>     else if (Matches("SET", "SESSION"))
>         COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
>                                           "AUTHORIZATION",
>                                           "CHARACTERISTICS AS TRANSACTION",
>                                           "TIME ZONE",
>                                           "SCHEMA",
>                                           "NAMES");
>
> and remove the now-redundant later Matches("SET", "SESSION") block
>
> Worth considering whether ROLE should be in both lists too, since SET LOCAL ROLE … and SET SESSION ROLE … are both valid.
>
> Regards,
> Surya Poondla

I tested the patch and was able to reproduce the regression that Surya
pointed out.

Before applying the patch:
   SET LOCAL  completed to TO.
   SET SESSION  suggested AUTHORIZATION and CHARACTERISTICS AS TRANSACTION.

After applying the patch:
    SET LOCAL  correctly shows configuration variable completions
along with TIME ZONE, SCHEMA, and NAMES.
    SET SESSION  also shows the configuration variable list, but
no longer suggests AUTHORIZATION or CHARACTERISTICS AS TRANSACTION.

I also experimented with separating the SET LOCAL and SET SESSION
completion rules, similar to the approach suggested by Surya, and
tried a few variations while testing. However, I was not able to
restore the previous SET SESSION completion behavior in my
environment.
So I can reproduce both the original issue and the regression, but I
do not yet have a verified fix.
Are there any additional ideas or approaches that I should investigate
to preserve the existing SET SESSION completions while keeping the new
SET LOCAL behavior?

regards
Solai

Re: Improving psql autocompletion for SET LOCAL / SET SESSION

Re: Improving psql autocompletion for SET LOCAL / SET SESSION

Hi everyone,

I have spent the past two days learning tab-complete.in.c and the relevant SQL syntax 
we discussed starting from 10:46:14 on 2026-06-12, and I have finalized my own patch version today.

Unfortunately, I did not keep track of updates to this mailing list and CF-App these days, and I just 
found that the patch has already been iterated to v2. I really appreciate all of your hard work moving this forward.

I have finished my independent v2 implementation and verified it works via local testing, so I am sharing 
my patch here and welcome any feedback or suggestions on its deficiencies.

I haven’t had time to implement and test the fixes related to SESSION / LOCAL pointed out by Surya, 
so those improvements are not covered in my current patch.

I also have two questions I would like to clarify:
1.We retained SEED and TIME ZONE as special SET parameters after removing the rarely used 
    SCHEMA and NAMES. Could anyone explain why SEED is not included in the official v2 patch?
2.It seems the current v2 patch does not refactor the logic for the SET command. Is this because 
    SET and RESET still share the same implementation, making decoupling difficult?


Below is the design plan I prepared in advance for this improvement:

After reviewing tab-complete.in.c together with the syntax specifications for the 
SET and RESET commands, I’ve come up with a refinement plan as follows:
1.Split the completion logic for SET and RESET.
  Both commands currently share the same Query_for_list_of_set_vars. With the new features 
we plan to introduce, the syntactic differences between them will become more prominent. 
Separating their tab-completion implementations will make the completion rules more precise for each command, so I propose splitting them.

2.Add a dedicated tab-completion branch for SET LOCAL.

3.Refine the SET SESSION completion branch to expand syntax coverage for tab completion.


Key Modification Details:
0.Add macro definition for newly introduced special SET parameters:
```c
/* SET special parameters  */
#define Set_special_parameters \
"SCHEMA", "NAMES", "SEED", "TIME ZONE"
```c

1.Tab-completion candidate list for the separated RESET command:
RESET candidates = $Query_for_list_of_set_vars + "ALL"
  This strictly conforms to official syntax. We remove the original irrelevant entries: 
CONSTRAINTS, TRANSACTION, SESSION, ROLE, TABLESPACE. No newly added parameter 
entries from this patch will be included here, resulting in a cleaner, syntax-consistent 
completion list free of unrelated options.

2.Tab-completion candidate list for the separated SET command:
SET candidates =
$Query_for_list_of_set_vars
 + original reserved entries: CONSTRAINTS, TRANSACTION, SESSION, ROLE, TABLESPACE
      (excluding "ALL", which exclusively belongs to RESET)
 + SESSION, LOCAL
 + Set_special_parameters
 
This candidate set fully aligns with PostgreSQL official SQL syntax.

3.New tab-completion candidate list for SET LOCAL:
SET LOCAL candidates = $Query_for_list_of_set_vars + Set_special_parameters
The list strictly matches valid grammar for the SET LOCAL clause.

4.Refined tab-completion candidate list for SET SESSION:
SET SESSION candidates = $Query_for_list_of_set_vars
  + Set_special_parameters
  + legacy entries from existing completion logic: AUTHORIZATION, CHARACTERISTICS AS TRANSACTION
This design complies with standard SQL syntax while retaining the original 
completion options bound to SET SESSION in the current codebase.

Looking forward to your valuable reviews and suggestions.

regards,
--
ZizhuanLiu (X-MAN) 
44973863@qq.com