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 <solai(dot)cdac(at)gmail(dot)com> 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 <TAB> completed to TO.
> SET SESSION <TAB> suggested AUTHORIZATION and CHARACTERISTICS AS TRANSACTION.
>
> After applying the patch:
> SET LOCAL <TAB> correctly shows configuration variable completions
> along with TIME ZONE, SCHEMA, and NAMES.
> SET SESSION <TAB> 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