Tab completion for ATTACH PARTITION
Hi,
Currently, the psql's tab completion feature does not support properly
for ATTACH PARTITION. When <TAB> key is typed after "ALTER TABLE
<table_name> ATTACH PARTITION ", all possible table names should be
displayed, however, foreign table names are not displayed. So I created
a patch that addresses this issue by ensuring that psql displays not
only normal table names but also foreign table names in this case.
Any kind of feedback is appreciated.
Best,
Tung Nguyen
Attachments:
v1-0001-Tab-completion-for-ATTACH-PARTITION.patchtext/x-diff; name=v1-0001-Tab-completion-for-ATTACH-PARTITION.patchDownload
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 779fdc90cb..174cf5e1dd 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -865,6 +865,18 @@ static const SchemaQuery Query_for_list_of_truncatables = {
.result = "c.relname",
};
+/* Relations supporting ATTACH PARTITION */
+static const SchemaQuery Query_for_list_of_attachables = {
+ .catname = "pg_catalog.pg_class c",
+ .selcondition =
+ "c.relkind IN (" CppAsString2(RELKIND_RELATION) ", "
+ CppAsString2(RELKIND_FOREIGN_TABLE) ", "
+ CppAsString2(RELKIND_PARTITIONED_TABLE) ")",
+ .viscondition = "pg_catalog.pg_table_is_visible(c.oid)",
+ .namespace = "c.relnamespace",
+ .result = "c.relname",
+};
+
/* Relations supporting GRANT are currently same as those supporting SELECT */
#define Query_for_list_of_grantables Query_for_list_of_selectables
@@ -2567,7 +2579,7 @@ psql_completion(const char *text, int start, int end)
* tables.
*/
else if (Matches("ALTER", "TABLE", MatchAny, "ATTACH", "PARTITION"))
- COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables);
+ COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_attachables);
/* Limited completion support for partition bound specification */
else if (TailMatches("ATTACH", "PARTITION", MatchAny))
COMPLETE_WITH("FOR VALUES", "DEFAULT"); 
On 2023-Sep-13, bt23nguyent wrote:
Hi,
Currently, the psql's tab completion feature does not support properly for
ATTACH PARTITION. When <TAB> key is typed after "ALTER TABLE <table_name>
ATTACH PARTITION ", all possible table names should be displayed, however,
foreign table names are not displayed. So I created a patch that addresses
this issue by ensuring that psql displays not only normal table names but
also foreign table names in this case.
Sounds reasonable, but I think if we're going to have a specific query
for this case, we should make it a lot more precise. For example, any
relation that's already a partition cannot be attached; as can't any
relation that is involved in legacy inheritance as either parent or
child.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"This is a foot just waiting to be shot" (Andrew Dunstan)
On 2023-09-13 12:19 a.m., Alvaro Herrera wrote:
On 2023-Sep-13, bt23nguyent wrote:
Hi,
Currently, the psql's tab completion feature does not support properly for
ATTACH PARTITION. When <TAB> key is typed after "ALTER TABLE <table_name>
ATTACH PARTITION ", all possible table names should be displayed, however,
foreign table names are not displayed. So I created a patch that addresses
this issue by ensuring that psql displays not only normal table names but
also foreign table names in this case.Sounds reasonable, but I think if we're going to have a specific query
for this case, we should make it a lot more precise. For example, any
relation that's already a partition cannot be attached; as can't any
relation that is involved in legacy inheritance as either parent or
child.
I applied the patch and performed below tests. I think it would be
better if "attach partition" can filter out those partitions which has
already been attached, just like "detach partition" is capable to filter
out the partitions which has already been detached.
Here are my test steps and results:
### create a main PG cluster on port 5432 and run below commands:
CREATE EXTENSION postgres_fdw;
CREATE SERVER s1 FOREIGN DATA WRAPPER postgres_fdw OPTIONS (dbname
'postgres', host '127.0.0.1', port '5433');
CREATE USER MAPPING for david SERVER s1 OPTIONS(user 'david');
CREATE TABLE t (a INT, b TEXT) PARTITION BY RANGE (a);
CREATE TABLE t_local PARTITION OF t FOR VALUES FROM (1) TO (10);
CREATE FOREIGN TABLE t_s1 PARTITION OF t FOR VALUES FROM (11) TO (20)
SERVER s1 OPTIONS(schema_name 'public', table_name 't');
CREATE FOREIGN TABLE t_s1 SERVER s1 OPTIONS(schema_name 'public',
table_name 't');
### create a foreign PG cluster on port 5433 and run below command:
CREATE TABLE t (a INT, b TEXT);
### "detach partition" can filter out already detached partition, in
this case, "t_local".
postgres=# alter table t detach partition
information_schema. public. t_local t_s1
postgres=# alter table t detach partition t_s1 ;
ALTER TABLE
postgres=# alter table t detach partition
information_schema. public. t_local
## before patch, "attach partition" can't display foreign table;
postgres=# alter table t attach partition
information_schema. public. t t_local
### after patch, "attach partition" dose display the foreign table
(patch works).
postgres=# alter table t attach partition
information_schema. public. t t_local t_s1
In both cases, the already attached partition "t_local" shows up. If it
can be filtered out then I believe better user experience.
Best regards,
David
On Wed, 13 Sept 2023 at 06:57, bt23nguyent <bt23nguyent@oss.nttdata.com> wrote:
Hi,
Currently, the psql's tab completion feature does not support properly
for ATTACH PARTITION. When <TAB> key is typed after "ALTER TABLE
<table_name> ATTACH PARTITION ", all possible table names should be
displayed, however, foreign table names are not displayed. So I created
a patch that addresses this issue by ensuring that psql displays not
only normal table names but also foreign table names in this case.Any kind of feedback is appreciated.
I have changed the status of the commitfest entry to RWF as Alvaro's
comments have not yet been addressed. Kindly post an updated version
by addressing the comments and add a new commitfest entry for the
same.
Regards,
Vignesh