Thread: BUG #13804: pg_restore returns unexpected error

BUG #13804: pg_restore returns unexpected error

From
pgdude@pgdude.com
Date:
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

Re: BUG #13804: pg_restore returns unexpected error

From
Michael Paquier
Date:
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

Re: BUG #13804: pg_restore returns unexpected error

From
Jeff Janes
Date:
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

Re: BUG #13804: pg_restore returns unexpected error

From
"michael@sqlexec.com"
Date:
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

Re: BUG #13804: pg_restore returns unexpected error

From
"michael@sqlexec.com"
Date:
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

Re: BUG #13804: pg_restore returns unexpected error

From
"David G. Johnston"
Date:
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.