COMMENT/SEC LABEL tab complete support suggests wrong SQL.

pg.ddx.io  pgsql-hackers@postgresql.org mailing list archive  
help / color / mirror / Atom feed

COMMENT/SEC LABEL tab complete support suggests wrong SQL.
2+ messages / 2 participants
[nested] [flat]

* COMMENT/SEC LABEL tab complete support suggests wrong SQL.
@ 2026-09-18 08:01  Kirill Reshke <reshkekirill@gmail.com>
  0 siblings, 1 reply; 2+ messages in thread

From: Kirill Reshke @ 2026-09-18 08:01 UTC (permalink / raw)
  To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>

HI!

I noticed an issue on tab completion with LARGE OBJECT support. I am
aware that tab complete support need not to be always-correct, but
this cases are simple, so decided to fix.

The psql tab completion for COMMENT ON and SECURITY LABEL ON suggests
"IS" immediately after "LARGE OBJECT", this produces invalid SQL:

  =# COMMENT ON LARGE OBJECT <TAB>
  -- now COMMENT ON LARGE OBJECT IS

The same bug exists for SECURITY LABEL. SEC LABEL also missed EVENT
TRIGGER/  FOREIGN TABLE/MATERIALIZED VIEW cases.

Same bug was fixed for GRANT/REVOKE in ff0bcb248e6.



Quick sum up:
  Before:
    COMMENT ON LARGE OBJECT <TAB> → IS (invalid SQL)
    SECURITY LABEL ON LARGE OBJECT <TAB> → IS (invalid SQL)
    SECURITY LABEL ON EVENT TRIGGER <TAB> → IS (invalid SQL)
    SECURITY LABEL ON FOREIGN TABLE <TAB> → IS (invalid SQL)
    SECURITY LABEL ON MATERIALIZED VIEW <TAB> → IS (invalid SQL)

  After:
    COMMENT ON LARGE OBJECT <TAB> → OID list (correct)
    SECURITY LABEL ON LARGE OBJECT <TAB> → OID list (correct)
    SECURITY LABEL ON EVENT TRIGGER <TAB> → trigger name list (correct)
    SECURITY LABEL ON FOREIGN TABLE <TAB> → foreign table list (correct)
    SECURITY LABEL ON MATERIALIZED VIEW <TAB> → matview list (correct)



-- 
Best regards,
Kirill Reshke

Attachments:

  [application/octet-stream] v1-0001-psql-Fix-tab-completion-for-COMMENT-SECURITY-LABE.patch (3.0K, ../../CALdSSPgcdsj9T3w0i1mc1ujWJGVJuQzGnnOxNNaG4QunP3hMpQ@mail.gmail.com/2-v1-0001-psql-Fix-tab-completion-for-COMMENT-SECURITY-LABE.patch)
  download | inline diff:
From 710474f3dcb41c6078751921566659d6ab0826dd Mon Sep 17 00:00:00 2001
From: reshke <reshke@double.cloud>
Date: Thu, 17 Sep 2026 22:58:36 +0300
Subject: [PATCH v1] psql: Fix tab completion for COMMENT/SECURITY LABEL on
 multi-word targets

COMMENT ON and SECURITY LABEL ON tab completion suggested IS
immediately after LARGE OBJECT, EVENT TRIGGER, FOREIGN TABLE,
and MATERIALIZED VIEW, without the required object name or OID.
---
 src/bin/psql/tab-complete.in.c | 24 ++++++++++++++++++++++++
 1 file changed, 24 insertions(+)

diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index e27a3e2208a..241a16723dc 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -1213,6 +1213,10 @@ Keywords_for_list_of_owner_roles, "PUBLIC"
 "   AND d.datname = pg_catalog.current_database() "\
 "   AND s.subdbid = d.oid"
 
+#define Query_for_list_of_large_objects \
+"SELECT oid FROM pg_catalog.pg_largeobject_metadata "\
+" WHERE oid::text LIKE '%s'"
+
 /* Privilege options shared between GRANT and REVOKE */
 #define Privilege_options_of_grant_and_revoke \
 "SELECT", "INSERT", "UPDATE", "DELETE", "TRUNCATE", "REFERENCES", "TRIGGER", \
@@ -3344,6 +3348,10 @@ match_previous_words(int pattern_id,
 		set_completion_reference(prev2_wd);
 		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables_for_trigger);
 	}
+	else if (Matches("COMMENT", "ON", "LARGE", "OBJECT"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_large_objects);
+	else if (Matches("COMMENT", "ON", "LARGE", "OBJECT", MatchAnyExcept("IS")))
+		COMPLETE_WITH("IS");
 	else if (Matches("COMMENT", "ON", MatchAny, MatchAnyExcept("IS")) ||
 			 Matches("COMMENT", "ON", MatchAny, MatchAny, MatchAnyExcept("IS")) ||
 			 Matches("COMMENT", "ON", MatchAny, MatchAny, MatchAny, MatchAnyExcept("IS")) ||
@@ -5185,6 +5193,22 @@ match_previous_words(int pattern_id,
 					  "LARGE OBJECT", "MATERIALIZED VIEW", "LANGUAGE",
 					  "PUBLICATION", "PROCEDURE", "ROLE", "ROUTINE", "SCHEMA",
 					  "SEQUENCE", "SUBSCRIPTION", "TABLESPACE", "TYPE", "VIEW");
+	else if (Matches("SECURITY", "LABEL", "ON", "EVENT", "TRIGGER"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_event_triggers);
+	else if (Matches("SECURITY", "LABEL", "ON", "EVENT", "TRIGGER", MatchAny))
+		COMPLETE_WITH("IS");
+	else if (Matches("SECURITY", "LABEL", "ON", "FOREIGN", "TABLE"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_foreign_tables);
+	else if (Matches("SECURITY", "LABEL", "ON", "FOREIGN", "TABLE", MatchAny))
+		COMPLETE_WITH("IS");
+	else if (Matches("SECURITY", "LABEL", "ON", "MATERIALIZED", "VIEW"))
+		COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_matviews);
+	else if (Matches("SECURITY", "LABEL", "ON", "MATERIALIZED", "VIEW", MatchAny))
+		COMPLETE_WITH("IS");
+	else if (Matches("SECURITY", "LABEL", "ON", "LARGE", "OBJECT"))
+		COMPLETE_WITH_QUERY(Query_for_list_of_large_objects);
+	else if (Matches("SECURITY", "LABEL", "ON", "LARGE", "OBJECT", MatchAny))
+		COMPLETE_WITH("IS");
 	else if (Matches("SECURITY", "LABEL", "ON", MatchAny, MatchAny))
 		COMPLETE_WITH("IS");
 
-- 
2.43.0



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: COMMENT/SEC LABEL tab complete support suggests wrong SQL.
@ 2026-09-18 08:27  Chao Li <li.evan.chao@gmail.com>
  parent: Kirill Reshke <reshkekirill@gmail.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Chao Li @ 2026-09-18 08:27 UTC (permalink / raw)
  To: Kirill Reshke <reshkekirill@gmail.com>; +Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>



> On Sep 18, 2026, at 16:01, Kirill Reshke <reshkekirill@gmail.com> wrote:
> 
> HI!
> 
> I noticed an issue on tab completion with LARGE OBJECT support. I am
> aware that tab complete support need not to be always-correct, but
> this cases are simple, so decided to fix.
> 
> The psql tab completion for COMMENT ON and SECURITY LABEL ON suggests
> "IS" immediately after "LARGE OBJECT", this produces invalid SQL:
> 
>  =# COMMENT ON LARGE OBJECT <TAB>
>  -- now COMMENT ON LARGE OBJECT IS
> 
> The same bug exists for SECURITY LABEL. SEC LABEL also missed EVENT
> TRIGGER/  FOREIGN TABLE/MATERIALIZED VIEW cases.
> 
> Same bug was fixed for GRANT/REVOKE in ff0bcb248e6.
> 
> 
> 
> Quick sum up:
>  Before:
>    COMMENT ON LARGE OBJECT <TAB> → IS (invalid SQL)
>    SECURITY LABEL ON LARGE OBJECT <TAB> → IS (invalid SQL)
>    SECURITY LABEL ON EVENT TRIGGER <TAB> → IS (invalid SQL)
>    SECURITY LABEL ON FOREIGN TABLE <TAB> → IS (invalid SQL)
>    SECURITY LABEL ON MATERIALIZED VIEW <TAB> → IS (invalid SQL)
> 
>  After:
>    COMMENT ON LARGE OBJECT <TAB> → OID list (correct)
>    SECURITY LABEL ON LARGE OBJECT <TAB> → OID list (correct)
>    SECURITY LABEL ON EVENT TRIGGER <TAB> → trigger name list (correct)
>    SECURITY LABEL ON FOREIGN TABLE <TAB> → foreign table list (correct)
>    SECURITY LABEL ON MATERIALIZED VIEW <TAB> → matview list (correct)
> 
> 
> 
> -- 
> Best regards,
> Kirill Reshke
> <v1-0001-psql-Fix-tab-completion-for-COMMENT-SECURITY-LABE.patch>

Do we also need to support SECURITY LABEL FOR provider ON LARGE OBJECT …?

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/










^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2026-09-18 08:27 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-18 08:01 COMMENT/SEC LABEL tab complete support suggests wrong SQL. Kirill Reshke <reshkekirill@gmail.com>
2026-09-18 08:27 ` Chao Li <li.evan.chao@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox