https://www.postgresql.org/message-id/CA%2BTgmoZFn7TJ7QBsFatnuEE%3DGYGdZSNXqr9489n5JBsdy5rFfA%40mail.gmail.com
Above thread, it's been pointed out as important consideration
about adding reload-through-the-top-parent switch the partition
table. One small step toward making use of hash function was
adding a switch into pg_dump which reload through the top
parent for the partition table.
Attach patch does the implementation for the same:
- Added switch reload-through-root: (Open for suggestion for the switch name)
- Fix dumpTableData to COPY to the Root table with the reload-through-root switch.
- Fix dumpTableData_insert - to generate the proper insert statement with the reload-through-root switch.
- Added switch reload-through-root into pg_dumpall
Testing:
- Performed test with multi level partition + different schema combination.
- Tested a case with parent and child attributes in different order.
Attaching the pg_dump output for below test with --reload-through-root for COPY and INSERTS.
Testcase:
create schema a;
create schema b;
create schema c;
create table a.t1 ( a int, b int ) partition by list(a);
create table b.t1_p1 partition of a.t1 FOR VALUES in ( 1, 2, 3) partition by list(b);
create table c.t1_p1_p1 partition of b.t1_p1 FOR VALUES in (10, 20 );
insert into a.t1 values ( 2 , 10 );
insert into a.t1 values ( 1 , 20 );
My colleague Robert and I had doubt about the order in of TABLE
and TABLE_DATA. We thought earlier that reload-thought-root might
might not solve the purpose which has been discussed in the above
mentioned thread. But later looking into code I realize the sort order
for DO_TABLE and DO_TABLE_DATA are different, so we don't need
to worry about that issue.
TODOs:
- Update documentation for pg_dump & pg_dumpall
Thanks,