reload-through-the-top-parent switch the partition table

From: Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: reload-through-the-top-parent switch the partition table
Date: 2017-08-01 09:34:31
Message-ID: CAGPqQf0C1he087bz9xRBOGZBuESYz9X=Fp8Ca_g+TfHgAff75g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

https://www.postgresql.org/message-id/CA%2BTgmoZFn7TJ7QBsFat
nuEE%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,
Rushabh Lathia
www.EnterpriseDB.com

Attachment Content-Type Size
reload_through_root_inserts.sql text/x-sql 1.9 KB
reload_through_root_copy.sql text/x-sql 1.9 KB
reload_through_root_v1_0001.patch text/x-patch 6.6 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Etsuro Fujita 2017-08-01 09:41:33 Re: map_partition_varattnos() and whole-row vars
Previous Message Yugo Nagata 2017-08-01 09:30:07 Re: Incorrect comment of XLByteToSeg() and XLByteToPrevSeg()