Skip site navigation (1) Skip section navigation (2)

pg_dumpall and temp_tablespaces dependency problem

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgreSQL(dot)org>
Subject: pg_dumpall and temp_tablespaces dependency problem
Date: 2012-01-28 19:04:47
Message-ID: 4F2446CF.5080601@enterprisedb.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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

Responses

pgsql-hackers by date

Next:From: Kohei KaiGaiDate: 2012-01-28 19:20:58
Subject: Re: [v9.2] Add GUC sepgsql.client_label
Previous:From: Oleg BartunovDate: 2012-01-28 19:04:31
Subject: Re: TS: Limited cover density ranking

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group