Horton

Thread

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. psql: Tab-complete ALTER ROLE ... IN DATABASE SET/RESET

  1. [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    Ian Lawrence Barwick <barwick@gmail.com> — 2025-11-21T03:14:00Z

    Hi

I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
and was annoyed by the lack of tab completion for this, so patch attached.


Regards

Ian Barwick

  2. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    Neil Chen <carpenter.nail.cz@gmail.com> — 2025-11-21T07:36:52Z

    On Fri, Nov 21, 2025 at 1:25 PM Ian Lawrence Barwick <barwick@gmail.com>
wrote:

> Hi
>
> I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
> and was annoyed by the lack of tab completion for this, so patch attached.
>
>
> Regards
>
> Ian Barwick
>
>
HI, I've reviewed the patch and did simple tests — it works correctly.

  3. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    Dagfinn Ilmari Mannsåker <ilmari@ilmari.org> — 2025-11-21T13:02:38Z

    Ian Lawrence Barwick <barwick@gmail.com> writes:

> Hi
>
> I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
> and was annoyed by the lack of tab completion for this, so patch attached.

A noble goal, but unfortunately th RESET form can't work properly due to
limitations of the tab completion system.

> +	/* ALTER USER,ROLE <name> IN DATABASE */
> +	else if (HeadMatches("ALTER", "USER|ROLE", MatchAny, "IN"))
> +	{
[...]
> +		else if (TailMatches("DATABASE", MatchAny, "RESET"))
> +		{
> +			set_completion_reference(prev5_wd);
> +			COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");

This queries pg_roles.rolconfig, which only contains variables set for
the user in all databases, not in the specified database.  Instead,
you'd need to query pg_db_role_setting WHERE setdatabase = (SELECT oid
FROM pg_database WHERE datname = '%s') AND setrole = '%s'::regrole, but
unfortunately the tab completion system doesn't let you more than one
previous word in the query.  I guess you could query WHERE setdatabase
<> 0, to get variables set for the user across all databases, not just
the specified one.

Also, alter ALTER ROLE ALL RESET needs separate handling, filtering
where setrole = 0, which is actually possible in the current system.

- ilmari

  4. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    BharatDB <bharatdbpg@gmail.com> — 2025-11-25T09:16:11Z

    Hi Ian,

+1 for the patch,LGTM

But after applying the patch i can be able to apply all the results
except the reset options user variables

postgres=#
postgres=# alter role bob
BYPASSRLS           CREATEROLE          INHERIT             NOCREATEDB
         NOLOGIN             PASSWORD            RESET
VALID UNTIL
CONNECTION LIMIT    ENCRYPTED PASSWORD  LOGIN
NOCREATEROLE        NOREPLICATION       RENAME TO           SET
         WITH
CREATEDB            IN DATABASE         NOBYPASSRLS         NOINHERIT
         NOSUPERUSER         REPLICATION         SUPERUSER
postgres=# alter role bob in DATABASE
postgres   template0  template1
postgres=# alter role bob in DATABASE postgres
RESET  SET
postgres=# alter role bob in DATABASE postgres reset ALL

postgres=# alter role bob in DATABASE postgres reset ALL

also i cross verified that my system doesn't have variables so it
returns 0 rows?but:

postgres=# SELECT name FROM pg_settings LIMIT 5;
            name
----------------------------
 allow_alter_system
 allow_in_place_tablespaces
 allow_system_table_mods
 application_name
 archive_cleanup_command
(5 rows)

Can you check this ?

-regards

Vasuki M


On Fri, Nov 21, 2025 at 8:44 AM Ian Lawrence Barwick <barwick@gmail.com> wrote:
>
> Hi
>
> I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
> and was annoyed by the lack of tab completion for this, so patch attached.
>
>
> Regards
>
> Ian Barwick
>

  5. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    Neil Chen <carpenter.nail.cz@gmail.com> — 2025-11-25T09:36:31Z

    Hi BharatDB,

On Tue, Nov 25, 2025 at 5:15 PM BharatDB <bharatdbpg@gmail.com> wrote:

> Hi Ian,
>
> +1 for the patch,LGTM
>
> But after applying the patch i can be able to apply all the results
> except the reset options user variables
>
> postgres=# alter role bob in DATABASE postgres reset ALL
>
> also i cross verified that my system doesn't have variables so it
> returns 0 rows?but:
>
> postgres=# SELECT name FROM pg_settings LIMIT 5;
>             name
> ----------------------------
>  allow_alter_system
>  allow_in_place_tablespaces
>  allow_system_table_mods
>  application_name
>  archive_cleanup_command
> (5 rows)
>
>
The tab-completion here queries the user-specific config (not the global
pg_settings). I believe the underlying code logic will help explain this
behavior:

> + else if (TailMatches("DATABASE", MatchAny, "RESET"))
> + {
> + set_completion_reference(prev5_wd);
> + COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");
> + }


 #define Query_for_list_of_user_vars \
> "SELECT conf FROM ("\
> "       SELECT rolname,
> pg_catalog.split_part(pg_catalog.unnest(rolconfig),'=',1) conf"\
> "         FROM pg_catalog.pg_roles"\
> "       ) s"\
> "  WHERE s.conf like '%s' "\
> "    AND s.rolname LIKE '%s'"

  6. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    VASUKI M <vasukianand0119@gmail.com> — 2025-11-27T09:20:07Z

    Hello all,

Based on the discussion, I have updated the patch to handle the RESET form
correctly without modifying psql’s tab-completion APIs.

Both the current database connection and the parsed input tokens are
already available via pset.db and the word tokens.The new patch extracts:

   -

   the role name from the parsed tokens (prev5_wd), and
   -

   the database name from the parsed tokens (prev2_wd),

and uses these to query pg_db_role_setting for variables that are actually
set for the specific (role, database) pair.

Literal quoting is now done with PQescapeLiteral(pset.db, …), per libpq
conventions, so no new helper functions were needed.

SET
After

ALTER ROLE <role> IN DATABASE <dbname> SET <TAB>

psql completes from Query_for_list_of_set_vars (same behavior as plain
ALTER ROLE … SET).

RESET
After

ALTER ROLE <role> IN DATABASE <dbname> RESET <TAB>

psql now completes with the GUC names recorded in pg_db_role_setting for
that specific (role,database), plus ALL.
When no settings exist, only ALL is suggested.
This mirrors the existing behavior of ALTER DATABASE … RESET.

I have attached the patch.

Regards,

Vasuki

On Thu, Nov 27, 2025 at 2:27 PM Ian Lawrence Barwick <barwick@gmail.com>
wrote:

> Hi
>
> I found myself needing to work with ALTER ROLE ... IN DATABASE ... recently
> and was annoyed by the lack of tab completion for this, so patch attached.
>
>
> Regards
>
> Ian Barwick
>
>

  7. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    VASUKI M <vasukianand0119@gmail.com> — 2025-12-04T09:10:59Z

    Kindly review the attached patch ,
As i saw the proposed patch by Ian failed with CI
https://commitfest.postgresql.org/patch/6244/
Guide me with the patch

Regards,
Vasuki M

On Thu, Nov 27, 2025 at 2:50 PM VASUKI M <vasukianand0119@gmail.com> wrote:

> Hello all,
>
> Based on the discussion, I have updated the patch to handle the RESET form
> correctly without modifying psql’s tab-completion APIs.
>
> Both the current database connection and the parsed input tokens are
> already available via pset.db and the word tokens.The new patch extracts:
>
>    -
>
>    the role name from the parsed tokens (prev5_wd), and
>    -
>
>    the database name from the parsed tokens (prev2_wd),
>
> and uses these to query pg_db_role_setting for variables that are actually
> set for the specific (role, database) pair.
>
> Literal quoting is now done with PQescapeLiteral(pset.db, …), per libpq
> conventions, so no new helper functions were needed.
>
> SET
> After
>
> ALTER ROLE <role> IN DATABASE <dbname> SET <TAB>
>
> psql completes from Query_for_list_of_set_vars (same behavior as plain
> ALTER ROLE … SET).
>
> RESET
> After
>
> ALTER ROLE <role> IN DATABASE <dbname> RESET <TAB>
>
> psql now completes with the GUC names recorded in pg_db_role_setting for
> that specific (role,database), plus ALL.
> When no settings exist, only ALL is suggested.
> This mirrors the existing behavior of ALTER DATABASE … RESET.
>
> I have attached the patch.
>
> Regards,
>
> Vasuki
>
> On Thu, Nov 27, 2025 at 2:27 PM Ian Lawrence Barwick <barwick@gmail.com>
> wrote:
>
>> Hi
>>
>> I found myself needing to work with ALTER ROLE ... IN DATABASE ...
>> recently
>> and was annoyed by the lack of tab completion for this, so patch attached.
>>
>>
>> Regards
>>
>> Ian Barwick
>>
>>

  8. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    surya poondla <suryapoondla4@gmail.com> — 2025-12-11T21:30:01Z

    Hi All,

Thanks to Ian and Vasuki for working on tab-completion support for ALTER
ROLE ... IN DATABASE.
This is a really good improvement.

Regarding Ian’s patch:
1. The patch applies cleanly and works as expected.
2. Tab-completion for IN DATABASE behaves consistently with existing ALTER
ROLE forms.

Suggestion: consider adding regression tests in "src/bin/psql/t/" to cover
these new cases.


Regarding Vasuki’s patch:
1. Really nice idea to extend completion for RESET by querying
pg_db_role_setting.
2. Use of PQescapeLiteral() (safe quoting) and consistent fallback to ALL
is really nice, and if the user falls back to 'ALL' maybe it is a good idea
to log it or let the user know of it.

Suggestion: consider adding regression tests in "src/bin/psql/t/" to cover
these new cases for SET/RESET.

Overall both patches look great.

Regards,
Surya

  9. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    VASUKI M <vasukianand0119@gmail.com> — 2025-12-22T12:25:57Z

    Hi surya,

Thanks a lot for reviewing the patches and for the encouraging feedback
Sorry for the late reply — I missed the mail since I wasn’t CC’d and
noticed it only now.

Good point about the TAP tests. I'll be adding tab-completion tests in
src/bin/psql/t/010_tab_completion.pl for both Ian’s patch and the SET/RESET
cases from my patch shortly.

Thanks again!

Regards,

Vasuki M

C-DAC,Chennai

  10. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    zengman <zengman@halodbtech.com> — 2025-12-22T14:48:37Z

    Hi

I noticed that in the code, the variables `q_role` and `q_dbname` are processed with the `PQescapeLiteral` function,
so `PQfreemem` – instead of `pfree` – should be used here to free the memory.

--
Regards,
Man Zeng
www.openhalo.org

  11. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    VASUKI M <vasukianand0119@gmail.com> — 2025-12-22T15:40:50Z

    Hi zeng,

Thanks for pointing this out. You’re absolutely right — PQescapeLiteral()
allocates memory using libpq’s allocator, so the returned buffers must be
released with PQfreemem() rather than pfree(). Using pfree() here would be
incorrect, since it expects memory allocated via PostgreSQL’s memory
context APIs (palloc/psprintf).

I’ll update the patch to replace pfree() with PQfreemem() for the buffers
returned by PQescapeLiteral(),while continuing to use pfree() for memory
allocated via psprintf().

Thanks again for catching this.

Best regards,
Vasuki M
C-DAC,Chennai


On Mon, 22 Dec 2025, 8:18 pm zengman, <zengman@halodbtech.com> wrote:

> Hi
>
> I noticed that in the code, the variables `q_role` and `q_dbname` are
> processed with the `PQescapeLiteral` function,
> so `PQfreemem` – instead of `pfree` – should be used here to free the
> memory.
>
> --
> Regards,
> Man Zeng
> www.openhalo.org

  12. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    VASUKI M <vasukianand0119@gmail.com> — 2025-12-29T06:23:57Z

    Hi all,

I tried adding TAP coverage for the new ALTER ROLE … IN
DATABASE tab-completion paths in src/bin/psql/t/010_tab_completion.pl.

The tests themselves work as expected, but I ran into a limitation of the
existing interactive completion harness. The new  RESET completion
intentionally ends on incomplete SQL and leaves psql in
continuation/readline mode (postgres-#). As a result, the interactive psql
process does not terminate cleanly at the end of the test, causing
 IPC::Run to time out and the test to abort, even though all completion
checks pass.

Earlier completion tests never exercised this state, so the harness
implicitly assumes that psql can always be exited cleanly after <TAB> using
\q / clear query(); / clear line(); . This change exposes that assumption
rather than introducing a regression.

Given this limitation, and to avoid relying on timeouts or fragile cleanup
logic, I’m omitting TAP tests for this change for now. If there’s interest
in extending or refactoring the completion test harness to better handle
continuation-mode cases, I’d be happy to look into that separately.

Attaching some lines from the logfile for the reference,

[11:19:45.497](0.000s) ok 79 - complete a psql variable name
[11:19:45.497](0.000s) ok 80 - complete a psql variable value
[11:19:45.498](0.000s) ok 81 - \r works
[11:19:45.498](0.000s) ok 82 - complete an interpolated psql variable name
[11:19:45.498](0.000s) ok 83 - \r works
[11:19:45.498](0.000s) ok 84 - complete a psql variable test
[11:19:45.498](0.000s) ok 85 - \r works
[11:19:45.498](0.000s) ok 86 - check completion failure path
[11:19:45.499](0.000s) ok 87 - \r works
[11:19:45.499](0.000s) ok 88 - COPY FROM with DEFAULT completion
[11:19:45.499](0.000s) ok 89 - control-U works
IPC::Run: timeout on timer #1 at /usr/share/perl5/IPC/Run.pm line 3007.
# Postmaster PID for node "main" is 16601
### Stopping node "main" using mode immediate
# Running: pg_ctl --pgdata
/home/cdac/postgres/src/bin/psql/tmp_check/t_010_tab_completion_main_data/pgdata
--mode immediate stop
waiting for server to shut down.... done
server stopped
# No postmaster PID for node "main"
[11:22:46.573](181.074s) # Tests were run but no plan was declared and
done_testing() was not seen.
[11:22:46.574](0.000s) # Looks like your test exited with 29 just after 89.

Regards,

Vasuki M
C-DAC,Chennai.

On Mon, Dec 22, 2025 at 9:10 PM VASUKI M <vasukianand0119@gmail.com> wrote:

>
> Hi zeng,
>
> Thanks for pointing this out. You’re absolutely right — PQescapeLiteral()
> allocates memory using libpq’s allocator, so the returned buffers must be
> released with PQfreemem() rather than pfree(). Using pfree() here would be
> incorrect, since it expects memory allocated via PostgreSQL’s memory
> context APIs (palloc/psprintf).
>
> I’ll update the patch to replace pfree() with PQfreemem() for the buffers
> returned by PQescapeLiteral(),while continuing to use pfree() for memory
> allocated via psprintf().
>
> Thanks again for catching this.
>
> Best regards,
> Vasuki M
> C-DAC,Chennai
>
>
> On Mon, 22 Dec 2025, 8:18 pm zengman, <zengman@halodbtech.com> wrote:
>
>> Hi
>>
>> I noticed that in the code, the variables `q_role` and `q_dbname` are
>> processed with the `PQescapeLiteral` function,
>> so `PQfreemem` – instead of `pfree` – should be used here to free the
>> memory.
>>
>> --
>> Regards,
>> Man Zeng
>> www.openhalo.org
>
>

  13. Re: [PATCH] psql: tab completion for ALTER ROLE ... IN DATABASE ...

    zengman <zengman@halodbtech.com> — 2025-12-29T07:47:52Z

    Hi,

I got lots of indentation-related warnings when running git apply (see output below). 
Also, I found an issue: the RESET command unexpectedly displays "work_mem=16MB", 
which is not correct. I've made a minor fix by adding split_part and attached the v3 patch.

```
postgres@zxm-VMware-Virtual-Platform:~/code/postgres$ git apply v2-0001-psql-alter-role-in-database-tab-completion.patch
v2-0001-psql-alter-role-in-database-tab-completion.patch:67: indent with spaces.
            /* ALTER ROLE bob IN DATABASE <TAB> → list databases */
v2-0001-psql-alter-role-in-database-tab-completion.patch:68: indent with spaces.
            COMPLETE_WITH_QUERY(Query_for_list_of_databases);
v2-0001-psql-alter-role-in-database-tab-completion.patch:73: indent with spaces.
            /* ALTER ROLE bob IN DATABASE mydb <TAB> → SET, RESET */
v2-0001-psql-alter-role-in-database-tab-completion.patch:74: indent with spaces.
            COMPLETE_WITH("SET", "RESET");
v2-0001-psql-alter-role-in-database-tab-completion.patch:79: indent with spaces.
            /* ALTER ROLE bob IN DATABASE mydb SET <TAB> */
warning: squelched 37 whitespace errors
warning: 42 lines add whitespace errors.
```

```
postgres=# ALTER ROLE postgres IN DATABASE postgres SET work_mem = '16MB';
ALTER ROLE
postgres=# ALTER ROLE postgres IN DATABASE postgres RESET 
ALL              "work_mem=16MB"  
```

Could you please take a look and see if this modification is correct?

--
Regards,
Man Zeng
www.openhalo.org