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