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

Search Discussions

  • Tom Lane at Jan 29, 2012 at 1:06 am

    Heikki Linnakangas 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedJan 28, '12 at 7:05p
activeJan 29, '12 at 1:06a
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Tom Lane: 1 post Heikki Linnakangas: 1 post

People

Translate

site design / logo © 2021 Grokbase