Horton

Re: pg_dump is broken for partition tablespaces

Alvaro Herrera <alvherre@2ndquadrant.com>

View thread View on postgr.es
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Andres Freund <andres@anarazel.de>
Cc: David Rowley <david.rowley@2ndquadrant.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2019-04-10T22:11:21Z
Lists: pgsql-hackers

Attachments

On 2019-Apr-10, Alvaro Herrera wrote:

> but the test immediately does this:
> 
> alter table at_partitioned alter column b type numeric using b::numeric;
> 
> and watch what happens!  (1663 is pg_default)
> 
> alvherre=# select relname, reltablespace from pg_class where relname like 'at_partitioned%';
>        relname        | reltablespace 
> ----------------------+---------------
>  at_partitioned       |             0
>  at_partitioned_a_idx |             0
>  at_partitioned_b_idx |          1663
> (3 filas)
> 
> Outrageous!

This is because ruleutils.c attaches a TABLESPACE clause when asked to
dump an index definition; and tablecmds.c uses ruleutils to deparse the
index definition into something that can be replayed via CREATE INDEX
commands (or ALTER TABLE ADD CONSTRAINT UNIQUE/PRIMARY KEY, if that's
the case.)

This patch (PoC quality) fixes that behavior, but I'm looking to see
what else it breaks.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Commits

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

  2. Fix tablespace inheritance for partitioned rels

  3. Make pg_dump emit ATTACH PARTITION instead of PARTITION OF

  4. Make sure ALTER TABLE preserves index tablespaces.