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

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Reply via email to