Date: Sat, 11 Mar 2023 11:01:54 +0800
From: Julien Rouhaud <rjuju123@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andrew Dunstan <andrew@dunslane.net>,
	Alvaro Herrera <alvherre@alvh.no-ip.org>,
	David Rowley <dgrowleyml@gmail.com>, Peter Geoghegan <pg@bowt.ie>,
	Robert Haas <robertmhaas@gmail.com>,
	pgsql-hackers@lists.postgresql.org,
	Andrew <pgsqlhackers@andrewrepp.com>
Subject: Re: pg_dump versus hash partitioning
Message-ID: <20230311030154.pg63uspio35h4a6t@jrouhaud>
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>
 <1425446.1675291093@sss.pgh.pa.us>
 <CAApHDvqJM5HxhZSnxESENAHLxTZ=eXKKHxeHfvVe1E8v28woDA@mail.gmail.com>
 <1440373.1675299790@sss.pgh.pa.us>
 <a7ef6212-f9fe-6826-9640-5dac54327140@dunslane.net>
 <1533086.1675349554@sss.pgh.pa.us>
 <1912821.1676402493@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <1912821.1676402493@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/20230311030154.pg63uspio35h4a6t%40jrouhaud>
Precedence: bulk

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.