The directive PREFIX_SUB_PARTITIONS does not work for MySQL or Oracle while exporting data

[rahulb-yb]

Hey team, We attempted to export the data from a MySQL (and Oracle) source which contains some tables having partitions and sub partitions. The data files for the tables containing sub partitions were exporting with these names while the directive PREFIX_SUB_PARTITIONS can be set to either 1 or 0: table name_subpartition name_data.sql

Shouldn't the exported data files be labelled with the format: table name_partition name_subpartition name_data.sql when PREFIX_SUB_PARTITIONS is set to 1?

This can cause issues when the subpartition names are not unique across partitions for the same table. Please do have a look at this. Thanks in advance!

[darold]

Please download and use latest development code I just pushed commit 2b1ddfb7f that completely rewrite the way partition and sub-partition are renamed. The PREFIX_PARTITION configuration directive have been renamed into RENAME_PARTITION. PREFIX_SUB_PARTITIONS is no more used. You will have to recreate the partition using the export type PARTITION and use COPY again to import the data.

[rahulb-yb]

Hey @darold , thanks for the response, I tried out the latest development code and found that the changes being talked about are only reflected on the schema side of export. The data files still contain their old names, and if the data file corresponds to a subpartition, the file appears to not have been renamed appropriately, and ora2pg exits. For example, here is a fraction of the schema file corresponding to the partitions:

CREATE TABLE donations_part1 PARTITION OF donations
FOR VALUES IN ('eur')
PARTITION BY RANGE (amount);
CREATE TABLE donations_part1_subpart1 PARTITION OF donations_part1
FOR VALUES FROM (MINVALUE) TO (350);
CREATE TABLE donations_part1_subpart2 PARTITION OF donations_part1
FOR VALUES FROM (MINVALUE) TO (1000);

This file contains the name changes you mentioned earlier. However, when we export data (I used the type COPY for exporting data), here is what the file names look like, along with one of the file contents as a sample:

[centos@ip-xx-xx-xx-xx ora2pgtest]$ tree data
data
├── data.sql
├── DONATIONS_data.sql
├── DONATIONS_P_DEFAULT_data.sql
├── PARTITIONING_TEST_data.sql
├── PARTITIONING_TEST_P0_data.sql
├── PARTITIONING_TEST_P1_data.sql
├── PARTITIONING_TEST_P2_data.sql
├── PARTITIONING_TEST_P3_data.sql
├── PARTITIONING_TEST_P4_data.sql
├── SUBPARTITIONING_TEST_data.sql
├── tmp_DONATIONS_P_AUD_NZD_CHF_HIGH_data.sql
├── tmp_DONATIONS_P_AUD_NZD_CHF_MEDIUM_data.sql
├── tmp_DONATIONS_P_EUR_HIGH_data.sql
├── tmp_DONATIONS_P_EUR_MEDIUM_data.sql
├── tmp_DONATIONS_P_GBP_HIGH_data.sql
├── tmp_DONATIONS_P_GBP_MEDIUM_data.sql
├── tmp_DONATIONS_P_INR_HIGH_data.sql
├── tmp_DONATIONS_P_INR_MEDIUM_data.sql
├── tmp_DONATIONS_P_JPY_HIGH_data.sql
├── tmp_DONATIONS_P_JPY_MEDIUM_data.sql
├── tmp_DONATIONS_P_ZAR_HIGH_data.sql
├── tmp_DONATIONS_P_ZAR_MEDIUM_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1260_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1261_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1262_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1263_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1264_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1265_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1266_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1267_data.sql
├── tmp_SUBPARTITIONING_TEST_SYS_SUBP1268_data.sql
└── tmp_SUBPARTITIONING_TEST_SYS_SUBP1269_data.sql

Contents of data.sql:

[centos@ip-xx-xx-xx-xx ora2pgtest]$ cat data/data.sql 

BEGIN;

\i './data/DONATIONS_data.sql'
\i './data/DONATIONS_part1_subpart1_data.sql'
\i './data/DONATIONS_part1_subpart2_data.sql'
\i './data/DONATIONS_part2_subpart1_data.sql'
\i './data/DONATIONS_part2_subpart2_data.sql'
\i './data/DONATIONS_part3_subpart1_data.sql'
\i './data/DONATIONS_part3_subpart2_data.sql'
\i './data/DONATIONS_part4_subpart1_data.sql'
\i './data/DONATIONS_part4_subpart2_data.sql'
\i './data/DONATIONS_part5_subpart1_data.sql'
\i './data/DONATIONS_part5_subpart2_data.sql'
\i './data/DONATIONS_part6_subpart1_data.sql'
\i './data/DONATIONS_part6_subpart2_data.sql'
\i './data/DONATIONS_part_default_data.sql'
\i './data/PARTITIONING_TEST_data.sql'
\i './data/PARTITIONING_TEST_part1_data.sql'
\i './data/PARTITIONING_TEST_part2_data.sql'
\i './data/PARTITIONING_TEST_part3_data.sql'
\i './data/PARTITIONING_TEST_part4_data.sql'
\i './data/PARTITIONING_TEST_part5_data.sql'
\i './data/SUBPARTITIONING_TEST_data.sql'
\i './data/SUBPARTITIONING_TEST_part1_subpart1_data.sql'
\i './data/SUBPARTITIONING_TEST_part1_subpart2_data.sql'
\i './data/SUBPARTITIONING_TEST_part2_subpart1_data.sql'
\i './data/SUBPARTITIONING_TEST_part2_subpart2_data.sql'
\i './data/SUBPARTITIONING_TEST_part3_subpart1_data.sql'
\i './data/SUBPARTITIONING_TEST_part3_subpart2_data.sql'
\i './data/SUBPARTITIONING_TEST_part4_subpart1_data.sql'
\i './data/SUBPARTITIONING_TEST_part4_subpart2_data.sql'
\i './data/SUBPARTITIONING_TEST_part5_subpart1_data.sql'
\i './data/SUBPARTITIONING_TEST_part5_subpart2_data.sql'

COMMIT;

Contents of one of the partition-related data files:

[centos@ip-xx-xx-xx-xx ora2pgtest]$ cat data/PARTITIONING_TEST_P0_data.sql 

COPY partitioning_test_part1 (bill_no,sale_date,cust_code,amount) FROM STDIN;
1   2000-01-02 00:00:00 C001    125.56
\.

Contents of one of the sub partition-related data files:

[centos@ip-xx-xx-xx-xx ora2pgtest]$ cat data/tmp_SUBPARTITIONING_TEST_SYS_SUBP1260_data.sql 

COPY subpartitioning_test_part1_subpart1 (bill_no,sale_date,cust_code,amount) FROM STDIN;
\.

We can see that the expected filenames do not match with the table names which are brought about with this change, when it comes to the data-related operations.

[rahulb-yb]

TLDR for the last comment: Thanks for the changes Darold, I tried out the latest code and found this: The names of the data files still follow the old format. You can refer to the examples provided in the earlier comment

[darold]

Commit ab13cf9 fixes this issue. Actually I prefer to keep the original table and partition name in the output file to be able to see which partition data have been exported for the renamed PG partition.