Re: pg_dump is broken for partition tablespaces

On 2019-Apr-10, Alvaro Herrera wrote:

> 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.)

Found a "solution" to this -- namely, to set the GUC default_tablespace
to the empty string temporarily, and teach ruleutils.c to attach
TABLESPACE clauses on index/constraint definitions only if they
are not in the database tablespace. That makes everything works
correctly. (I did have to patch psql to show tablespace for partitioned
indexes.)

However, because the tablespace to use for an index is determined at
phase 3 execution time (i.e. inside DefineIndex), look what happens in
certain weird cases:

create tablespace foo location '/tmp/foo';
set default_tablespace to foo;
alter table t add unique (b) ;
create index on t (a);

at this point, the indexes for "a" and "b" is in tablespace foo, which
is correct because that's the default tablespace.

However, if we do a type change *and add an index in the same command*,
then that index ends up in the wrong tablespace (namely the database
tablespace instead of default_tablespace):

alter table t alter a type bigint, add unique (c);

I'm not seeing any good way to fix this; I need the default tablespace
reset to only affect the index creations caused by the rewrite, but not
the ones used by different commands. I suppose forbidding ADD
CONSTRAINT subcommands together with ALTER COLUMN SET DATA TYPE would
not fly very far. (I'm not sure that's complete either: if you change
datatype so that a toast table is created, perhaps this action will
affect the location of said new toast table, also.)

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