Re: pg_dump is broken for partition tablespaces

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: David Rowley <david(dot)rowley(at)2ndquadrant(dot)com>, 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-17 22:06:00
Message-ID: 20190417220600.GA26716@alvherre.pgsql
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

On 2019-Apr-17, Tom Lane wrote:

> Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> > 1. pg_dump now uses regular CREATE TABLE followed by ALTER TABLE / ATTACH
> > PARTITION when creating partitions, rather than CREATE TABLE
> > PARTITION OF. pg_dump --binary-upgrade was already doing that, so
> > this part mostly removes some code. In order to make the partitions
> > reach the correct tablespace, the "default_tablespace" GUC is used.
> > No TABLESPACE clause is added to the dump. This is David's patch
> > near the start of the thread.
> This idea seems reasonable independently of all else, simply on the grounds
> of reducing code duplication. It also has the advantage that if you try
> to do a selective restore of just a partition, and the parent partitioned
> table isn't around, you can still do it (with an ignorable error).

I'll get this part pushed, then.

> > 2. When creating a partition using the CREATE TABLE PARTITION OF syntax,
> > the TABLESPACE clause has highest precedence; if that is not given,
> > the partitioned table's tablespace is used; if that is set to 0 (the
> > default), default_tablespace is used; if that's set to empty or a
> > nonexistant tablespace, the database's default tablespace is used.
> > This is (I think) what Andres proposed in
> >
> Hmm. The precedence order between the second and third options seems
> pretty arbitrary and hence unrememberable. I don't say this choice is
> wrong, but it's not clear that it's right, either.

Well, I see it as the default_tablespace being a global setting whereas
the parent is "closer" to the partition definition, which is why it has
higher priority. I don't have a strong opinion however (and I think the
patch would be shorter if default_tablespace had higher precedence.)

Maybe others care to comment?

> > 3. Partitioned relations can have the database tablespace in
> > pg_class.reltablespace, as opposed to storage-bearing relations which
> > cannot. This is useful to be able to put partitions in the database
> > tablespace even if the default_tablespace is set to something else.
> I still feel that this is a darn bad idea. It goes against the rule
> that's existed for pg_class.reltablespace since its beginning, and
> I think it's inevitable that that's going to break something.

Yes, this deviates from current practice, and while I tested this in as
many ways as I could think of, I cannot deny that it might break
something unexpectedly.

Here's a v4 btw, which is just some adjustments to the regress test
script and expected file.

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

Attachment Content-Type Size
v4-0001-psql-display-tablespace-for-partitioned-indexes.patch text/x-diff 2.6 KB
v4-0002-Make-pg_dump-emit-ATTACH-PARTITION-instead-of-PAR.patch text/x-diff 9.8 KB
v4-0003-fix-behavior-to-consider-default_tablespace.patch text/x-diff 45.6 KB

In response to


Browse pgsql-hackers by date

  From Date Subject
Next Message Stephen Frost 2019-04-17 22:43:10 Re: block-level incremental backup
Previous Message Tom Lane 2019-04-17 21:57:14 Re: pgsql: Fix unportable code in pgbench.