Thread: pg_dumpall and temp_tablespaces dependency problem

pg_dumpall and temp_tablespaces dependency problem

From
Heikki Linnakangas
Date:
create user foouser;
create tablespace temptblspc location '/tmp/tmptblspc';
alter user foouser set temp_tablespaces='temptblspc';

Run pg_dumpall. It will produce a dump like:

...
CREATE ROLE foouser;
ALTER ROLE foouser WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB 
LOGIN NOREPLICATION;
ALTER ROLE foouser SET temp_tablespaces TO 'temptblspc';
...
CREATE TABLESPACE temptblspc OWNER heikki LOCATION '/tmp/tmptblspc';

That latter ALTER ROLE statement fails at restore:

ERROR:  tablespace "temptblspc" does not exist

The problem here is that the ALTER ROLE statement refers to the 
tablespace, which is created afterwards. There's two possible solutions 
to this that I can see:

1. Teach pg_dumpall to dump the ALTER ROLE statement after creating 
tablespaces.

2. Relax the check on ALTER ROLE to not throw an error when you set 
temp_tablespaces to a non-existent tablespace.

There's another GUC that has the same problem: 
default_text_search_config. Only that is worse, because text search 
configurations are local to a database, so reordering the statements in 
the pg_dumpall output won't help. So I'm leaning towards option 2, also 
because moving the ALTER ROLE statement in the dump would make it less 
readable. Relaxing the check would be consistent with setting 
search_path, where you get a NOTICE rather than an ERROR if you refer to 
a non-existent schema in the ALTER ROLE statement.

Barring objections, I'll write a patch to relax the checking on 
default_text_search_config and temp_tablespaces to match search_path.

--   Heikki Linnakangas  EnterpriseDB   http://www.enterprisedb.com


Re: pg_dumpall and temp_tablespaces dependency problem

From
Tom Lane
Date:
Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:
> Barring objections, I'll write a patch to relax the checking on 
> default_text_search_config and temp_tablespaces to match search_path.

This seems like something that's going to come back again and again.
What do you think of changing things so that ALTER ROLE/DATABASE SET
*never* throw hard errors for bogus-seeming values?
        regards, tom lane