Feedback-ID: i1ff147bf:Fastmail
From: =?utf-8?Q?Dagfinn_Ilmari_Manns=C3=A5ker?= <ilmari@ilmari.org>
To: pgsql-hackers@postgresql.org
Subject: Improve tab completion for various SET/RESET forms
Date: Mon, 09 Jun 2025 21:27:07 +0100
Message-ID: <87bjqwwtic.fsf@wibble.ilmari.org>
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.1 (gnu/linux)
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="=-=-="
Archived-At: 
 <https://www.postgresql.org/message-id/87bjqwwtic.fsf%40wibble.ilmari.org>
Precedence: bulk

--=-=-=
Content-Type: text/plain

Hi hackers,

I noticed that psql tab-completes every possible session-settable
variable after RESET, not just the ones that have actually been set in
the current session.  However, as I was fixing that I noticed several
other deficiencies around other forms of SET/RESET.  So, here's the
resulting yak stack.

Feel free to squash them as desired when committing, I just find it
easier to explain each thing as a separate commit/patch.

1. Fix tab completion for ALTER ROLE/USER ... RESET

   It was trying to get the variables set for the current user, but the
   query only worked on the intial tab after RESET, not when you started
   typing a prefix to pick the one you wanted.  Fix in the same way as
   ALTER DATABASE ... RESET does it.

2. Add tab completion for ALTER TABLE ... ALTER COLUMN ... RESET

   Complete with "(" and then the same as after ALTER COLUMN ... SET (.

   There are only two possible attribute options, so no need to filter
   down to the ones that have actually been set.

3. Add tab completion for ALTER FOREIGN TABLE ... SET

   Setting the schema is the only supported thing.

4. Remove guard against generic SET/RESET completion after ALTER TABLE
   ... RESET

   This is already handled specifically earlier in the code.  Only
   UPDATE is later and needs guarding against.

5. The actual patch I set out to write: only complete variables that
   have actually been set in the current session after RESET.

- ilmari
-- part-time yak stylist


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=0001-Fix-tab-completion-for-ALTER-ROLE-USER-.-RESET.patch

From b017b6f114059bf793a0f79ed892f4beafb251bc Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:35:29 +0100
Subject: [PATCH 1/5] Fix tab completion for ALTER ROLE|USER ... RESET

It was showing all the right variables when hitting tab just after
RESET, but as soon as you started typing something to pick what to
complete, it would no longer work.  Fix it in the same way as ALTER
DATABASE ... RESET does it.
---
 src/bin/psql/tab-complete.in.c | 12 +++++++++---
 1 file changed, 9 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index ec65ab79fec..82f82595abb 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1078,9 +1078,12 @@ Keywords_for_list_of_owner_roles, "PUBLIC"
 "  WHERE usename LIKE '%s'"
 
 #define Query_for_list_of_user_vars \
-" SELECT pg_catalog.split_part(pg_catalog.unnest(rolconfig),'=',1) "\
-"   FROM pg_catalog.pg_roles "\
-"  WHERE rolname LIKE '%s'"
+"SELECT conf FROM ("\
+"       SELECT rolname, pg_catalog.split_part(unnest(rolconfig),'=',1) conf"\
+"         FROM pg_catalog.pg_roles"\
+"       ) s"\
+"  WHERE s.conf like '%s' "\
+"    AND s.rolname LIKE '%s'"
 
 #define Query_for_list_of_access_methods \
 " SELECT amname "\
@@ -2495,7 +2498,10 @@ match_previous_words(int pattern_id,
 
 	/* ALTER USER,ROLE <name> RESET */
 	else if (Matches("ALTER", "USER|ROLE", MatchAny, "RESET"))
+	{
+		set_completion_reference(prev2_wd);
 		COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_user_vars, "ALL");
+	}
 
 	/* ALTER USER,ROLE <name> WITH */
 	else if (Matches("ALTER", "USER|ROLE", MatchAny, "WITH"))
-- 
2.49.0


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=0002-Add-tab-completion-for-ALTER-TABLE-.-ALTER-COLUMN-.-.patch

From 73e18d6e122431c7c8f8612bcccafd64e1ab94ff Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:39:15 +0100
Subject: [PATCH 2/5] Add tab completion for ALTER TABLE ... ALTER COLUMN ...
 RESET

Unlike SET, it only takes parenthesised attribute options.
---
 src/bin/psql/tab-complete.in.c | 10 +++++++---
 1 file changed, 7 insertions(+), 3 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 82f82595abb..ab35f1a32ce 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2884,9 +2884,13 @@ match_previous_words(int pattern_id,
 					  "STATISTICS", "STORAGE",
 		/* a subset of ALTER SEQUENCE options */
 					  "INCREMENT", "MINVALUE", "MAXVALUE", "START", "NO", "CACHE", "CYCLE");
-	/* ALTER TABLE ALTER [COLUMN] <foo> SET ( */
-	else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "(") ||
-			 Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET", "("))
+	/* ALTER TABLE ALTER [COLUMN] <foo> RESET */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "RESET") ||
+			 Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "RESET"))
+		COMPLETE_WITH("(");
+ 	/* ALTER TABLE ALTER [COLUMN] <foo> SET|RESET ( */
+	else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET|RESET", "(") ||
+			 Matches("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET|RESET", "("))
 		COMPLETE_WITH("n_distinct", "n_distinct_inherited");
 	/* ALTER TABLE ALTER [COLUMN] <foo> SET COMPRESSION */
 	else if (Matches("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET", "COMPRESSION") ||
-- 
2.49.0


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=0003-Add-tab-completion-for-ALTER-FOREIGN-TABLE-.-SET.patch

From c70810608bfe774348c1b4eed064444c3eaed464 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:41:29 +0100
Subject: [PATCH 3/5] Add tab completion for ALTER FOREIGN TABLE ... SET

The schema is the only thing that can be set for foreign tables.
---
 src/bin/psql/tab-complete.in.c | 4 ++++
 1 file changed, 4 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index ab35f1a32ce..a6db4c0d012 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -2413,6 +2413,10 @@ match_previous_words(int pattern_id,
 		COMPLETE_WITH("ADD", "ALTER", "DISABLE TRIGGER", "DROP", "ENABLE",
 					  "INHERIT", "NO INHERIT", "OPTIONS", "OWNER TO",
 					  "RENAME", "SET", "VALIDATE CONSTRAINT");
+	else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny, "SET"))
+		COMPLETE_WITH("SCHEMA");
+	else if (Matches("ALTER", "FOREIGN", "TABLE", MatchAny, "SET", "SCHEMA"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
 
 	/* ALTER INDEX */
 	else if (Matches("ALTER", "INDEX"))
-- 
2.49.0


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=0004-Remove-guard-against-generic-completion-after-ALTER-.patch

From 16851e126dbbb48717b59fb2f2062ff22d7b0138 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:45:30 +0100
Subject: [PATCH 4/5] Remove guard against generic completion after ALTER
 DATABASE ... RESET

Commit 9df8727c5067 added explicit handling for ALTER DATABASE
... RESET earlier in the code, so no need to guard against it here.
---
 src/bin/psql/tab-complete.in.c | 3 +--
 1 file changed, 1 insertion(+), 2 deletions(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index a6db4c0d012..8daf3ab71a3 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -4956,8 +4956,7 @@ match_previous_words(int pattern_id,
 /* SET, RESET, SHOW */
 	/* Complete with a variable name */
 	else if (TailMatches("SET|RESET") &&
-			 !TailMatches("UPDATE", MatchAny, "SET") &&
-			 !TailMatches("ALTER", "DATABASE", MatchAny, "RESET"))
+			 !TailMatches("UPDATE", MatchAny, "SET"))
 		COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
 										  "CONSTRAINTS",
 										  "TRANSACTION",
-- 
2.49.0


--=-=-=
Content-Type: text/x-diff
Content-Disposition: attachment;
 filename=0005-Improve-tab-completion-for-RESET.patch

From 249232172eaedd1c87939ca5474b5cf73676bff8 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Mon, 9 Jun 2025 20:48:43 +0100
Subject: [PATCH 5/5] Improve tab completion for RESET

Only complete variables that have been set in the current session,
plus the keywords ALL, ROLE and SESSION (which will subsequently be
completed with AUTHORIZATION).
---
 src/bin/psql/tab-complete.in.c | 14 +++++++++++++-
 1 file changed, 13 insertions(+), 1 deletion(-)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index 8daf3ab71a3..e28bf6886dd 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1039,6 +1039,12 @@ static const SchemaQuery Query_for_trigger_of_table = {
 " WHERE context IN ('user', 'superuser') "\
 "   AND pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
 
+#define Query_for_list_of_session_vars \
+"SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
+" WHERE context IN ('user', 'superuser') "\
+"   AND source = 'session' "\
+"   AND pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
+
 #define Query_for_list_of_show_vars \
 "SELECT pg_catalog.lower(name) FROM pg_catalog.pg_settings "\
 " WHERE pg_catalog.lower(name) LIKE pg_catalog.lower('%s')"
@@ -4954,8 +4960,14 @@ match_previous_words(int pattern_id,
 	/* naah . . . */
 
 /* SET, RESET, SHOW */
+	/* Complete with variables set in the current session */
+	else if (TailMatches("RESET"))
+		COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_session_vars,
+										  "ALL",
+										  "ROLE",
+										  "SESSION");
 	/* Complete with a variable name */
-	else if (TailMatches("SET|RESET") &&
+	else if (TailMatches("SET") &&
 			 !TailMatches("UPDATE", MatchAny, "SET"))
 		COMPLETE_WITH_QUERY_VERBATIM_PLUS(Query_for_list_of_set_vars,
 										  "CONSTRAINTS",
-- 
2.49.0


--=-=-=--