Thread: BUG #13804: pg_restore returns unexpected error
The following bug has been logged on the website: Bug reference: 13804 Logged by: Michael Vitale Email address: pgdude@pgdude.com PostgreSQL version: 9.4.5 Operating system: Linux Redhat 7 Description: pg_restore returns 1 return code indicating failure when it tries to create the public schema using PG 9.4.5 Here is my command: pg_restore -h host2 -p 5432 -d postgres -C -c --if-exists -Fd -j 6 -v /path to my dumps/mydump Same error using format: -Fc
On Tue, Dec 8, 2015 at 1:35 AM, <pgdude@pgdude.com> wrote: > pg_restore returns 1 return code indicating failure when it tries to create > the public schema using PG 9.4.5 > > Here is my command: > pg_restore -h host2 -p 5432 -d postgres -C -c --if-exists -Fd -j 6 -v /path > to my dumps/mydump > > Same error using format: -Fc > If you think this is a bug, could you send a test case? There is not enough information regarding what you expect of pg_restore and what it is currently doing. -- Michael
On Mon, Dec 7, 2015 at 4:06 PM, Michael Paquier
<michael.paquier@gmail.com> wrote:
>
>
> On Tue, Dec 8, 2015 at 1:35 AM, <pgdude@pgdude.com> wrote:
>>
>> pg_restore returns 1 return code indicating failure when it tries to
>> create
>> the public schema using PG 9.4.5
>>
>> Here is my command:
>> pg_restore -h host2 -p 5432 -d postgres -C -c --if-exists -Fd -j 6 -v
>> /path
>> to my dumps/mydump
>>
>> Same error using format: -Fc
>
>
> If you think this is a bug, could you send a test case? There is not enough
> information regarding what you expect of pg_restore and what it is currently
> doing.
createdb foobar
pgbench -i foobar
pg_dump foobar -Fc > dump.dmp
dropdb foobar
pg_restore -C -c --if-exists -d postgres dump.dmp
The above yields this message:
========
ERROR: schema "public" already exists
STATEMENT: CREATE SCHEMA public;
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA
public jjanes
pg_restore: [archiver (db)] could not execute query: ERROR: schema
"public" already exists
Command was: CREATE SCHEMA public;
=======
Since both -C and -c are specified, it should know that it is starting
out with a freshly created database which has a public schema by
default. So either it should not attempt to create the public schema
a second time, or it should silently discard the (expected) error
message, or conditionally drop the schema before recreating it.
The error message does not indicate an actual problem, and can be
ignored. But since the point of -c and --if-exists seems to be to
suppress just that kind of ignorable error message, it does seem like
a bug that it fails to do so.
Cheers,
Jeff
Example Test Case: psql create database aaa; \c aaa create table t1(akey int); insert into t1 VALUES(1); \q -- example generates exit code 1 for pg_restore when creating public schema pg_dump -d aaa -p 6432 -C -c --if-exists -Fc -v -w -f ./db.dump pg_restore -d postgres -p 6432 -C -c --if-exists -Fc -v ./db.dump -- generate listing pg_restore -C -c --if-exists -Fc -v -l db.dump > db.list -- generate output commands (no db provided) pg_restore -C -c --if-exists -Fc -v -L db.list db.dump -- execute using the list: generates same exit code 1 when attempting to create public schema pg_restore -C -c --if-exists -Fc -v -L db.list db.dump -d postgres > Jeff Janes <mailto:jeff.janes@gmail.com> > Monday, December 7, 2015 7:33 PM > On Mon, Dec 7, 2015 at 4:06 PM, Michael Paquier > > > createdb foobar > pgbench -i foobar > pg_dump foobar -Fc > dump.dmp > dropdb foobar > pg_restore -C -c --if-exists -d postgres dump.dmp > > The above yields this message: > ======== > > ERROR: schema "public" already exists > STATEMENT: CREATE SCHEMA public; > > > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 5; 2615 2200 SCHEMA > public jjanes > pg_restore: [archiver (db)] could not execute query: ERROR: schema > "public" already exists > Command was: CREATE SCHEMA public; > > > ======= > > Since both -C and -c are specified, it should know that it is starting > out with a freshly created database which has a public schema by > default. So either it should not attempt to create the public schema > a second time, or it should silently discard the (expected) error > message, or conditionally drop the schema before recreating it. > > The error message does not indicate an actual problem, and can be > ignored. But since the point of -c and --if-exists seems to be to > suppress just that kind of ignorable error message, it does seem like > a bug that it fails to do so. > > Cheers, > > Jeff > Michael Paquier <mailto:michael.paquier@gmail.com> > Monday, December 7, 2015 7:06 PM > > > > If you think this is a bug, could you send a test case? There is not > enough information regarding what you expect of pg_restore and what it > is currently doing. > -- > Michael
Example Test Case: psql create database aaa; \c aaa create table t1(akey int); insert into t1 VALUES(1); \q -- example generates exit code 1 for pg_restore when creating public schema pg_dump -d aaa -p 6432 -C -c --if-exists -Fc -v -w -f ./db.dump pg_restore -d postgres -p 6432 -C -c --if-exists -Fc -v ./db.dump -- generate listing pg_restore -C -c --if-exists -Fc -v -l db.dump > db.list -- generate output commands (no db provided) pg_restore -C -c --if-exists -Fc -v -L db.list db.dump -- execute using the list: generates same exit code 1 when attempting to create public schema pg_restore -C -c --if-exists -Fc -v -L db.list db.dump -d postgres > Michael Paquier <mailto:michael.paquier@gmail.com> > Monday, December 7, 2015 7:06 PM > > > > If you think this is a bug, could you send a test case? There is not > enough information regarding what you expect of pg_restore and what it > is currently doing. > -- > Michael
On Mon, Dec 7, 2015 at 7:06 PM, Michael Paquier <michael.paquier@gmail.com>
wrote:
>
>
> On Tue, Dec 8, 2015 at 1:35 AM, <pgdude@pgdude.com> wrote:
>
>> pg_restore returns 1 return code indicating failure when it tries to
>> create
>> the public schema using PG 9.4.5
>>
>> Here is my command:
>> pg_restore -h host2 -p 5432 -d postgres -C -c --if-exists -Fd -j 6 -v
>> /path
>> to my dumps/mydump
>>
>> Same error using format: -Fc
>>
>
> If you think this is a bug, could you send a test case? There is not
> enough information regarding what you expect of pg_restore and what it is
> currently doing.
>
=E2=80=8Bpg_backup_archiver.c@3269-3283
=E2=80=8B /*
* Avoid dumping the public schema, as it will already be created ...
* unless we are using --clean mode, in which case it's been deleted and
* we'd better recreate it. Likewise for its comment, if any.
*/
if (!ropt->dropSchema)
{
if (strcmp(te->desc, "SCHEMA") =3D=3D 0 &&
strcmp(te->tag, "public") =3D=3D 0)
return;
/* The comment restore would require super-user privs, so avoid it. */
if (strcmp(te->desc, "COMMENT") =3D=3D 0 &&
strcmp(te->tag, "SCHEMA public") =3D=3D 0)
return;
}
This is wrong. The presence of --create causes the schema to still be
present in the newly created database and the logic here (among other
things):
=E2=80=8Bpg_backup_archiver.c@472-488
/*
* In createDB mode, issue a DROP *only* for the database as a
* whole. Issuing drops against anything else would be wrong,
* because at this point we're connected to the wrong database.
* Conversely, if we're not in createDB mode, we'd better not
* issue a DROP against the database at all.
*/
if (ropt->createDB)
{
if (strcmp(te->desc, "DATABASE") !=3D 0)
continue;
}
else
{
if (strcmp(te->desc, "DATABASE") =3D=3D 0)
continue;
}
=E2=80=8Bprevents it from being removed from the newly created database.=E2=
=80=8B
=E2=80=8BThe <if (!ropt->dropSchema)> should be something like <if NOT(drop=
Schema
AND NOT createDB) {skip public schema}>
I really dislike the negative logic here, though...but it is correct and I
couldn't figure out a better way to write it. Maybe pretty-up and add the
following truth-table to the code comment...
dropSchema createDB skip/makePublic
drop create skip /* present from new database - we are presently "make"
here and that is wrong. */
drop exist make /*!!! dropped from existing database */
leave create skip /* present from new database */
leave exist skip /* ***assumed*** present in old database and not asked
to drop... */
I dislike the reasoning for the last truth table row...but we mustn't drop
the schema as we were not asked to do so. We also cannot conditionally
create the schema since IF NOT EXISTS was only introduced in 9.3...
David J.