Re: pg_dump is broken for partition tablespaces

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>
Cc: Andres Freund <andres(at)anarazel(dot)de>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: pg_dump is broken for partition tablespaces
Date: 2019-04-09 22:34:35
Message-ID: 20190409223435.GA14335@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2019-Apr-09, Alvaro Herrera wrote:

> However, in order to fix the pg_dump behavior for the partitioned rel,
> we would need to emit the tablespace differently, i.e. not use SET
> default_tablespace, but instead attach the tablespace clause to the
> I'll go have a look at what problems are there with doing that.

I tried with the attached tbspc-partitioned.patch (on top of the
previous patch). It makes several additional cases work correctly, but
causes much more pain than it fixes:

1. if a partitioned table is created without a tablespace spec, then
pg_dump dumps it with a clause like TABLESPACE "".

2. If you fix that to make pg_dump omit the tablespace clause if the
tablespace is default, then there's no SET nor TABLESPACE clauses, so
the table is created in the tablespace of the previously dumped table.

3. You can probably make the above work anyway with enough hacks, but
by the time you're finished, the code stinks like months-only fish.

4. Even if you ignore the odor, all the resulting CREATE TABLE commands
will fail if you run them in a database that doesn't contain the
tablespaces in question. That is, it negates one of the main points of
using "SET default_tablespace" in the first place.

I therefore decree that this approach is not a solution and never will

Álvaro Herrera
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Attachment Content-Type Size
tbspc-partitioned.patch text/x-diff 4.2 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Alvaro Herrera 2019-04-09 22:58:42 Re: pg_dump is broken for partition tablespaces
Previous Message Alvaro Herrera 2019-04-09 21:28:38 Re: block-level incremental backup