Обсуждение: BUG #6581: pg_dumpall --no-tablespaces option still sets default tablespace

The following bug has been logged on the website:

Bug reference:      6581
Logged by:          Keith Fiske
Email address:      keith@omniti.com
PostgreSQL version: 9.1.3
Operating system:   Any
Description:=20=20=20=20=20=20=20=20

Using the --no-tablespaces option for pg_dumpall correctly stops tablespace
creation and each individual object from having its tablespace set. But if
the original database had its default tablespace changed, this is still
added at the beginning of the dump file:

ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace';

keith@omniti.com writes:
> Using the --no-tablespaces option for pg_dumpall correctly stops tablespace
> creation and each individual object from having its tablespace set. But if
> the original database had its default tablespace changed, this is still
> added at the beginning of the dump file:

> ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace';

Well, what the --no-tablespaces option does is suppress actions that are
related to tablespaces.  However, ALTER DATABASE SET options are just
dumped out by generic code (likewise for ALTER ROLE SET, function SET
options, yadda yadda).  I guess we could insert a hack that looked for
tablespace-related options and didn't print them, but it seems like it
would be a wart not a feature.

Also, the purpose of the option is to let you reload the dump into an
installation that hasn't got the same (or any) tablespaces.  ALTER SET
commands won't prevent that, they'll at worst result in some NOTICEs
during the reload.

In short, I'm not real sure this should be considered a bug.

            regards, tom lane

On Wed, Apr 11, 2012 at 2:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> keith@omniti.com writes:
>> Using the --no-tablespaces option for pg_dumpall correctly stops tablesp=
ace
>> creation and each individual object from having its tablespace set. But =
if
>> the original database had its default tablespace changed, this is still
>> added at the beginning of the dump file:
>
>> ALTER DATABASE dbname SET default_tablespace TO 'old_db_tablespace';
>
> Well, what the --no-tablespaces option does is suppress actions that are
> related to tablespaces. =A0However, ALTER DATABASE SET options are just
> dumped out by generic code (likewise for ALTER ROLE SET, function SET
> options, yadda yadda). =A0I guess we could insert a hack that looked for
> tablespace-related options and didn't print them, but it seems like it
> would be a wart not a feature.
>
> Also, the purpose of the option is to let you reload the dump into an
> installation that hasn't got the same (or any) tablespaces. =A0ALTER SET
> commands won't prevent that, they'll at worst result in some NOTICEs
> during the reload.
>
> In short, I'm not real sure this should be considered a bug.
>
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0regards, tom lane

Agreed it's not a serious bug and doesn't stop the restore during a
basic replay with psql. But besides the warning during restore, it
also causes warnings after the reload when you connect to the
database.

postgres=3D# \c dbname
WARNING:  invalid value for parameter "default_tablespace": "alldata1"
DETAIL:  Tablespace "alldata1" does not exist.
You are now connected to database "dbname" as user "postgres".
dbname=3D#

Again, easily fixed with running another ALTER after the database is
up and running. But figured I'd report it.

--
Keith Fiske
Database Administrator
OmniTI Computer Consulting, Inc.
443.325.1357 x251