Re: pg_dump is broken for partition tablespaces

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

On 2019-Mar-06, Andres Freund wrote:

> > I also find it far from clear that:
> > <listitem>
> > <para>
> > The <replaceable class="parameter">tablespace_name</replaceable> is the name
> > of the tablespace in which the new table is to be created.
> > If not specified,
> > <xref linkend="guc-default-tablespace"/> is consulted, or
> > <xref linkend="guc-temp-tablespaces"/> if the table is temporary. For
> > partitioned tables, since no storage is required for the table itself,
> > the tablespace specified here only serves to mark the default tablespace
> > for any newly created partitions when no other tablespace is explicitly
> > specified.
> > </para>
> > </listitem>
> > is handled correctly. The above says that the *specified* tablespaces -
> > which seems to exclude the default tablespace - is what's going to
> > determine what partitions use as their default tablespace. But in fact
> > that's not true, the partitioned table's pg_class.retablespace is set to
> > what default_tablespaces was at the time of the creation.
> I still think the feature as is doesn't seem to have very well defined
> behaviour.

I have just started looking into this issue. I'm not sure yet what's
the best fix; maybe for the specific case of partitioned tables and
indexes we should deviate from the ages-old behavior of storing zero
tablespace, if the tablespace is specified as the default one. But I
haven't written the code yet.

In the meantime, here's David's patch, rebased to current master and
with the pg_upgrade and pg_dump tests fixed to match the new partition
creation behavior.

> > If we instead did:
> >
> > CREATE TABLE public.listp1 (a integer
> > );
> >
> > ALTER TABLE public.list1 ATTACH PARTITION public.listp FOR VALUES IN (1);
> Isn't that a bit more expensive, because now the table needs to be
> scanned for maching the value? That's probably neglegible though, given
> it'd probably always empty.

I think it's always empty. In the standard case, there are two
transactions rather than one, so yeah it's a little bit more expensive.
Maybe we should make this conditional on there actually being an
important tablespace distinction to preserve.

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

Attachment Content-Type Size
v2-0001-Make-pg_dump-emit-ATTACH-PARTITION-instead-of-PAR.patch text/x-diff 9.8 KB

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2019-04-09 14:09:21 Re: Zedstore - compressed in-core columnar storage
Previous Message Zhichao Liu 2019-04-09 13:20:05 GSOC 2019 proposal 'WAL-G safety features'