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.
