[BUGS] BUG #13804: pg_restore returns unexpected error

[p***@pgdude.com]

The following bug has been logged on the website:

Bug reference: 13804
Logged by: Michael Vitale
Email address: ***@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

[Michael Paquier]

Post by p***@pgdude.com
pg_restore returns 1 return code indicating failure when it tries to create
the public schema using PG 9.4.5
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.

[Jeff Janes]

On Mon, Dec 7, 2015 at 4:06 PM, Michael Paquier

Post by Michael Paquier

Post by p***@pgdude.com
pg_restore returns 1 return code indicating failure when it tries to
create
the public schema using PG 9.4.5
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

[m***@sqlexec.com]

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

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
========
ERROR: schema "public" already exists
STATEMENT: CREATE SCHEMA public;
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
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

[m***@sqlexec.com]

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

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

[David G. Johnston]

Post by Michael Paquier

Post by p***@pgdude.com
pg_restore returns 1 return code indicating failure when it tries to
create
the public schema using PG 9.4.5
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.

​***@3269-3283

​ /*
* 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") == 0 &&
strcmp(te->tag, "public") == 0)
return;
/* The comment restore would require super-user privs, so avoid it. */
if (strcmp(te->desc, "COMMENT") == 0 &&
strcmp(te->tag, "SCHEMA public") == 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):

​***@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") != 0)
continue;
}
else
{
if (strcmp(te->desc, "DATABASE") == 0)
continue;
}

​prevents it from being removed from the newly created database.​

​The <if (!ropt->dropSchema)> should be something like <if NOT(dropSchema
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.