There was some discussion today about restoring pg_dump output as a
non-superuser:
http://archives.postgresql.org/pgsql-admin/2008-01/msg00128.php
In 8.3 we have eliminated one of the major roadblocks to doing that,
which is that we now allow non-superuser database owners to create
trusted procedural languages for themselves. There's still a minor
roadblock, which is that at the moment pg_dump emits a "COMMENT ON
SCHEMA public" by default, and that fails if you're not the owner
of schema public, ie, the bootstrap superuser.
In the past we've always written off this kind of thing as just
cosmetic, but with the increasing performance advantages of doing
a restore in a single transaction, I think it's important to try
to eliminate "ignorable errors" in dump/restore. Especially ones
as silly as this.
Accordingly I propose the attached patch. It's certainly ugly,
but it's not very much uglier than what was there already.
Anyone who had a custom comment on schema public would lose it,
but who does that?
Thoughts?
regards, tom lane
Index: pg_backup_archiver.c
===================================================================
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_backup_archiver.c,v
retrieving revision 1.151
diff -c -r1.151 pg_backup_archiver.c
*** pg_backup_archiver.c 24 Nov 2007 20:26:49 -0000 1.151
--- pg_backup_archiver.c 13 Jan 2008 01:44:00 -0000
***************
*** 2528,2538 **** /* * Avoid dumping the public schema, as it will already be created ... * unless we
areusing --clean mode, in which case it's been deleted and
! * we'd better recreate it. */
! if (!ropt->dropSchema &&
! strcmp(te->desc, "SCHEMA") == 0 && strcmp(te->tag, "public") == 0)
! return; /* Select owner, schema, and tablespace as necessary */ _becomeOwner(AH, te);
--- 2528,2544 ---- /* * Avoid dumping the public schema, as it will already be created ... * unless we
areusing --clean mode, in which case it's been deleted and
! * we'd better recreate it. Likewise for its comment, if any. */
! if (!ropt->dropSchema)
! {
! if (strcmp(te->desc, "SCHEMA") == 0 &&
! strcmp(te->tag, "public") == 0)
! return;
! if (strcmp(te->desc, "COMMENT") == 0 &&
! strcmp(te->tag, "SCHEMA public") == 0)
! return;
! } /* Select owner, schema, and tablespace as necessary */ _becomeOwner(AH, te);
