From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: justin@tonic.ai
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17753: pg_dump --if-exists bug
Date: Thu, 19 Jan 2023 17:22:15 -0500
Message-ID: <282372.1674166935@sss.pgh.pa.us> (raw)
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>
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
Attachments:fix-dump-if-exists-on-public-schema.patch (text/x-diff, 694B) downloaddiff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.cindex 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 != '\0')
{
- if (!ropt->if_exists)+ if (!ropt->if_exists ||+ strncmp(te->dropStmt, "--", 2) == 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: tgl@sss.pgh.pa.us, dgrowleyml@gmail.com, justin@tonic.ai, pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #17753: pg_dump --if-exists bug
In-Reply-To: <282372.1674166935@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox