From: Tom Lane <tgl@sss.pgh.pa.us>
To: Justin Pryzby <pryzby@telsasoft.com>
cc: Julien Rouhaud <rjuju123@gmail.com>, 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
In-reply-to: <ZA4uCu/SvHqCdv75@telsasoft.com>
References: <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> <20230311030154.pg63uspio35h4a6t@jrouhaud> <1078804.1678504214@sss.pgh.pa.us> <20230311033232.g6jgkuzxdawepsbe@jrouhaud> <1602203.1678650412@sss.pgh.pa.us> <ZA4uCu/SvHqCdv75@telsasoft.com>
Comments: In-reply-to Justin Pryzby <pryzby@telsasoft.com>
	message dated "Sun, 12 Mar 2023 14:54:50 -0500"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1604681.1678651363.1@sss.pgh.pa.us>
Date: Sun, 12 Mar 2023 16:02:43 -0400
Message-ID: <1604682.1678651363@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1604682.1678651363%40sss.pgh.pa.us>
Precedence: bulk

Justin Pryzby <pryzby@telsasoft.com> writes:
> On Sun, Mar 12, 2023 at 03:46:52PM -0400, Tom Lane wrote:
>> What I propose we do about that is further tweak things so that
>> load-via-partition-root forces dumping via COPY.  AFAIK the only
>> compelling use-case for dump-as-INSERTs is in transferring data
>> to a non-Postgres database, which is a context in which dumping
>> partitioned tables as such is pretty hopeless anyway.  (I wonder if
>> we should have some way to dump all the contents of a partitioned
>> table as if it were unpartitioned, to support such migration.)

> I think that what this other thread is about.
> https://commitfest.postgresql.org/42/4130/
> pg_dump all child tables with the root table

As far as I understood (didn't actually read the latest patch) that
one is just about easily selecting all the partitions of a partitioned
table when doing a selective dump.  It's not helping you produce a
non-Postgres-specific dump.

Although I guess by combining load-via-partition-root, data-only mode,
and dump-as-inserts you could produce a clean collection of
non-partition-dependent INSERT commands ... so maybe we'd better not
force dump-as-inserts off.  I'm starting to like the te->defn hack
more.

			regards, tom lane