Thread
Commits
-
Add psql PROMPT variable for search_path.
- b3ce55f413cd 19 (unreleased) landed
-
doc PG 18 relnotes: add AFTER trigger user change item
- 73e26cbeb592 18.0 cited
-
Feature: psql - display current search_path in prompt
Lauri Siltanen <lauri.siltanen@gmail.com> — 2025-06-06T07:50:04Z
Hi all, I need to switch search_paths often. It would be tremendously helpful to see the current search_path in the prompt. - Lauri Siltanen
-
Re: Feature: psql - display current search_path in prompt
Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-06-07T23:36:54Z
On Sat, 7 Jun 2025 at 20:52, Lauri Siltanen <lauri.siltanen@gmail.com> wrote: > I need to switch search_paths often. It would be tremendously helpful to see the current search_path in the prompt. That feature should be pretty easy to implement, now that search_path is marked as GUC_REPORT in PG18. Basically you need to use PQparameterStatus like we do for session_authorization[1][2]. [1]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/prompt.c#L166-L169 [2]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/common.c#L2508-L2520
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-09T15:54:16Z
> On 8 Jun 2025, at 2:36 AM, Jelte Fennema-Nio <postgres@jeltef.nl> wrote: > > On Sat, 7 Jun 2025 at 20:52, Lauri Siltanen <lauri.siltanen@gmail.com> wrote: >> I need to switch search_paths often. It would be tremendously helpful to see the current search_path in the prompt. > > That feature should be pretty easy to implement, now that search_path > is marked as GUC_REPORT in PG18. Basically you need to use > PQparameterStatus like we do for session_authorization[1][2]. > > [1]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/prompt.c#L166-L169 > [2]: https://github.com/postgres/postgres/blob/73e26cbeb5927053eea4e209e5eda34a30c353f1/src/bin/psql/common.c#L2508-L2520 > > Here’s a quick attempt that makes %S substitue for a search_path Like \set PROMPT1 'user:%n search_path: %S'
-
Re: Feature: psql - display current search_path in prompt
Jelte Fennema-Nio <postgres@jeltef.nl> — 2025-06-09T23:08:43Z
On Mon, 9 Jun 2025 at 17:54, Florents Tselai <florents.tselai@gmail.com> wrote: > Here’s a quick attempt that makes %S substitue for a search_path > Like > \set PROMPT1 'user:%n search_path: %S' + else + return PQuser(pset.db); That seems like a copy paste error. If we don't have data for it, we should either use the empty string, or some string like "<unknown>". Other than that, the patch looks good (but I haven't tested it yet).
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-10T11:36:53Z
On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl> wrote: > On Mon, 9 Jun 2025 at 17:54, Florents Tselai <florents.tselai@gmail.com> > wrote: > > Here’s a quick attempt that makes %S substitue for a search_path > > Like > > \set PROMPT1 'user:%n search_path: %S' > > + else > + return PQuser(pset.db); > > That seems like a copy paste error. If we don't have data for it, we > should either use the empty string, or some string like "<unknown>". > Opted for an empty string > Other than that, the patch looks good (but I haven't tested it yet). > Btw - I haven't worked on bin/psql code; aren't these auto tested? CF: https://commitfest.postgresql.org/patch/5808/
-
Re: Feature: psql - display current search_path in prompt
Jim Jones <jim.jones@uni-muenster.de> — 2025-06-10T12:51:16Z
Hi Florents On 10.06.25 13:36, Florents Tselai wrote: > > On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl > <mailto:postgres@jeltef.nl>> wrote: > > On Mon, 9 Jun 2025 at 17:54, Florents Tselai > <florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote: > > Here’s a quick attempt that makes %S substitue for a search_path > > Like > > \set PROMPT1 'user:%n search_path: %S' > > + else > + return PQuser(pset.db); > > That seems like a copy paste error. If we don't have data for it, we > should either use the empty string, or some string like "<unknown>". > > > Opted for an empty string > > > Other than that, the patch looks good (but I haven't tested it yet). > I've taken a quick look at the patch, and it seems to work as expected. == PROMPT1 == postgres=# \set PROMPT1 '(search_path: %S) ;; ' (search_path: "$user", public) ;; SET search_path TO s1, public; SET (search_path: s1, public) ;; SET search_path TO s2, public; SET (search_path: s2, public) ;; RESET search_path; RESET (search_path: "$user", public) ;; == PROMPT2 == postgres=# \set PROMPT2 '(search_path: %S) ** ' postgres=# SELECT (search_path: "$user", public) ** ^C postgres=# SET search_path TO s1, public; SET postgres=# SELECT (search_path: s1, public) ** ^C postgres=# SET search_path TO s2, public; SET postgres=# SELECT (search_path: s2, public) ** ^C postgres=# RESET search_path; RESET postgres=# SELECT (search_path: "$user", public) ** == PROMPT3 == postgres=# \set PROMPT3 '(search_path: %S) ## ' postgres=# COPY t1 (a) FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. (search_path: "$user", public) ## 1 (search_path: "$user", public) ## 2 (search_path: "$user", public) ## \. COPY 2 postgres=# SET search_path TO s1, public; SET postgres=# COPY t1 (a) FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. (search_path: s1, public) ## 42 (search_path: s1, public) ## 73 (search_path: s1, public) ## \. COPY 2 postgres=# RESET search_path; RESET postgres=# COPY t1 (a) FROM STDIN; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself, or an EOF signal. (search_path: "$user", public) ## 0 (search_path: "$user", public) ## 1 (search_path: "$user", public) ## \. COPY 2 Documentation looks ok as well -- it aligns with the other entries in the file. > > Btw - I haven't worked on bin/psql code; > aren't these auto tested? I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte knows more about it? Best regards, Jim
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-10T13:09:08Z
> On 10 Jun 2025, at 3:51 PM, Jim Jones <jim.jones@uni-muenster.de> wrote: > > Hi Florents > > On 10.06.25 13:36, Florents Tselai wrote: >> >> On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl >> <mailto:postgres@jeltef.nl>> wrote: >> >> On Mon, 9 Jun 2025 at 17:54, Florents Tselai >> <florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote: >>> Here’s a quick attempt that makes %S substitue for a search_path >>> Like >>> \set PROMPT1 'user:%n search_path: %S' >> >> + else >> + return PQuser(pset.db); >> >> That seems like a copy paste error. If we don't have data for it, we >> should either use the empty string, or some string like "<unknown>". >> >> >> Opted for an empty string >> >> >> Other than that, the patch looks good (but I haven't tested it yet). >> > > > I've taken a quick look at the patch, and it seems to work as expected. > > == PROMPT1 == > > postgres=# \set PROMPT1 '(search_path: %S) ;; ' > (search_path: "$user", public) ;; SET search_path TO s1, public; > SET > (search_path: s1, public) ;; SET search_path TO s2, public; > SET > (search_path: s2, public) ;; RESET search_path; > RESET > (search_path: "$user", public) ;; > > == PROMPT2 == > > postgres=# \set PROMPT2 '(search_path: %S) ** ' > postgres=# SELECT > (search_path: "$user", public) ** ^C > postgres=# SET search_path TO s1, public; > SET > postgres=# SELECT > (search_path: s1, public) ** ^C > postgres=# SET search_path TO s2, public; > SET > postgres=# SELECT > (search_path: s2, public) ** ^C > postgres=# RESET search_path; > RESET > postgres=# SELECT > (search_path: "$user", public) ** > > > == PROMPT3 == > > postgres=# \set PROMPT3 '(search_path: %S) ## ' > postgres=# COPY t1 (a) FROM STDIN; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself, or an EOF signal. > (search_path: "$user", public) ## 1 > (search_path: "$user", public) ## 2 > (search_path: "$user", public) ## \. > COPY 2 > postgres=# SET search_path TO s1, public; > SET > postgres=# COPY t1 (a) FROM STDIN; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself, or an EOF signal. > (search_path: s1, public) ## 42 > (search_path: s1, public) ## 73 > (search_path: s1, public) ## \. > COPY 2 > postgres=# RESET search_path; > RESET > postgres=# COPY t1 (a) FROM STDIN; > Enter data to be copied followed by a newline. > End with a backslash and a period on a line by itself, or an EOF signal. > (search_path: "$user", public) ## 0 > (search_path: "$user", public) ## 1 > (search_path: "$user", public) ## \. > COPY 2 > > Documentation looks ok as well -- it aligns with the other entries in > the file. Thanks for the review. > >> >> Btw - I haven't worked on bin/psql code; >> aren't these auto tested? > > I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte > knows more about it? Doesn’t look like it though; e.g. this https://github.com/Florents-Tselai/postgres/commit/79fad725aa410d6c631d4ffe0f4120837f9b478c didn’t break anything - on Cirrus at least
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-10T13:37:57Z
> On 10 Jun 2025, at 4:09 PM, Florents Tselai <florents.tselai@gmail.com> wrote: > > > >> On 10 Jun 2025, at 3:51 PM, Jim Jones <jim.jones@uni-muenster.de> wrote: >> >> Hi Florents >> >> On 10.06.25 13:36, Florents Tselai wrote: >>> >>> On Tue, Jun 10, 2025 at 2:08 AM Jelte Fennema-Nio <postgres@jeltef.nl >>> <mailto:postgres@jeltef.nl>> wrote: >>> >>> On Mon, 9 Jun 2025 at 17:54, Florents Tselai >>> <florents.tselai@gmail.com <mailto:florents.tselai@gmail.com>> wrote: >>>> Here’s a quick attempt that makes %S substitue for a search_path >>>> Like >>>> \set PROMPT1 'user:%n search_path: %S' >>> >>> + else >>> + return PQuser(pset.db); >>> >>> That seems like a copy paste error. If we don't have data for it, we >>> should either use the empty string, or some string like "<unknown>". >>> >>> >>> Opted for an empty string >>> >>> >>> Other than that, the patch looks good (but I haven't tested it yet). >>> >> >> >> I've taken a quick look at the patch, and it seems to work as expected. >> >> == PROMPT1 == >> >> postgres=# \set PROMPT1 '(search_path: %S) ;; ' >> (search_path: "$user", public) ;; SET search_path TO s1, public; >> SET >> (search_path: s1, public) ;; SET search_path TO s2, public; >> SET >> (search_path: s2, public) ;; RESET search_path; >> RESET >> (search_path: "$user", public) ;; >> >> == PROMPT2 == >> >> postgres=# \set PROMPT2 '(search_path: %S) ** ' >> postgres=# SELECT >> (search_path: "$user", public) ** ^C >> postgres=# SET search_path TO s1, public; >> SET >> postgres=# SELECT >> (search_path: s1, public) ** ^C >> postgres=# SET search_path TO s2, public; >> SET >> postgres=# SELECT >> (search_path: s2, public) ** ^C >> postgres=# RESET search_path; >> RESET >> postgres=# SELECT >> (search_path: "$user", public) ** >> >> >> == PROMPT3 == >> >> postgres=# \set PROMPT3 '(search_path: %S) ## ' >> postgres=# COPY t1 (a) FROM STDIN; >> Enter data to be copied followed by a newline. >> End with a backslash and a period on a line by itself, or an EOF signal. >> (search_path: "$user", public) ## 1 >> (search_path: "$user", public) ## 2 >> (search_path: "$user", public) ## \. >> COPY 2 >> postgres=# SET search_path TO s1, public; >> SET >> postgres=# COPY t1 (a) FROM STDIN; >> Enter data to be copied followed by a newline. >> End with a backslash and a period on a line by itself, or an EOF signal. >> (search_path: s1, public) ## 42 >> (search_path: s1, public) ## 73 >> (search_path: s1, public) ## \. >> COPY 2 >> postgres=# RESET search_path; >> RESET >> postgres=# COPY t1 (a) FROM STDIN; >> Enter data to be copied followed by a newline. >> End with a backslash and a period on a line by itself, or an EOF signal. >> (search_path: "$user", public) ## 0 >> (search_path: "$user", public) ## 1 >> (search_path: "$user", public) ## \. >> COPY 2 >> >> Documentation looks ok as well -- it aligns with the other entries in >> the file. > > Thanks for the review. > >> >>> >>> Btw - I haven't worked on bin/psql code; >>> aren't these auto tested? >> >> I also couldn't find any test related to psql's PROMPT*. Perhaps Jelte >> knows more about it? > > > Doesn’t look like it though; > e.g. this https://github.com/Florents-Tselai/postgres/commit/79fad725aa410d6c631d4ffe0f4120837f9b478c > didn’t break anything - on Cirrus at least EDIT: There are test under `src/psql/t` , not sure though how much coverage they have, but most importantly how it’d look like for this case.
-
Re: Feature: psql - display current search_path in prompt
Jim Jones <jim.jones@uni-muenster.de> — 2025-06-11T09:51:02Z
On 10.06.25 15:37, Florents Tselai wrote: > EDIT: There are test under `src/psql/t` , not sure though how much > coverage they have, > but most importantly how it’d look like for this case. I took a look at these files, but I'm still unsure how to use them for automated prompt checking - I'm not super familiar with the perl tests, to be honest. -- Jim
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-11T10:01:40Z
On Wed, Jun 11, 2025 at 12:51 PM Jim Jones <jim.jones@uni-muenster.de> wrote: > On 10.06.25 15:37, Florents Tselai wrote: > > EDIT: There are test under `src/psql/t` , not sure though how much > > coverage they have, > > but most importantly how it’d look like for this case. > > I took a look at these files, but I'm still unsure how to use them for > automated prompt checking - I'm not super familiar with the perl tests, > to be honest. > From Tom at the discord channel * Yeah, you can see from the code coverage report [1] that session_username() isn't reached in our tests. It's only used if the psql prompt string is set to use it, and testing that in an interesting way is kind of hard --- our standard regression-script framework doesn't expose prompt output. On balance I'm not sure that covering session_username() would be worth the test cycles. [1] https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html <https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html>* So, yes I don't think we can auto-test it really, thus we'll have to rely on these simple functional tests.
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-06-12T15:57:37Z
On Wed, Jun 11, 2025 at 1:01 PM Florents Tselai <florents.tselai@gmail.com> wrote: > > > > On Wed, Jun 11, 2025 at 12:51 PM Jim Jones <jim.jones@uni-muenster.de> > wrote: > >> On 10.06.25 15:37, Florents Tselai wrote: >> > EDIT: There are test under `src/psql/t` , not sure though how much >> > coverage they have, >> > but most importantly how it’d look like for this case. >> >> I took a look at these files, but I'm still unsure how to use them for >> automated prompt checking - I'm not super familiar with the perl tests, >> to be honest. >> > > From Tom at the discord channel > > * Yeah, you can see from the code coverage report [1] that > session_username() isn't reached in our tests. It's only used if the psql > prompt string is set to use it, and testing that in an interesting way is > kind of hard --- our standard regression-script framework doesn't expose > prompt output. On balance I'm not sure that covering session_username() > would be worth the test cycles. [1] > https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html > <https://coverage.postgresql.org/src/bin/psql/common.c.gcov.html>* > > So, yes I don't think we can auto-test it really, thus we'll have to rely > on these simple functional tests. > Absent any other feedback I'm marking this as Ready for Committer; Said committer can push back on my arbitrary %S selection https://commitfest.postgresql.org/patch/5808/
-
Re: Feature: psql - display current search_path in prompt
Nathan Bossart <nathandbossart@gmail.com> — 2025-10-23T20:52:23Z
On Thu, Jun 12, 2025 at 06:57:37PM +0300, Florents Tselai wrote: > Absent any other feedback I'm marking this as Ready for Committer; > Said committer can push back on my arbitrary %S selection > https://commitfest.postgresql.org/patch/5808/ PQparameterStatus() will return NULL if the parameter is not known. So, with this patch, using %S in a prompt when connected to a <v18 version of PostgreSQL will produce an empty string. Given an empty string is a valid value for search_path, I think we probably want to use a special value in this case. Perhaps we could use "?", which is much less likely to be a value for search_path. -- nathan
-
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-10-24T14:10:13Z
On Thu, Oct 23, 2025 at 11:52 PM Nathan Bossart <nathandbossart@gmail.com> wrote: > On Thu, Jun 12, 2025 at 06:57:37PM +0300, Florents Tselai wrote: > > Absent any other feedback I'm marking this as Ready for Committer; > > Said committer can push back on my arbitrary %S selection > > https://commitfest.postgresql.org/patch/5808/ > > PQparameterStatus() will return NULL if the parameter is not known. So, > with this patch, using %S in a prompt when connected to a <v18 version of > PostgreSQL will produce an empty string. Given an empty string is a valid > value for search_path, I think we probably want to use a special value in > this case. Perhaps we could use "?", which is much less likely to be a > value for search_path. > Good catch. v2 attached
-
Re: Feature: psql - display current search_path in prompt
Nathan Bossart <nathandbossart@gmail.com> — 2025-10-24T14:20:38Z
On Fri, Oct 24, 2025 at 05:10:13PM +0300, Florents Tselai wrote: > Good catch. v2 attached Thanks. + <varlistentry id="app-psql-prompting-S"> + <term><literal>%S</literal></term> + <listitem><para>The current search path.</para></listitem> + </varlistentry> I'd suggest linking to a page about the search_path [0]. +extern const char *session_search_path(void); This function appears to be unused. + /* + * Distinguish unknown (NULL) from an empty but valid search_path (""). + * If not connected or older server doesn't report it via ParameterStatus, + * show "?". + */ + if (!pset.db || PQparameterStatus(pset.db, "search_path") == NULL) + strlcpy(buf, "?", sizeof(buf)); + else + strlcpy(buf, PQparameterStatus(pset.db, "search_path"), sizeof(buf)); + break; Shouldn't we move most of this logic to session_search_path() and use it here? [0] https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH -- nathan -
Re: Feature: psql - display current search_path in prompt
Florents Tselai <florents.tselai@gmail.com> — 2025-10-25T04:38:58Z
On Fri, Oct 24, 2025 at 5:20 PM Nathan Bossart <nathandbossart@gmail.com> wrote: > On Fri, Oct 24, 2025 at 05:10:13PM +0300, Florents Tselai wrote: > > Good catch. v2 attached > > Thanks. > > + <varlistentry id="app-psql-prompting-S"> > + <term><literal>%S</literal></term> > + <listitem><para>The current search path.</para></listitem> > + </varlistentry> > > I'd suggest linking to a page about the search_path [0]. > Done > > +extern const char *session_search_path(void); > > This function appears to be unused. > > + /* > + * Distinguish unknown (NULL) from > an empty but valid search_path (""). > + * If not connected or older > server doesn't report it via ParameterStatus, > + * show "?". > + */ > + if (!pset.db || > PQparameterStatus(pset.db, "search_path") == NULL) > + strlcpy(buf, "?", > sizeof(buf)); > + else > + strlcpy(buf, > PQparameterStatus(pset.db, "search_path"), sizeof(buf)); > + break; > > Shouldn't we move most of this logic to session_search_path() and use it > here? > Yes -
Re: Feature: psql - display current search_path in prompt
Chao Li <li.evan.chao@gmail.com> — 2025-10-25T07:57:11Z
> On Oct 25, 2025, at 12:38, Florents Tselai <florents.tselai@gmail.com> wrote: > > <v3-0001-psql-Add-S-prompt-escape-to-display-current-searc.patch> Overall LGTM. Just one comment: ``` + /* current search_path, or "?" if not reported by the server */ + case 'S': + strlcpy(buf, session_search_path(), sizeof(buf)); + break; ``` I guess we also need to update the function comment to add a description for “%S”. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
-
Re: Feature: psql - display current search_path in prompt
Nathan Bossart <nathandbossart@gmail.com> — 2025-10-27T20:29:37Z
Here is what I have staged for commit. I ended up simplifying the patch a bit. In particular, I thought better of the question mark business. It looks like we ordinarily just skip values that can't be found, and an empty search_path will appear as "" (two double-quotes), so you can still distinguish empty versus not-available. -- nathan
-
Re: Feature: psql - display current search_path in prompt
Chao Li <li.evan.chao@gmail.com> — 2025-10-28T01:08:36Z
> On Oct 28, 2025, at 04:29, Nathan Bossart <nathandbossart@gmail.com> wrote: > > Here is what I have staged for commit. I ended up simplifying the patch a > bit. In particular, I thought better of the question mark business. It > looks like we ordinarily just skip values that can't be found, and an empty > search_path will appear as "" (two double-quotes), so you can still > distinguish empty versus not-available. +1, I like this idea. V4 looks good to me. Best regards, -- Chao Li (Evan) HighGo Software Co., Ltd. https://www.highgo.com/
-
Re: Feature: psql - display current search_path in prompt
Nathan Bossart <nathandbossart@gmail.com> — 2025-10-28T17:21:15Z
On Tue, Oct 28, 2025 at 09:08:36AM +0800, Chao Li wrote: >> On Oct 28, 2025, at 04:29, Nathan Bossart <nathandbossart@gmail.com> wrote: >> Here is what I have staged for commit. I ended up simplifying the patch a >> bit. In particular, I thought better of the question mark business. It >> looks like we ordinarily just skip values that can't be found, and an empty >> search_path will appear as "" (two double-quotes), so you can still >> distinguish empty versus not-available. > > +1, I like this idea. Actually, I take it back. The following command to empty the search_path will cause %S to be replaced with nothing, in which case you can't distinguish empty versus not-reported. SELECT pg_catalog.set_config('search_path', '', false); So, I've added the question mark back. -- nathan -
Re: Feature: psql - display current search_path in prompt
Nathan Bossart <nathandbossart@gmail.com> — 2025-10-28T19:10:18Z
Committed. -- nathan