Hello,
When using pg_dump (or pg_restore) with option "--clean", there is some
SQL code to drop every objects at the beginning.
The DROP statement for a view involving circular dependencies is :
CREATE OR REPLACE VIEW [...]
(see commit message of d8c05aff for a much better explanation)
If the view is not in the "public" schema, and the target database is
empty, this statement fails, because the schema hasn't been created yet.
The attached patches are a TAP test which can be used to reproduce the
bug, and a proposed fix. They apply to the master branch.
Best regards,
Frédéric
From 96a334e2411794bdc4648475be286f159daa2484 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yh...@dalibo.com>
Date: Wed, 31 Aug 2022 15:57:39 +0200
Subject: [PATCH 2/2] pg_dump: fix up d8c05aff
---
src/bin/pg_dump/pg_backup_archiver.c | 3 ++-
src/bin/pg_dump/pg_dump.c | 4 ++++
2 files changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 233198afc0..bb5031105e 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -585,7 +585,8 @@ RestoreArchive(Archive *AHX)
*/
if (strcmp(te->desc, "DEFAULT") == 0 ||
strcmp(te->desc, "DATABASE PROPERTIES") == 0 ||
- strncmp(dropStmt, "CREATE OR REPLACE VIEW", 22) == 0)
+ ( strncmp(dropStmt, "CREATE SCHEMA IF NOT EXISTS", 27) == 0 &&
+ strstr(dropStmt+29, "CREATE OR REPLACE VIEW") ))
appendPQExpBufferStr(ftStmt, dropStmt);
else
{
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index d25709ad5f..8820984d9e 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -17274,6 +17274,7 @@ dumpRule(Archive *fout, const RuleInfo *rinfo)
char *qtabname;
PGresult *res;
char *tag;
+ char *qnspname;
/* Do nothing in data-only dump */
if (dopt->dataOnly)
@@ -17374,6 +17375,9 @@ dumpRule(Archive *fout, const RuleInfo *rinfo)
*/
PQExpBuffer result;
+ qnspname = pg_strdup(fmtId(tbinfo->dobj.namespace->dobj.name));
+ appendPQExpBuffer(delcmd, "CREATE SCHEMA IF NOT EXISTS %s;\n", qnspname);
+ free(qnspname);
appendPQExpBuffer(delcmd, "CREATE OR REPLACE VIEW %s",
fmtQualifiedDumpable(tbinfo));
result = createDummyViewAsClause(fout, tbinfo);
--
2.30.2
From 463774feb3b968b577ac082a71fdc2de51c8bac4 Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Fr=C3=A9d=C3=A9ric=20Yhuel?= <frederic.yh...@dalibo.com>
Date: Wed, 31 Aug 2022 16:03:34 +0200
Subject: [PATCH 1/2] Add TAP test for pg_restore
---
src/bin/pg_dump/t/002_pg_dump.pl | 22 ++++++++++++++++++++++
1 file changed, 22 insertions(+)
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 2873b662fb..ff826e4688 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4042,6 +4042,28 @@ $node->command_ok(
'pg_dumpall: option --exclude-database handles database names with embedded dots'
);
+########################################
+# Test pg_restore with --clean --if-exists
+
+$node->psql('postgres', 'create database postgres2;');
+$node->psql('postgres', 'create database postgres3;');
+
+$node->psql('postgres3',
+ 'CREATE SCHEMA dump_test;'
+ . 'CREATE TABLE dump_test.foo (id INT primary key, payload TEXT);'
+ . 'CREATE VIEW dump_test.babar AS SELECT * FROM dump_test.foo GROUP BY id;',
+ );
+
+$node->run_log(
+ [ 'pg_dump', '-p', "$port", '-Fc', '--no-sync', "--file=$tempdir/clean_if_exists.dump", 'postgres3' ]
+);
+
+$node->command_like(
+ [ 'pg_restore', '-p', "$port", '--clean', '--if-exists', '-d', 'postgres2', "$tempdir/clean_if_exists.dump" ],
+ '/^\s*$/',
+ 'pg_restore should output no warnings on stderr'
+);
+
#########################################
# Test invalid multipart schema names
--
2.30.2
