pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #14650: pg_dump -c fails when 'public' schema doesn't exist
7+ messages / 4 participants
[nested] [flat]
* BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
@ 2017-05-12 18:18 tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: tureba@gmail.com @ 2017-05-12 18:18 UTC (permalink / raw)
To: pgsql-bugs
The following bug has been logged on the website:
Bug reference: 14650
Logged by: Arthur Nascimento
Email address: tureba@gmail.com
PostgreSQL version: 9.6.3
Operating system: Linux - CentOS 7
Description:
Hi,
It seems that pg_dump -c fails when the database doesn't have a 'public'
schema. When using a clean database, then dropping (or renaming) the
'public' schema, a subsequent pg_dump -c tells me:
$ pg_dump -c test
pg_dump: [archiver (db)] query failed: ERROR: schema "public" does not
exist
LINE 1: ...::regclass AND pip.objsubid = 0 AND pip.objoid <> 'public'::...
^
pg_dump: [archiver (db)] query was: SELECT n.tableoid, n.oid, n.nspname,
(SELECT rolname FROM pg_catalog.pg_roles WHERE oid = nspowner) AS rolname,
(SELECT pg_catalog.array_agg(acl) FROM (SELECT
pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner)))
AS acl EXCEPT SELECT
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner))))
as foo) as nspacl, (SELECT pg_catalog.array_agg(acl) FROM (SELECT
pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault('n',n.nspowner)))
AS acl EXCEPT SELECT
pg_catalog.unnest(coalesce(n.nspacl,pg_catalog.acldefault('n',n.nspowner))))
as foo) as rnspacl, NULL as initnspacl, NULL as initrnspacl FROM
pg_namespace n LEFT JOIN pg_init_privs pip ON (n.oid = pip.objoid AND
pip.classoid = 'pg_namespace'::regclass AND pip.objsubid = 0 AND pip.objoid
<> 'public'::regnamespace)
This seems to have started in commit 330b84d8c4
A quick workaround is to create a schema called 'public' again, but I did
not expect the pg_dump error.
Thanks,
Arthur.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
@ 2017-05-12 18:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2017-05-12 18:22 UTC (permalink / raw)
To: tureba@gmail.com; +Cc: Stephen Frost <sfrost@snowman.net>; pgsql-bugs
tureba@gmail.com writes:
> It seems that pg_dump -c fails when the database doesn't have a 'public'
> schema. When using a clean database, then dropping (or renaming) the
> 'public' schema, a subsequent pg_dump -c tells me:
> $ pg_dump -c test
> pg_dump: [archiver (db)] query failed: ERROR: schema "public" does not exist
> LINE 1: ...::regclass AND pip.objsubid = 0 AND pip.objoid <> 'public'::...
> ^
> This seems to have started in commit 330b84d8c4
Yeah, this is not cool. Stephen?
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-05-13 22:54 ` Michael Paquier <michael.paquier@gmail.com>
2017-05-14 15:40 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
0 siblings, 1 reply; 7+ messages in thread
From: Michael Paquier @ 2017-05-13 22:54 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: tureba@gmail.com, Stephen Frost <sfrost@snowman.net>; pgsql-bugs
On Sat, May 13, 2017 at 3:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> tureba@gmail.com writes:
>> It seems that pg_dump -c fails when the database doesn't have a 'public'
>> schema. When using a clean database, then dropping (or renaming) the
>> 'public' schema, a subsequent pg_dump -c tells me:
>
>> $ pg_dump -c test
>> pg_dump: [archiver (db)] query failed: ERROR: schema "public" does not exist
>> LINE 1: ...::regclass AND pip.objsubid = 0 AND pip.objoid <> 'public'::...
>> ^
>
>> This seems to have started in commit 330b84d8c4
>
> Yeah, this is not cool. Stephen?
This is visibly an oversight of the recent commit 330b84d, which is
broken on its face because it does not check for the existence of this
schema. One simple fix would be to extend this query with a SELECT
CASE ... ELSE 0 FROM pg_namespace WHERE nspname = 'public' but that's
ugly.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
@ 2017-05-14 15:40 ` Stephen Frost <sfrost@snowman.net>
2017-05-15 00:53 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
2017-06-25 19:38 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
0 siblings, 2 replies; 7+ messages in thread
From: Stephen Frost @ 2017-05-14 15:40 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs
* Michael Paquier (michael.paquier@gmail.com) wrote:
> On Sat, May 13, 2017 at 3:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > tureba@gmail.com writes:
> >> It seems that pg_dump -c fails when the database doesn't have a 'public'
> >> schema. When using a clean database, then dropping (or renaming) the
> >> 'public' schema, a subsequent pg_dump -c tells me:
> >
> >> $ pg_dump -c test
> >> pg_dump: [archiver (db)] query failed: ERROR: schema "public" does not exist
> >> LINE 1: ...::regclass AND pip.objsubid = 0 AND pip.objoid <> 'public'::...
> >> ^
> >
> >> This seems to have started in commit 330b84d8c4
> >
> > Yeah, this is not cool. Stephen?
>
> This is visibly an oversight of the recent commit 330b84d, which is
> broken on its face because it does not check for the existence of this
> schema. One simple fix would be to extend this query with a SELECT
> CASE ... ELSE 0 FROM pg_namespace WHERE nspname = 'public' but that's
> ugly.
I would think using a coalesce() would be a bit cleaner, as in:
! appendPQExpBuffer(query,
! " AND pip.objoid <> "
! "coalesce((select oid from pg_namespace "
! "where nspname = 'public'),0)");
Attached is a patch which fixes this issue in that way and includes
changes to the TAP tests to check this case. The TAP tests are a bit
much to back-patch, I guess, since it involves adding the ability to
have multiple databases to the 002_pg_dump.pl framework, but hopefully
there won't be objections to adding that into master. This may also
provide an initial step towards folding 010_dump_connstr.pl into
002_pg_dump.pl and therefore possibly removing the need for another
cluster to be created during the pg_dump TAP tests and hopefully
reducing their overall runtime.
Thanks!
Stephen
Attachments:
[text/x-diff] fix_pg_dump_no_public_schema_v1-master.patch (6.8K, ../../20170514154025.GM3151@tamriel.snowman.net/2-fix_pg_dump_no_public_schema_v1-master.patch)
download | inline diff:
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
new file mode 100644
index b26358c..d331c46
*** a/src/bin/pg_dump/pg_dump.c
--- b/src/bin/pg_dump/pg_dump.c
*************** getNamespaces(Archive *fout, int *numNam
*** 4130,4138 ****
* essentially a no-op because the new public schema won't have an
* entry in pg_init_privs anyway, as the entry will be removed when
* the public schema is dropped.
*/
if (dopt->outputClean)
! appendPQExpBuffer(query," AND pip.objoid <> 'public'::regnamespace");
appendPQExpBuffer(query,") ");
--- 4130,4144 ----
* essentially a no-op because the new public schema won't have an
* entry in pg_init_privs anyway, as the entry will be removed when
* the public schema is dropped.
+ *
+ * Further, we have to handle the case where the public schema does
+ * not exist at all.
*/
if (dopt->outputClean)
! appendPQExpBuffer(query,
! " AND pip.objoid <> "
! "coalesce((select oid from pg_namespace "
! "where nspname = 'public'),0)");
appendPQExpBuffer(query,") ");
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
new file mode 100644
index 9bd400e..19b40de
*** a/src/bin/pg_dump/t/002_pg_dump.pl
--- b/src/bin/pg_dump/t/002_pg_dump.pl
*************** my %pgdump_runs = (
*** 103,108 ****
--- 103,125 ----
'-f',
"$tempdir/defaults.sql",
'postgres', ], },
+ defaults_no_public => {
+ database => 'regress_pg_dump_test',
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '-f',
+ "$tempdir/defaults_no_public.sql",
+ 'regress_pg_dump_test', ], },
+ defaults_no_public_clean => {
+ database => 'regress_pg_dump_test',
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '-c',
+ '-f',
+ "$tempdir/defaults_no_public_clean.sql",
+ 'regress_pg_dump_test', ], },
# Do not use --no-sync to give test coverage for data sync.
defaults_custom_format => {
test_key => 'defaults',
*************** qr/CREATE TRANSFORM FOR integer LANGUAGE
*** 4513,4518 ****
--- 4530,4572 ----
pg_dumpall_globals_clean => 1,
test_schema_plus_blobs => 1, }, },
+ 'CREATE SCHEMA public (none exists)' => {
+ all_runs => 1,
+ database => 'regress_pg_dump_test',
+ regexp => qr/^DROP SCHEMA public;/m,
+ like => { },
+ unlike => { defaults_no_public => 1,
+ defaults_no_public_clean => 1, } },
+
+ 'CREATE SCHEMA public' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ regexp => qr/^CREATE SCHEMA public;/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1, },
+ unlike => {
+ binary_upgrade => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_post_data => 1, }, },
+
'CREATE SCHEMA dump_test' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
*************** qr/CREATE TRANSFORM FOR integer LANGUAGE
*** 5205,5210 ****
--- 5259,5274 ----
data_only => 1,
section_data => 1, }, },
+ 'DROP SCHEMA public' => {
+ all_runs => 1,
+ database => 'regress_pg_dump_test',
+ create_order => 100,
+ create_sql => 'DROP SCHEMA public;',
+ regexp => qr/^DROP SCHEMA public;/m,
+ like => { },
+ unlike => { defaults_no_public => 1,
+ defaults_no_public_clean => 1, } },
+
'DROP EXTENSION plpgsql' => {
all_runs => 1,
catch_all => 'DROP ... commands',
*************** if ($collation_check_stderr !~ /ERROR: /
*** 6411,6416 ****
--- 6475,6483 ----
$collation_support = 1;
}
+ # Create a second database for certain tests to work against
+ $node->psql('postgres','create database regress_pg_dump_test;');
+
# Start with number of command_fails_like()*2 tests below (each
# command_fails_like is actually 2 tests)
my $num_tests = 12;
*************** my $num_tests = 12;
*** 6418,6423 ****
--- 6485,6495 ----
foreach my $run (sort keys %pgdump_runs)
{
my $test_key = $run;
+ my $run_db = 'postgres';
+
+ if (defined($pgdump_runs{$run}->{database})) {
+ $run_db = $pgdump_runs{$run}->{database};
+ }
# Each run of pg_dump is a test itself
$num_tests++;
*************** foreach my $run (sort keys %pgdump_runs)
*** 6436,6441 ****
--- 6508,6523 ----
# Then count all the tests run against each run
foreach my $test (sort keys %tests)
{
+ my $test_db = 'postgres';
+
+ if (defined($tests{$test}->{database})) {
+ $test_db = $tests{$test}->{database};
+ }
+
+ if ($run_db ne $test_db) {
+ next;
+ }
+
# Skip any collation-related commands if there is no collation support
if (!$collation_support && defined($tests{$test}->{collation})) {
next;
*************** plan tests => $num_tests;
*** 6483,6489 ****
# Set up schemas, tables, etc, to be dumped.
# Build up the create statements
! my $create_sql = '';
foreach my $test (
sort {
--- 6565,6571 ----
# Set up schemas, tables, etc, to be dumped.
# Build up the create statements
! my %create_sql = ();
foreach my $test (
sort {
*************** foreach my $test (
*** 6505,6510 ****
--- 6587,6598 ----
}
} keys %tests)
{
+ my $test_db = 'postgres';
+
+ if (defined($tests{$test}->{database})) {
+ $test_db = $tests{$test}->{database};
+ }
+
if ($tests{$test}->{create_sql})
{
# Skip any collation-related commands if there is no collation support
*************** foreach my $test (
*** 6513,6524 ****
}
# Add terminating semicolon
! $create_sql .= $tests{$test}->{create_sql} . ";";
}
}
# Send the combined set of commands to psql
! $node->safe_psql('postgres', $create_sql);
#########################################
# Test connecting to a non-existent database
--- 6601,6615 ----
}
# Add terminating semicolon
! $create_sql{$test_db} .= $tests{$test}->{create_sql} . ";";
}
}
# Send the combined set of commands to psql
! foreach my $db (sort keys %create_sql)
! {
! $node->safe_psql($db, $create_sql{$db});
! }
#########################################
# Test connecting to a non-existent database
*************** command_fails_like(
*** 6565,6571 ****
foreach my $run (sort keys %pgdump_runs)
{
-
my $test_key = $run;
$node->command_ok(\@{ $pgdump_runs{$run}->{dump_cmd} },
--- 6656,6661 ----
[application/pgp-signature] signature.asc (818B, ../../20170514154025.GM3151@tamriel.snowman.net/3-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
2017-05-14 15:40 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
@ 2017-05-15 00:53 ` Michael Paquier <michael.paquier@gmail.com>
1 sibling, 0 replies; 7+ messages in thread
From: Michael Paquier @ 2017-05-15 00:53 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs
On Mon, May 15, 2017 at 12:40 AM, Stephen Frost <sfrost@snowman.net> wrote:
> * Michael Paquier (michael.paquier@gmail.com) wrote:
>> On Sat, May 13, 2017 at 3:22 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> > tureba@gmail.com writes:
>> >> It seems that pg_dump -c fails when the database doesn't have a 'public'
>> >> schema. When using a clean database, then dropping (or renaming) the
>> >> 'public' schema, a subsequent pg_dump -c tells me:
>> >
>> >> $ pg_dump -c test
>> >> pg_dump: [archiver (db)] query failed: ERROR: schema "public" does not exist
>> >> LINE 1: ...::regclass AND pip.objsubid = 0 AND pip.objoid <> 'public'::...
>> >> ^
>> >
>> >> This seems to have started in commit 330b84d8c4
>> >
>> > Yeah, this is not cool. Stephen?
>>
>> This is visibly an oversight of the recent commit 330b84d, which is
>> broken on its face because it does not check for the existence of this
>> schema. One simple fix would be to extend this query with a SELECT
>> CASE ... ELSE 0 FROM pg_namespace WHERE nspname = 'public' but that's
>> ugly.
>
> I would think using a coalesce() would be a bit cleaner, as in:
>
> ! appendPQExpBuffer(query,
> ! " AND pip.objoid <> "
> ! "coalesce((select oid from pg_namespace "
> ! "where nspname = 'public'),0)");
No objections to that.
> Attached is a patch which fixes this issue in that way and includes
> changes to the TAP tests to check this case.
This looks good to me.
> The TAP tests are a bit
> much to back-patch, I guess, since it involves adding the ability to
> have multiple databases to the 002_pg_dump.pl framework, but hopefully
> there won't be objections to adding that into master. This may also
> provide an initial step towards folding 010_dump_connstr.pl into
> 002_pg_dump.pl and therefore possibly removing the need for another
> cluster to be created during the pg_dump TAP tests and hopefully
> reducing their overall runtime.
Back-patching test-related patches are not a bad thing IMO, this
provides more coverage for bugs in stable releases.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
2017-05-14 15:40 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
@ 2017-06-25 19:38 ` Stephen Frost <sfrost@snowman.net>
2017-06-26 05:49 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
1 sibling, 1 reply; 7+ messages in thread
From: Stephen Frost @ 2017-06-25 19:38 UTC (permalink / raw)
To: Michael Paquier <michael.paquier@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs
Micahel, all,
* Stephen Frost (sfrost@snowman.net) wrote:
> I would think using a coalesce() would be a bit cleaner, as in:
[...]
Attached are updated patches for both master and 9.6.
Barring objections, I'll push these tomorrow.
Thanks!
Stephen
Attachments:
[text/x-diff] fix_pg_dump_public_schema-master_v2.patch (7.2K, ../../20170625193852.GH1769@tamriel.snowman.net/2-fix_pg_dump_public_schema-master_v2.patch)
download | inline diff:
From 29a5cefadf0213525b57c40c8022654542668829 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Sun, 25 Jun 2017 11:27:49 -0400
Subject: [PATCH] Do not require 'public' to exist for pg_dump -c
Commit 330b84d8c4 didn't contemplate the case where the public schema
has been dropped and introduced a query which fails when there is no
public schema into pg_dump (when used with -c).
Adjust the query used by pg_dump to handle the case where the public
schema doesn't exist and add tests to check that such a case no longer
fails.
Back-patch the specific fix to 9.6, as the prior commit was.
Adding tests for this case involved adding support to the pg_dump
TAP tests to work with multiple databases, which, while not a large
change, is a bit much to back-patch, so that's only done in master.
Addresses bug #14650
Discussion: https://www.postgresql.org/message-id/20170512181801.1795.47483%40wrigleys.postgresql.org
---
src/bin/pg_dump/pg_dump.c | 7 ++-
src/bin/pg_dump/t/002_pg_dump.pl | 110 +++++++++++++++++++++++++++++++++++++--
2 files changed, 113 insertions(+), 4 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index ec349d4..502d5eb 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4135,9 +4135,14 @@ getNamespaces(Archive *fout, int *numNamespaces)
* essentially a no-op because the new public schema won't have an
* entry in pg_init_privs anyway, as the entry will be removed when
* the public schema is dropped.
+ *
+ * Further, we have to handle the case where the public schema does
+ * not exist at all.
*/
if (dopt->outputClean)
- appendPQExpBuffer(query, " AND pip.objoid <> 'public'::regnamespace");
+ appendPQExpBuffer(query, " AND pip.objoid <> "
+ "coalesce((select oid from pg_namespace "
+ "where nspname = 'public'),0)");
appendPQExpBuffer(query, ") ");
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index e70a421..a8000db 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -97,6 +97,23 @@ my %pgdump_runs = (
'pg_dump', '--no-sync',
'-f', "$tempdir/defaults.sql",
'postgres', ], },
+ defaults_no_public => {
+ database => 'regress_pg_dump_test',
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '-f',
+ "$tempdir/defaults_no_public.sql",
+ 'regress_pg_dump_test', ], },
+ defaults_no_public_clean => {
+ database => 'regress_pg_dump_test',
+ dump_cmd => [
+ 'pg_dump',
+ '--no-sync',
+ '-c',
+ '-f',
+ "$tempdir/defaults_no_public_clean.sql",
+ 'regress_pg_dump_test', ], },
# Do not use --no-sync to give test coverage for data sync.
defaults_custom_format => {
@@ -4524,6 +4541,35 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
pg_dumpall_globals_clean => 1,
test_schema_plus_blobs => 1, }, },
+ 'CREATE SCHEMA public' => {
+ all_runs => 1,
+ catch_all => 'CREATE ... commands',
+ regexp => qr/^CREATE SCHEMA public;/m,
+ like => {
+ clean => 1,
+ clean_if_exists => 1, },
+ unlike => {
+ binary_upgrade => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table => 1,
+ exclude_test_table_data => 1,
+ no_blobs => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_pre_data => 1,
+ test_schema_plus_blobs => 1,
+ with_oids => 1,
+ exclude_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1,
+ role => 1,
+ section_post_data => 1, }, },
+
'CREATE SCHEMA dump_test' => {
all_runs => 1,
catch_all => 'CREATE ... commands',
@@ -5219,6 +5265,34 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
data_only => 1,
section_data => 1, }, },
+ 'DROP SCHEMA public (for testing without public schema)' => {
+ all_runs => 1,
+ database => 'regress_pg_dump_test',
+ create_order => 100,
+ create_sql => 'DROP SCHEMA public;',
+ regexp => qr/^DROP SCHEMA public;/m,
+ like => { },
+ unlike => { defaults_no_public => 1,
+ defaults_no_public_clean => 1, } },
+
+ 'DROP SCHEMA public' => {
+ all_runs => 1,
+ catch_all => 'DROP ... commands',
+ regexp => qr/^DROP SCHEMA public;/m,
+ like => { clean => 1 },
+ unlike => {
+ clean_if_exists => 1,
+ pg_dumpall_globals_clean => 1, }, },
+
+ 'DROP SCHEMA IF EXISTS public' => {
+ all_runs => 1,
+ catch_all => 'DROP ... commands',
+ regexp => qr/^DROP SCHEMA IF EXISTS public;/m,
+ like => { clean_if_exists => 1 },
+ unlike => {
+ clean => 1,
+ pg_dumpall_globals_clean => 1, }, },
+
'DROP EXTENSION plpgsql' => {
all_runs => 1,
catch_all => 'DROP ... commands',
@@ -6433,6 +6507,9 @@ if ($collation_check_stderr !~ /ERROR: /)
$collation_support = 1;
}
+# Create a second database for certain tests to work against
+$node->psql('postgres','create database regress_pg_dump_test;');
+
# Start with number of command_fails_like()*2 tests below (each
# command_fails_like is actually 2 tests)
my $num_tests = 12;
@@ -6440,6 +6517,11 @@ my $num_tests = 12;
foreach my $run (sort keys %pgdump_runs)
{
my $test_key = $run;
+ my $run_db = 'postgres';
+
+ if (defined($pgdump_runs{$run}->{database})) {
+ $run_db = $pgdump_runs{$run}->{database};
+ }
# Each run of pg_dump is a test itself
$num_tests++;
@@ -6458,6 +6540,19 @@ foreach my $run (sort keys %pgdump_runs)
# Then count all the tests run against each run
foreach my $test (sort keys %tests)
{
+ # postgres is the default database, if it isn't overridden
+ my $test_db = 'postgres';
+
+ # Specific tests can override the database to use
+ if (defined($tests{$test}->{database})) {
+ $test_db = $tests{$test}->{database};
+ }
+
+ # The database to test against needs to match the database the run is
+ # for, so skip combinations where they don't match up.
+ if ($run_db ne $test_db) {
+ next;
+ }
# Skip any collation-related commands if there is no collation support
if (!$collation_support && defined($tests{$test}->{collation}))
@@ -6507,7 +6602,7 @@ plan tests => $num_tests;
# Set up schemas, tables, etc, to be dumped.
# Build up the create statements
-my $create_sql = '';
+my %create_sql = ();
foreach my $test (
sort {
@@ -6529,6 +6624,12 @@ foreach my $test (
}
} keys %tests)
{
+ my $test_db = 'postgres';
+
+ if (defined($tests{$test}->{database})) {
+ $test_db = $tests{$test}->{database};
+ }
+
if ($tests{$test}->{create_sql})
{
@@ -6539,12 +6640,15 @@ foreach my $test (
}
# Add terminating semicolon
- $create_sql .= $tests{$test}->{create_sql} . ";";
+ $create_sql{$test_db} .= $tests{$test}->{create_sql} . ";";
}
}
# Send the combined set of commands to psql
-$node->safe_psql('postgres', $create_sql);
+foreach my $db (sort keys %create_sql)
+{
+ $node->safe_psql($db, $create_sql{$db});
+}
#########################################
# Test connecting to a non-existent database
--
2.7.4
[text/x-diff] fix_pg_dump_public_schema-96_v2.patch (1.8K, ../../20170625193852.GH1769@tamriel.snowman.net/3-fix_pg_dump_public_schema-96_v2.patch)
download | inline diff:
From abc4cb77d428bbb9227e39010e2596bc68242c6f Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfrost@snowman.net>
Date: Sun, 25 Jun 2017 11:27:49 -0400
Subject: [PATCH] Do not require 'public' to exist for pg_dump -c
Commit 330b84d8c4 didn't contemplate the case where the public schema
has been dropped and introduced a query which fails when there is no
public schema into pg_dump (when used with -c).
Adjust the query used by pg_dump to handle the case where the public
schema doesn't exist and add tests to check that such a case no longer
fails.
Back-patch the specific fix to 9.6, as the prior commit was.
Adding tests for this case involved adding support to the pg_dump
TAP tests to work with multiple databases, which, while not a large
change, is a bit much to back-patch, so that's only done in master.
Addresses bug #14650
Discussion: https://www.postgresql.org/message-id/20170512181801.1795.47483%40wrigleys.postgresql.org
---
src/bin/pg_dump/pg_dump.c | 7 ++++++-
1 file changed, 6 insertions(+), 1 deletion(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 27a2a0c..d1db370 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3659,9 +3659,14 @@ getNamespaces(Archive *fout, int *numNamespaces)
* essentially a no-op because the new public schema won't have an
* entry in pg_init_privs anyway, as the entry will be removed when
* the public schema is dropped.
+ *
+ * Further, we have to handle the case where the public schema does
+ * not exist at all.
*/
if (dopt->outputClean)
- appendPQExpBuffer(query," AND pip.objoid <> 'public'::regnamespace");
+ appendPQExpBuffer(query," AND pip.objoid <> "
+ "coalesce((select oid from pg_namespace "
+ "where nspname = 'public'),0)");
appendPQExpBuffer(query,") ");
--
2.7.4
[application/pgp-signature] signature.asc (818B, ../../20170625193852.GH1769@tamriel.snowman.net/4-signature.asc)
download
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Michael Paquier <michael.paquier@gmail.com>
2017-05-14 15:40 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
2017-06-25 19:38 ` Re: BUG #14650: pg_dump -c fails when 'public' schema doesn't exist Stephen Frost <sfrost@snowman.net>
@ 2017-06-26 05:49 ` Michael Paquier <michael.paquier@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Michael Paquier @ 2017-06-26 05:49 UTC (permalink / raw)
To: Stephen Frost <sfrost@snowman.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs
On Mon, Jun 26, 2017 at 4:38 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Michael, all,
>
> * Stephen Frost (sfrost@snowman.net) wrote:
>> I would think using a coalesce() would be a bit cleaner, as in:
>
> [...]
>
> Attached are updated patches for both master and 9.6.
>
> Barring objections, I'll push these tomorrow.
Thanks Stephen for the new patches. Those look good to me.
--
Michael
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2017-06-26 05:49 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-12 18:18 BUG #14650: pg_dump -c fails when 'public' schema doesn't exist tureba@gmail.com
2017-05-12 18:22 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-13 22:54 ` Michael Paquier <michael.paquier@gmail.com>
2017-05-14 15:40 ` Stephen Frost <sfrost@snowman.net>
2017-05-15 00:53 ` Michael Paquier <michael.paquier@gmail.com>
2017-06-25 19:38 ` Stephen Frost <sfrost@snowman.net>
2017-06-26 05:49 ` Michael Paquier <michael.paquier@gmail.com>
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