Thread: [BUGS] BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
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
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
Re: [BUGS] BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
From
Michael Paquier
Date:
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
* 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
Attachment
Re: [BUGS] BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
From
Michael Paquier
Date:
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
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
Attachment
Re: [BUGS] BUG #14650: pg_dump -c fails when 'public' schema doesn't exist
From
Michael Paquier
Date:
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