GPDB7 REGRESSION: External partitions isn't working as documented or as gpdb6 worked about gpdb HOT 5 CLOSED

@huansong @Chibin work together and see what follow-up is required for this one. New syntax usage is preferred for sure compared to old exchange as its better.

Though we should still know why old syntax didn't work - as expected to work (guessing something funky going on with partition name - didn't check the details).

from gpdb.

The below set of commands (using the new syntax) does work so maybe you can just change the documentation to reflect the new syntax and indicate the old syntax no longer is supported in this case:

externalize_part_step1:
create writable external table rp_tests.ext_rp_date_ext20150602 (like "rp_tests"."rp_date_ext") location ('gpfdist://localhost:8099/rp_tests_ext_rp_date_ext201506021714268232_3423479_0.gz') format 'text' (delimiter '|' null '')

externalize_part_step2:
insert into rp_tests.ext_rp_date_ext20150602 select * from rp_tests.rp_date_ext_1_prt_2

Rows Inserted: 100
externalize_part_step3:
create external table rp_tests.r_ext_rp_date_ext20150602 (like "rp_tests"."rp_date_ext") location ('gpfdist://localhost:8099/rp_tests_ext_rp_date_ext201506021714268232_3423479_0.gz') format 'text' (delimiter '|' null '')

externalize_part_step4:
alter table "rp_tests"."rp_date_ext" detach partition rp_tests.rp_date_ext_1_prt_2

externalize_part_step5:
alter table "rp_tests"."rp_date_ext" attach partition rp_tests.r_ext_rp_date_ext20150602 for values from ('2015-06-02') to ('2015-06-03')

externalize_part_step6.1:
drop table rp_tests.rp_date_ext_1_prt_2

externalize_part_step6.2:
drop external table rp_tests.ext_rp_date_ext20150602

Here is the link to the incorrect documentation in 7.1:

https://docs.vmware.com/en/VMware-Greenplum/7/greenplum-database/admin_guide-ddl-classic-partition.html#topic_yhz_gpn_qs

"Exchanging a Leaf Partition with an External Table"

from gpdb.

Just throwing this out here, I saw found this test that's similar?:

-- This used to work on GPDB 6 and below, but it is now rejected. ALTER
-- PARTITION is used to address subpartitions, it is not needed otherwise.
create table parttab_y (a int4, b int4) distributed by (a);
alter table parttab alter partition for (1) exchange partition for (1) with table parttab_y;
ERROR:  table "parttab_1_prt_1" is not partitioned

from gpdb.

Thanks for the report.

Yes ALTER PARTITION is supposed to be used in a multi-level partition hierarchy. As mentioned in doc:

If altering a partition deeper than the first level of partitions, use ALTER PARTITION clauses to specify which sub-partition in the hierarchy you want to alter

It actualy has the same meaning in 6X doc: https://docs.vmware.com/en/VMware-Greenplum/6/greenplum-database/ref_guide-sql_commands-ALTER_TABLE.html. But the query like you specified just somehow worked in 6X. So you are not the first one encountering this issue :) : #15055 (comment).

So if we take out ALTER PARTITION it would work:

alter table "rp_tests"."rp_date_ext"
exchange partition for ('2015-06-01')
with table rp_tests.r_ext_rp_date_ext20150601
without validation;

from gpdb.

from gpdb.