Re: pg_dump versus hash partitioning

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Julien Rouhaud <rjuju123(at)gmail(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, David Rowley <dgrowleyml(at)gmail(dot)com>, Peter Geoghegan <pg(at)bowt(dot)ie>, Robert Haas <robertmhaas(at)gmail(dot)com>, pgsql-hackers(at)lists(dot)postgresql(dot)org, Andrew <pgsqlhackers(at)andrewrepp(dot)com>
Subject: Re: pg_dump versus hash partitioning
Date: 2023-03-13 23:39:12
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Julien Rouhaud <rjuju123(at)gmail(dot)com> writes:
> On Sun, Mar 12, 2023 at 03:46:52PM -0400, Tom Lane wrote:
>> The trick is to detect in pg_restore whether pg_dump chose to do
>> load-via-partition-root.

> Given that this approach wouldn't help with existing dump files (at least if
> using COPY, in any case the one using INSERT are doomed), so I'm slightly in
> favor of the first approach, and later add an easy and non magic incantation
> way to produce dumps that don't depend on partitioning.

Yeah, we need to do both. Attached find an updated patch series:

0001: TAP test that exhibits both this deadlock problem and the
different-hash-codes problem. I'm not sure if we want to commit
this, or if it should be in exactly this form --- the second set
of tests with a manual --load-via-partition-root switch will be
pretty redundant after this patch series.

0002: Make pg_restore detect load-via-partition-root by examining the
COPY commands embedded in the dump, and skip the TRUNCATE if so,
thereby fixing the deadlock issue. This is the best we can do for
legacy dump files, I think, but it should be good enough.

0003: Also detect load-via-partition-root by adding a label in the
dump. This is a more bulletproof solution going forward.

0004-0006: same as previous patches, but rebased over these.
This gets us to a place where the new TAP test passes.

I've not done anything about modifying the documentation, but I still
think we could remove the warning label on --load-via-partition-root.

regards, tom lane

Attachment Content-Type Size
v2-0001-Create-a-test-case-for-dump-and-restore-of-hashed.patch text/x-diff 2.9 KB
v2-0002-Avoid-TRUNCATE-when-restoring-load-via-partition-.patch text/x-diff 4.5 KB
v2-0003-Add-a-marker-to-TABLE-DATA-items-using-load-via-p.patch text/x-diff 4.0 KB
v2-0004-Force-load-via-partition-root-for-hash-partitioni.patch text/x-diff 9.0 KB
v2-0005-Don-t-create-useless-TableAttachInfo-objects.patch text/x-diff 1.5 KB
v2-0006-Simplify-pg_dump-s-creation-of-parent-table-links.patch text/x-diff 6.7 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2023-03-13 23:40:57 Re: pg_dump/pg_restore: Fix stdin/stdout handling of custom format on Win32
Previous Message Jeff Davis 2023-03-13 23:39:04 ICU 54 and earlier are too dangerous