Re: pg_dump is broken for partition tablespaces

On 2019-Apr-23, Alvaro Herrera wrote:

> I'm not sure yet that 100% of the patch is gone, but yes much of it
> would go away thankfully.

Of course, the part that fixes the bug that indexes move tablespace when
recreated by a rewriting ALTER TABLE is still necessary. Included in
the attached patch.

(I think it would be good to have the relation being complained about in
the error message, though that requires passing the name to
GetDefaultTablespace.)

> I do suggest we should raise an error if rule a3 hits and it mentions
> the database tablespace (I stupidly forgot this critical point in the
> previous email). I think astonishment is lesser that way.

As in the attached. When pg_default is the database tablespace, these
cases fail with the patch, as expected:

alvherre=# CREATE TABLE q (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE pg_default;
psql: ERROR: cannot specify default tablespace for partitioned relations

alvherre=# CREATE TABLE q (a int PRIMARY KEY USING INDEX TABLESPACE pg_default) PARTITION BY LIST (a);
psql: ERROR: cannot specify default tablespace for partitioned relations

alvherre=# SET default_tablespace TO 'pg_default';

alvherre=# CREATE TABLE q (a int PRIMARY KEY) PARTITION BY LIST (a) ;
psql: ERROR: cannot specify default tablespace for partitioned relations

alvherre=# CREATE TABLE q (a int PRIMARY KEY) PARTITION BY LIST (a) TABLESPACE foo;
psql: ERROR: cannot specify default tablespace for partitioned relations

alvherre=# CREATE TABLE q (a int PRIMARY KEY USING INDEX TABLESPACE foo) PARTITION BY LIST (a);
psql: ERROR: cannot specify default tablespace for partitioned relations

These cases work:

alvherre=# CREATE TABLE q (a int PRIMARY KEY USING INDEX TABLESPACE foo) PARTITION BY LIST (a) TABLESPACE foo;

alvherre=# SET default_tablespace TO ''; -- the default
alvherre=# CREATE TABLE q (a int PRIMARY KEY) PARTITION BY LIST (a);

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