pg_dumpall (7.3) two search_path schema bugs - Mailing list pgsql-bugs

Hi,

Went to upgrade from postgresql (RedHat's postgresql
rh-postgresql-7.3.6-7) to Fedora core 3 postgresql
7.4.6-1 and encountered a problem.  If nothing else this
is worth a note on the 7.4 upgrade doc page.

It appears as though pg_dumpall is setting the search_path
runtime variable in the databases before it creates the
schemas.  Further, it appears as though the ALTER
DATABASE command used to set the search path does
not have the quotes correct.  (I used
alter database babase_test set search_path to babase, sandbox, '$user';
the ALTER DATABASE written by pg_dumpall does
not work.)

Ran pg_dumpall on 7.3.  When the input was fed to
psql on 7.4 I got the following errors:

CREATE DATABASE
ALTER DATABASE
psql:7.3.dump:34: ERROR:  unrecognized time zone name: "Nairobi"
psql:7.3.dump:35: ERROR:  schema "babase, sandbox, "$user"" does not
exist
CREATE DATABASE
ALTER DATABASE
psql:7.3.dump:38: ERROR:  unrecognized time zone name: "Nairobi"
psql:7.3.dump:39: ERROR:  schema "babase, sandbox, "$user"" does not
exist
CREATE DATABASE
ALTER DATABASE
psql:7.3.dump:42: ERROR:  unrecognized time zone name: "Nairobi"
psql:7.3.dump:43: ERROR:  schema "babase, sandbox, "$user"" does not
exist
You are now connected to database "babase".
SET
CREATE SCHEMA
CREATE SCHEMA

The output of the pg_dumpall is:

ALTER DATABASE babase SET "DateStyle" TO 'European';
ALTER DATABASE babase SET "TimeZone" TO 'Nairobi';
ALTER DATABASE babase SET search_path TO 'babase, sandbox, "$user"';
CREATE DATABASE babase_copy WITH OWNER = babase_admin TEMPLATE =
template0 ENCOD
ING = 'SQL_ASCII';
ALTER DATABASE babase_copy SET "DateStyle" TO 'European';
ALTER DATABASE babase_copy SET "TimeZone" TO 'Nairobi';
ALTER DATABASE babase_copy SET search_path TO 'babase, sandbox,
"$user"';
CREATE DATABASE babase_test WITH OWNER = babase_admin TEMPLATE =
template0 ENCOD
ING = 'SQL_ASCII';
ALTER DATABASE babase_test SET "DateStyle" TO 'European';
ALTER DATABASE babase_test SET "TimeZone" TO 'Nairobi';
ALTER DATABASE babase_test SET search_path TO 'babase, sandbox,
"$user"';


\connect babase
--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'babase_admin';

--
-- TOC entry 2 (OID 16979)
-- Name: babase; Type: SCHEMA; Schema: -; Owner: babase_admin
--

CREATE SCHEMA babase;


--
-- TOC entry 4 (OID 16980)
-- Name: sandbox; Type: SCHEMA; Schema: -; Owner: babase_admin
--

CREATE SCHEMA sandbox;



Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                    -- Robert A. Heinlein