Re: pg_dump is broken for partition tablespaces

On Mon, 15 Apr 2019 at 02:16, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> writes:
> > I'd say the fact that we populate reltablespace with 0 is a bug as
> > it's not going to do what they want after a dump/restore.
>
> Well, it's not really nice perhaps, but you cannot just put in some
> other concrete tablespace OID instead. What a zero there means is
> "use the database's default tablespace", and the point of it is that
> it still means that after the DB has been cloned with a different
> default tablespace. If we don't store 0 then we break
> "CREATE DATABASE ... TABLESPACE = foo".
>
> You could imagine using some special tablespace OID that has these
> semantics (*not* pg_default, but some new row in pg_tablespace).
> I'm not sure that that'd provide any functional improvement over
> using zero, but we could certainly entertain such a change if
> partitioned tables seem to need it.

The patch only changes that behaviour when the user does something like:

set default_tablespace = 'pg_default';
create table ... (...);

or:

create table ... (...) tablespace pg_default;

The 0 value is still maintained when the tablespace is not specified
or default_tablespace is an empty string.

The CREATE TABLE docs mention:

"The tablespace_name is the name of the tablespace in which the new
table is to be created. If not specified, default_tablespace is
consulted, or 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."

and the default_tablespace docs say:

"When default_tablespace is set to anything but an empty string, it
supplies an implicit TABLESPACE clause for CREATE TABLE and CREATE
INDEX commands that do not have an explicit one."

so the change just seems to be altering the code to follow the documents.

Alvaro is proposing to change this behaviour for partitioned tables
and indexes. I'm proposing not having that special case and just
changing it.

--
David Rowley http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services