From: Tom Lane <tgl@sss.pgh.pa.us>
To: "David G. Johnston" <david.g.johnston@gmail.com>
cc: =?UTF-8?Q?Guly=C3=A1s_Attila?= <toraritte@gmail.com>,
        "pgsql-docs@lists.postgresql.org" <pgsql-docs@lists.postgresql.org>
Subject: Re: `pg_restore --if-exists` clarification
In-reply-to: 
 <CAKFQuwbM2s5Naeb4SZted17zM=r_-C8w07-Bw-SyYd1b8a4LRA@mail.gmail.com>
References: 
 <169590536647.3727336.11070254203649648453@wrigleys.postgresql.org>
 <CANwZ8rniBS68AQzUn3NRmtmPAKD3f5h6b8CBMkRyEdpcD7vPOQ@mail.gmail.com>
 <CADnELphErTfH0_R-7g-4TM2v6fnPaLnbPoCYLag1ATGSxb9ivA@mail.gmail.com>
 <CAKFQuwbM2s5Naeb4SZted17zM=r_-C8w07-Bw-SyYd1b8a4LRA@mail.gmail.com>
Comments: In-reply-to "David G. Johnston" <david.g.johnston@gmail.com>
	message dated "Thu, 28 Sep 2023 09:36:34 -0700"
MIME-Version: 1.0
Content-Type: text/plain; charset="UTF-8"
Content-ID: <998114.1695920626.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 28 Sep 2023 13:03:46 -0400
Message-ID: <998115.1695920626@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/998115.1695920626%40sss.pgh.pa.us>
Precedence: bulk

"David G. Johnston" <david.g.johnston@gmail.com> writes:
>>> .. But pragmatically it doesn't matter, so why not
>>> reclassify this as a "warning" or a "notice"?

> Because pg_restore is just a client and it is repeating back what the
> server tells it.  And for the server it is an error to drop an object th=
at
> doesn=E2=80=99t exist.
> psql and pg_restore, as clients, can choose to ignore the errors they se=
e,
> regardless of what kind of error it is, but they don=E2=80=99t take on t=
he added
> burden of trying to reclassify errors into something else.

If you actually try this, you'll get output like

...
pg_restore: error: could not execute query: ERROR:  schema "fkpart6" does =
not exist
Command was: DROP SCHEMA fkpart6;
pg_restore: error: could not execute query: ERROR:  schema "fkpart5" does =
not exist
Command was: DROP SCHEMA fkpart5;
pg_restore: error: could not execute query: ERROR:  schema "fkpart4" does =
not exist
Command was: DROP SCHEMA fkpart4;
pg_restore: error: could not execute query: ERROR:  schema "fkpart3" does =
not exist
Command was: DROP SCHEMA fkpart3;
...
pg_restore: warning: errors ignored on restore: 1488

so the only real problem is that there are (typically) enough of these
to obscure any errors that might be of greater significance.  Still,
genuine restore errors would come out after the blizzard of failed
DROPs, so I don't think there's a big problem in practice.

If we actually wanted to change any behavior here, I think what would
be most profitable to discuss is making --if-exists the default.
Not sure if we want to go there, but if we'd had the DROP IF EXISTS
option all along I bet it would have been done that way.

In any case, it's fair to complain about the documentation.
How about

--clean

   Before restoring database objects, issue commands to DROP all the
   objects that will be restored.  This option is useful for overwriting
   an existing database.  If any of the objects do not exist in the
   destination database, ignorable error messages will be reported,
   unless --if-exists is also specified.

--if-exists

   Use DROP ... IF EXISTS commands to drop objects in --clean mode.
   This suppresses "does not exist" errors that might otherwise be
   reported.  This option is not valid unless --clean is also specified.

			regards, tom lane