public inbox for [email protected]
help / color / mirror / Atom feedFrom: Julien Rouhaud <[email protected]>
To: Tom Lane <[email protected]>
Cc: Andrew Dunstan <[email protected]>
Cc: Alvaro Herrera <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Peter Geoghegan <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: [email protected]
Cc: Andrew <[email protected]>
Subject: Re: pg_dump versus hash partitioning
Date: Sat, 11 Mar 2023 11:01:54 +0800
Message-ID: <20230311030154.pg63uspio35h4a6t@jrouhaud> (raw)
In-Reply-To: <[email protected]>
References: <CA+TgmoYoSoX8bN_yzabmRDQBNxEaWmjBKPnkZPMUDpPTBy=d4A@mail.gmail.com>
<CAH2-WzkjKCmVD9JDKHx09YC+c5kSL_1rHH0kRMyvDHRFoxgw6Q@mail.gmail.com>
<CA+TgmoYJ+3XpW_iYapXN3gwq2MECi7GH8BxuwKFCQPOzLaa3OQ@mail.gmail.com>
<CAH2-Wz=cMRoYHeoac7kscdDNBSYzHKgMvSHX5CFEoE66rimk8A@mail.gmail.com>
<[email protected]>
<CAApHDvqJM5HxhZSnxESENAHLxTZ=eXKKHxeHfvVe1E8v28woDA@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Tue, Feb 14, 2023 at 02:21:33PM -0500, Tom Lane wrote:
> Here's a set of draft patches around this issue.
>
> 0001 does what I last suggested, ie force load-via-partition-root for
> leaf tables underneath a partitioned table with a partitioned-by-hash
> enum column. It wasn't quite as messy as I first feared, although we do
> need a new query (and pg_dump now knows more about pg_partitioned_table
> than it used to).
>
> I was a bit unhappy to read this in the documentation:
>
> It is best not to use parallelism when restoring from an archive made
> with this option, because <application>pg_restore</application> will
> not know exactly which partition(s) a given archive data item will
> load data into. This could result in inefficiency due to lock
> conflicts between parallel jobs, or perhaps even restore failures due
> to foreign key constraints being set up before all the relevant data
> is loaded.
>
> This made me wonder if this could be a usable solution at all, but
> after thinking for awhile, I don't see how the claim about foreign key
> constraints is anything but FUD. pg_dump/pg_restore have sufficient
> dependency logic to prevent that from happening. I think we can just
> drop the "or perhaps ..." clause here, and tolerate the possible
> inefficiency as better than failing.
Working on some side project that can cause dump of hash partitions to be
routed to a different partition, I realized that --load-via-partition-root can
indeed cause deadlock in such case without FK dependency or anything else.
The problem is that each worker will perform a TRUNCATE TABLE ONLY followed by
a copy of the original partition's data in a transaction, and that obviously
will lead to deadlock if the original and locked partition and the restored
partition are different.
view thread (24+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
Subject: Re: pg_dump versus hash partitioning
In-Reply-To: <20230311030154.pg63uspio35h4a6t@jrouhaud>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox