Horton

Re: pg_dump, ATTACH, and independently restorable child partitions

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@alvh.no-ip.org>
To: Justin Pryzby <pryzby@telsasoft.com>
Cc: David Rowley <drowley@postgresql.org>, pgsql-hackers@lists.postgresql.org
Date: 2020-11-07T02:18:35Z
Lists: pgsql-hackers 
On 2020-Oct-24, Justin Pryzby wrote:

> On Fri, Oct 23, 2020 at 12:29:40AM -0500, Justin Pryzby wrote:

> > Now that I look, it seems like this is calling PQexec(), which sends a single,
> > "simple" libpq message with:
> > |CREATE TABLE ..; ALTER TABLE .. ATTACH PARTITION;
> > ..which is transactional, so when the 2nd command fails, the CREATE is rolled back.
> > https://www.postgresql.org/docs/9.5/libpq-exec.html#LIBPQ-EXEC-MAIN
> 
> The easy fix is to add an explicit begin/commit.

Hmm, I think this throws a warning when used with "pg_restore -1",
right?  I don't think that's sufficient reason to discard the idea, but
it be better to find some other way.

I have no ideas ATM :-(

Commits

  1. pg_dump: label INDEX ATTACH ArchiveEntries with an owner.

  2. Dump ALTER TABLE ... ATTACH PARTITION as a separate ArchiveEntry.

  3. Make pg_dump emit ATTACH PARTITION instead of PARTITION OF (reprise)