public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: Julien Rouhaud <[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: Sun, 12 Mar 2023 15:46:52 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <20230311033232.g6jgkuzxdawepsbe@jrouhaud>
References: <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]>
	<20230311030154.pg63uspio35h4a6t@jrouhaud>
	<[email protected]>
	<20230311033232.g6jgkuzxdawepsbe@jrouhaud>

Julien Rouhaud <[email protected]> writes:
> The BEGIN + TRUNCATE is only there to avoid generating WAL records just in case
> the wal_level is minimal.  I don't remember if that optimization still exists,
> but if yes we could avoid doing that if the server's wal_level is replica or
> higher?  That's not perfect but it would help in many cases.

After thinking about this, it seems like a better idea is to skip the
TRUNCATE if we're doing load-via-partition-root.  In that case it's
clearly a dangerous thing to do regardless of deadlock worries, since
it risks discarding previously-loaded data that came over from another
partition.  (IOW this seems like an independent, pre-existing bug in
load-via-partition-root mode.)

The trick is to detect in pg_restore whether pg_dump chose to do
load-via-partition-root.  If we have a COPY statement we can fairly
easily examine it to see if the target table is what we expect or
something else.  However, if the table was dumped as INSERT statements
it'd be far messier; the INSERTs are not readily accessible from the
code that needs to make the decision.

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.)

An alternative could be to extend the archive TOC format to record
directly whether a given TABLE DATA object loads data via partition
root or normally.  Potentially we could do that without an archive
format break by defining te->defn for TABLE DATA to be empty for
normal dumps (as it is now) or something like "-- load via partition root"
for the load-via-partition-root case.  However, depending on examination
of the COPY command would already work for the vast majority of existing
archive files, so I feel like it might be the preferable choice.

Thoughts?

			regards, tom lane

view thread (7+ 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: <[email protected]>

* 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