pg_dump/all doesn't output schemas correctly (v7.3.4)

From: Ben Grimm <ben(at)zaeon(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: pg_dump/all doesn't output schemas correctly (v7.3.4)
Date: 2003-09-04 12:59:37
Message-ID: 20030904125937.GA6187@zaeon.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

I haven't tried the 7.4 beta, so it may be fixed there - but in
7.3.4, pg_dumpall doesn't generate the commands to create schemas
in the right order. This bug may have been reported before, but
I saw no response to it in the lists.

Try this in a fresh database after an initdb:

template1=# create user test nocreatedb nocreateuser;
CREATE USER
template1=# create database testdb;
CREATE DATABASE
template1=# \c testdb
You are now connected to database testdb.
testdb=# create schema authorization test;
CREATE SCHEMA
testdb=# set session authorization test;
SET
testdb=# set search_path="test";
SET
testdb=# create table abc ();
CREATE TABLE
template1=# \q
testdb=# \q

$ pg_dumpall -U postgres
--
-- PostgreSQL database cluster dump
--

\connect "template1"

--
-- Users
--

DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0');

CREATE USER test WITH SYSID 100 NOCREATEDB NOCREATEUSER;

--
-- Groups
--

DELETE FROM pg_group;

--
-- Database creation
--

CREATE DATABASE testdb WITH OWNER = postgres TEMPLATE = template0 ENCODING = 'SQL_ASCII';

\connect template1
--
-- PostgreSQL database dump
--

--
-- TOC entry 2 (OID 1)
-- Name: DATABASE template1; Type: COMMENT; Schema: -; Owner:
--

COMMENT ON DATABASE template1 IS 'Default template database';

\connect testdb
--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'test';

--
-- TOC entry 2 (OID 16977)
-- Name: test; Type: SCHEMA; Schema: -; Owner: test
--

*************************************************************************
This will fail because user 'test' has not been granted
create on the database (which pg_dump also fails to output,
but that's a separate bug) It should create the schema as
the superuser, then switch to the use to create tables within
that schema.
*************************************************************************

CREATE SCHEMA test;

SET search_path = test, pg_catalog;

--
-- TOC entry 3 (OID 16978)
-- Name: abc; Type: TABLE; Schema: test; Owner: test
--

CREATE TABLE abc (
);

--
-- Data for TOC entry 4 (OID 16978)
-- Name: abc; Type: TABLE DATA; Schema: test; Owner: test
--

COPY abc FROM stdin;
\.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Stephan Szabo 2003-09-04 15:58:33 Re: Foreign key not working in some cases when using triggers
Previous Message Ben Grimm 2003-09-04 12:38:52 pg_dump/all doesn't output database ACLs (v7.3.4)

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeroen Ruigrok/asmodai 2003-09-04 14:02:19 Potential bug in ALTER TABLE?
Previous Message Andrew Dunstan 2003-09-04 12:32:52 Re: TCP/IP with 7.4 beta2 broken?