Re: 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

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(at)oss(dot)nttdata(dot)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