From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
cc: justin@tonic.ai, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17753: pg_dump --if-exists bug
In-reply-to: <4062761.1674142680@sss.pgh.pa.us>
References: <17753-9c8773631747ee1c@postgresql.org>
 <CAApHDvq+JTtZWOrXBrY7LvquyXHoCWWq=DfPYKB4b7BuFiM_8A@mail.gmail.com>
 <4062761.1674142680@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Thu, 19 Jan 2023 10:38:00 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <282231.1674166858.0@sss.pgh.pa.us>
Date: Thu, 19 Jan 2023 17:22:15 -0500
Message-ID: <282372.1674166935@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/282372.1674166935%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <282231.1674166858.1@sss.pgh.pa.us>

I wrote:
> Noting that that string is only associated with the public schema,
> I tried dumping a DB in which the public schema had been dropped
> --- no dice --- and then recreated --- and then it reproduces!
> Apparently, a schema that's named "public" but isn't the original
> one is confusing something somewhere, but I'm not immediately
> seeing where.

Ah, here's the difference: a schema named "public" will be marked
dumpable if its owner is not pg_database_owner:

    else if (strcmp(nsinfo->dobj.name, "public") == 0)
    {
        /*
         * The public schema is a strange beast that sits in a sort of
         * no-mans-land between being a system object and a user object.
         * CREATE SCHEMA would fail, so its DUMP_COMPONENT_DEFINITION is just
         * a comment and an indication of ownership.  If the owner is the
         * default, omit that superfluous DUMP_COMPONENT_DEFINITION.  Before
         * v15, the default owner was BOOTSTRAP_SUPERUSERID.
         */
        nsinfo->create = false;
        nsinfo->dobj.dump = DUMP_COMPONENT_ALL;
        if (nsinfo->nspowner == ROLE_PG_DATABASE_OWNER)
            nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;

Having done that, RestoreArchive's kluge to inject IF EXISTS
clauses fails, because (unlike other places in pg_backup_archiver.c)
it is unaware that the dropStmt might be just a comment.

I think we need to do the attached, more or less.  Also, although the
given case seems to be new, we'd probably better back-patch, because
I'm not convinced that there aren't other ways to reach this.  The
warning message is just cosmetic (the dump output is the same either
way), so possibly this has been seen in the field but not reported.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="fix-dump-if-exists-on-public-schema.patch";
	charset="us-ascii"
Content-ID: <282231.1674166858.2@sss.pgh.pa.us>
Content-Description: fix-dump-if-exists-on-public-schema.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_bac=
kup_archiver.c
index 7f7a0f1ce7..ba5e6acbbb 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -538,9 +538,14 @@ RestoreArchive(Archive *AHX)
 				 */
 				if (*te->dropStmt !=3D '\0')
 				{
-					if (!ropt->if_exists)
+					if (!ropt->if_exists ||
+						strncmp(te->dropStmt, "--", 2) =3D=3D 0)
 					{
-						/* No --if-exists?	Then just use the original */
+						/*
+						 * Without --if-exists, or if it's just a comment (as
+						 * happens for the public schema), print the dropStmt
+						 * as-is.
+						 */
 						ahprintf(AH, "%s", te->dropStmt);
 					}
 					else

------- =_aaaaaaaaaa0--