Thread: pg_dumpall (7.3) 'public' schema bug

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.

I deleted the 'public' schema from my databases
in 7.3, now in 7.4 they are back.  I suppose it is remotely
possible that I really didn't delete 'public' in 7.3,
I can't recall the last time I looked, but I'm sure
I must have checked at the time I deleted public.

Ran pg_dumpall on 7.3.  After feeding the ouput to psql
on 7.4 I got:

$ psql babase
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
         \h for help with SQL commands
         \? for help on internal slash commands
         \g or terminate with semicolon to execute query
         \q to quit

babase=> select * from pg_namespace;
        nspname       | nspowner
|
nspacl

--------------------+----------+---------------------------------------------------------------------------------------------------------------
   pg_toast           |        1 |  pg_temp_1          |        1 |
pg_catalog         |        1 | {postgres=U*C*/postgres,=U/postgres}
   public             |        1 | {postgres=U*C*/postgres,=UC/postgres}
   information_schema |        1 | {postgres=U*C*/postgres,=U/postgres}
   babase             |      100 |
{babase_admin=U*C*/babase_admin,"group
babase_editors=U/babase_admin","group babase_readers=U/babase_admin"}
   sandbox            |      100 |
{babase_admin=U*C*/babase_admin,"group
babase_editors=UC/babase_admin","group babase_readers=U/babase_admin"}
(7 rows)


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

"Karl O. Pinc" <kop@meme.com> writes:
> I deleted the 'public' schema from my databases
> in 7.3, now in 7.4 they are back.

IMHO this is not a bug.  It is not pg_dump's charter to remove
system-created objects...

            regards, tom lane

On 2004.11.16 16:25 Tom Lane wrote:
> "Karl O. Pinc" <kop@meme.com> writes:
> > I deleted the 'public' schema from my databases
> > in 7.3, now in 7.4 they are back.
>
> IMHO this is not a bug.  It is not pg_dump's charter to remove
> system-created objects...

I can live with this, but I find the implication distasteful --
that there is no way to move a database  from one cluster
to another and have an exact copy.  Not even when both
clusters are running the same release.

I don't care that much about the behavior, it's easy enough
to delete 'public'.  I do think that a note should be
made in the administrator manual regards system upgrades
where pg_dump(all) scripts are given if this is going to be
the behavior.

Thanks for postgresql.

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