pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feed`pg_restore --if-exists` clarification
8+ messages / 5 participants
[nested] [flat]
* `pg_restore --if-exists` clarification
@ 2023-09-28 12:49 PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: PG Doc comments form @ 2023-09-28 12:49 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org; +Cc: toraritte@gmail.com
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/16/app-pgrestore.html
Description:
Good Morning,
In the `pg_restore` docs
(https://www.postgresql.org/docs/current/app-pgrestore.html), `--if-exists`
states that it is to
> Use conditional commands (i.e., add an IF EXISTS clause)
> to drop database objects. This option is not valid unless
> --clean is also specified.", but not being a SQL expert,
> I'm having a hard time deciphering this.
Suggestion: Would you consider adding the sentence (or something similar)?
> "--clean makes pg_restore drop all objects first,
> and --if-exists prevents that non-existent objects
> cause a failure."
Source: https://stackoverflow.com/a/75136163/1498178
This is succinct, and tells exactly what one needs to know. Thank you and
have a great day!
Appreciatively,
Attila
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-28 14:32 Kirk Parker <khp@equatoria.us>
parent: PG Doc comments form <noreply@postgresql.org>
0 siblings, 1 reply; 8+ messages in thread
From: Kirk Parker @ 2023-09-28 14:32 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
On Thu, Sep 28, 2023, 05:52 PG Doc comments form <noreply@postgresql.org>
wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/16/app-pgrestore.html
> Description:
>
> Good Morning,
>
> In the `pg_restore` docs
> (https://www.postgresql.org/docs/current/app-pgrestore.html),
> `--if-exists`
> states that it is to
>
> > Use conditional commands (i.e., add an IF EXISTS clause)
> > to drop database objects. This option is not valid unless
> > --clean is also specified.", but not being a SQL expert,
> > I'm having a hard time deciphering this.
>
> Suggestion: Would you consider adding the sentence (or something similar)?
>
> > "--clean makes pg_restore drop all objects first,
> > and --if-exists prevents that non-existent objects
> > cause a failure."
> Source: https://stackoverflow.com/a/75136163/1498178
>
> This is succinct, and tells exactly what one needs to know. Thank you and
> have a great day!
>
> Appreciatively,
> Attila
>
But "failure" is not what happens. If you read the part regarding --clean,
you will see that it says:
(Unless --if-exists is used, this might generate some *harmless error
messages*, if any objects were not present in the destination
database.) *[emphasis
added]*
with extra emphasis on the word "harmless" -- no failure is caused; the
restore proceeds just fine.
If anything were to change in this regard, it might be better to reconsider
what we call the message (i.e.what language pg_restore emits in this
scenario.) It's true, I suppose, in a literal sense that it's an error in
that pg_restore couldn't drop a table when instructed to, where no such
table exists. But pragmatically it doesn't matter, so why not reclassify
this as a "warning" or a "notice"?
--Kirk
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-28 16:12 Gulyás Attila <toraritte@gmail.com>
parent: Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 8+ messages in thread
From: Gulyás Attila @ 2023-09-28 16:12 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
So `--if-exists` simply suppresses any notice / warning that would occur?
I'm sorry if I come off as obtuse, but I have literally no idea what
`--clean` and `--if-exists` do when used together. I took the quote from
the Stackoverflow answer on face value, because it sounded plausible and I
can't understand `--if-exists` current description yet. Any clarification
there would be an improvement in my opinion for people in similar
situations (i.e., having little to no SQL knowledge, but still having to do
basic DB admin tasks).
Appreciatively,
Attila
On Thu, Sep 28, 2023 at 12:01 PM Kirk Parker <khp@equatoria.us> wrote:
>
>
> On Thu, Sep 28, 2023, 05:52 PG Doc comments form <noreply@postgresql.org>
> wrote:
>
>> The following documentation comment has been logged on the website:
>>
>> Page: https://www.postgresql.org/docs/16/app-pgrestore.html
>> Description:
>>
>> Good Morning,
>>
>> In the `pg_restore` docs
>> (https://www.postgresql.org/docs/current/app-pgrestore.html),
>> `--if-exists`
>> states that it is to
>>
>> > Use conditional commands (i.e., add an IF EXISTS clause)
>> > to drop database objects. This option is not valid unless
>> > --clean is also specified.", but not being a SQL expert,
>> > I'm having a hard time deciphering this.
>>
>> Suggestion: Would you consider adding the sentence (or something similar)?
>>
>> > "--clean makes pg_restore drop all objects first,
>> > and --if-exists prevents that non-existent objects
>> > cause a failure."
>> Source: https://stackoverflow.com/a/75136163/1498178
>>
>> This is succinct, and tells exactly what one needs to know. Thank you and
>> have a great day!
>>
>> Appreciatively,
>> Attila
>>
>
> But "failure" is not what happens. If you read the part regarding
> --clean, you will see that it says:
>
> (Unless --if-exists is used, this might generate some *harmless error
> messages*, if any objects were not present in the destination database.) *[emphasis
> added]*
> with extra emphasis on the word "harmless" -- no failure is caused; the
> restore proceeds just fine.
> If anything were to change in this regard, it might be better to
> reconsider what we call the message (i.e.what language pg_restore emits in
> this scenario.) It's true, I suppose, in a literal sense that it's an
> error in that pg_restore couldn't drop a table when instructed to, where no
> such table exists. But pragmatically it doesn't matter, so why not
> reclassify this as a "warning" or a "notice"?
> --Kirk
>
>>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-28 16:36 David G. Johnston <david.g.johnston@gmail.com>
parent: Gulyás Attila <toraritte@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-09-28 16:36 UTC (permalink / raw)
To: Gulyás Attila <toraritte@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
On Thursday, September 28, 2023, Gulyás Attila <toraritte@gmail.com> wrote:
> So `--if-exists` simply suppresses any notice / warning that would occur?
>
That switch causes the drop DDL produced by pg_restore to include if exists
clauses. The server then simply treats the DDL as a no-op if said object
being dropped cannot be found.
>
>> But "failure" is not what happens. If you read the part regarding
>> --clean, you will see that it says:
>>
>> (Unless --if-exists is used, this might generate some *harmless error
>> messages*, if any objects were not present in the destination database.) *[emphasis
>> added]*
>> with extra emphasis on the word "harmless" -- no failure is caused; the
>> restore proceeds just fine.
>> If anything were to change in this regard, it might be better to
>> reconsider what we call the message (i.e.what language pg_restore emits in
>> this scenario.) It's true, I suppose, in a literal sense that it's an
>> error in that pg_restore couldn't drop a table when instructed to, where no
>> such table exists. 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 that
doesn’t exist.
psql and pg_restore, as clients, can choose to ignore the errors they see,
regardless of what kind of error it is, but they don’t take on the added
burden of trying to reclassify errors into something else.
David J.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-28 17:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-09-28 17:03 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Gulyás Attila <toraritte@gmail.com>; pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
"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 that
> doesn’t exist.
> psql and pg_restore, as clients, can choose to ignore the errors they see,
> regardless of what kind of error it is, but they don’t take on the 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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-29 14:30 Gulyás Attila <toraritte@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Gulyás Attila @ 2023-09-29 14:30 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
I would love to see Tom's version in the docs! It is only slightly longer
than the current one, but absolutely clear of the purpose of both, and the
behaviour when used in tandem.
Appreciatively,
Attila
On Thu, Sep 28, 2023 at 1:03 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "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
> that
> > doesn’t exist.
> > psql and pg_restore, as clients, can choose to ignore the errors they
> see,
> > regardless of what kind of error it is, but they don’t take on the 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-29 17:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: Gulyás Attila <toraritte@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-09-29 17:15 UTC (permalink / raw)
To: Gulyás Attila <toraritte@gmail.com>; +Cc: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
=?UTF-8?Q?Guly=C3=A1s_Attila?= <toraritte@gmail.com> writes:
> I would love to see Tom's version in the docs! It is only slightly longer
> than the current one, but absolutely clear of the purpose of both, and the
> behaviour when used in tandem.
Hearing no objections, done at
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=75af0f401f905b947ea14401e8a51f1ba...
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: `pg_restore --if-exists` clarification
@ 2023-09-30 00:02 Gulyás Attila <toraritte@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 8+ messages in thread
From: Gulyás Attila @ 2023-09-30 00:02 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org <pgsql-docs@lists.postgresql.org>
Thank you so much!
Appreciatively,
Attila
On Fri, Sep 29, 2023 at 1:15 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> =?UTF-8?Q?Guly=C3=A1s_Attila?= <toraritte@gmail.com> writes:
> > I would love to see Tom's version in the docs! It is only slightly longer
> > than the current one, but absolutely clear of the purpose of both, and
> the
> > behaviour when used in tandem.
>
> Hearing no objections, done at
>
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=75af0f401f905b947ea14401e8a51f1ba...
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-09-30 00:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-09-28 12:49 `pg_restore --if-exists` clarification PG Doc comments form <noreply@postgresql.org>
2023-09-28 14:32 ` Kirk Parker <khp@equatoria.us>
2023-09-28 16:12 ` Gulyás Attila <toraritte@gmail.com>
2023-09-28 16:36 ` David G. Johnston <david.g.johnston@gmail.com>
2023-09-28 17:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-29 14:30 ` Gulyás Attila <toraritte@gmail.com>
2023-09-29 17:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-09-30 00:02 ` Gulyás Attila <toraritte@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox