pg_restore documentation and --create/--single-transaction limitation

On Mon, Mar 24, 2025 at 5:11 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:

Hi All,
The pg_restore documentation usually mentions the pair of switches
which can not be used together. However, it does not mention that
--create and --single-transaction can not be used together. Here's a
patch fixing the same.

Looking for a precedence, I found that we have mentioned a similar
limitation concerning --data-only and --schema-only only under
--schema and not at both the sections. Maybe it's missing or we chose
to mention it only at one place. But then I am not sure which one
place I should use to mention the new limitation. So, I have added the
note in the sections corresponding to both the switches so that a user
reading either of them knows about the limitation.

Adding this to the next CF to get some attention. It will be good to
ship this doc fix with the PG 18 release though.

--
Best Wishes,
Ashutosh Bapat

On Mon, 2025-03-24 at 17:11 +0530, Ashutosh Bapat wrote:

The pg_restore documentation usually mentions the pair of switches
which can not be used together. However, it does not mention that
--create and --single-transaction can not be used together. Here's a
patch fixing the same.

Looking for a precedence, I found that we have mentioned a similar
limitation concerning --data-only and --schema-only only under
--schema and not at both the sections. Maybe it's missing or we chose
to mention it only at one place. But then I am not sure which one
place I should use to mention the new limitation. So, I have added the
note in the sections corresponding to both the switches so that a user
reading either of them knows about the limitation.

I grepped the source for all incompatible options:

pg_log_error("options -d/--dbname and -f/--file cannot be used together");
pg_fatal("options -d/--dbname and --restrict-key cannot be used together");
pg_fatal("options -s/--schema-only and -a/--data-only cannot be used together");
pg_fatal("options -s/--schema-only and --statistics-only cannot be used together");
pg_fatal("options -a/--data-only and --statistics-only cannot be used together");
pg_fatal("options -a/--data-only and --no-data cannot be used together");
pg_fatal("options -s/--schema-only and --no-schema cannot be used together");
pg_fatal("options --statistics-only and --no-statistics cannot be used together");
pg_fatal("options --statistics and --no-statistics cannot be used together");
pg_fatal("options %s and %s cannot be used together",
"-a/--data-only", "--statistics");
pg_fatal("options %s and %s cannot be used together",
"-s/--schema-only", "--statistics");
pg_fatal("options -c/--clean and -a/--data-only cannot be used together");
pg_fatal("options -1/--single-transaction and --transaction-size cannot be used together");
pg_fatal("options -C/--create and -1/--single-transaction cannot be used together");
pg_fatal("cannot specify both --single-transaction and multiple jobs");

Most of them are pretty obvious and need no documentation. The ones
that are not obvious unless you know the inner workings are that last
two, and the last one is already documented under --jobs.

So I think that your suggestion makes sense.

I tried to improve the English, and I have added the incompatibility
with multiple --jobs to the --single-transaction documentation.

Does that look alright to you?

Yours,
Laurenz Albe

On Tue, Sep 30, 2025 at 3:00 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Mon, 2025-03-24 at 17:11 +0530, Ashutosh Bapat wrote:

The pg_restore documentation usually mentions the pair of switches
which can not be used together. However, it does not mention that
--create and --single-transaction can not be used together. Here's a
patch fixing the same.

Looking for a precedence, I found that we have mentioned a similar
limitation concerning --data-only and --schema-only only under
--schema and not at both the sections. Maybe it's missing or we chose
to mention it only at one place. But then I am not sure which one
place I should use to mention the new limitation. So, I have added the
note in the sections corresponding to both the switches so that a user
reading either of them knows about the limitation.

I grepped the source for all incompatible options:

pg_log_error("options -d/--dbname and -f/--file cannot be used together");
pg_fatal("options -d/--dbname and --restrict-key cannot be used together");
pg_fatal("options -s/--schema-only and -a/--data-only cannot be used together");
pg_fatal("options -s/--schema-only and --statistics-only cannot be used together");
pg_fatal("options -a/--data-only and --statistics-only cannot be used together");
pg_fatal("options -a/--data-only and --no-data cannot be used together");
pg_fatal("options -s/--schema-only and --no-schema cannot be used together");
pg_fatal("options --statistics-only and --no-statistics cannot be used together");
pg_fatal("options --statistics and --no-statistics cannot be used together");
pg_fatal("options %s and %s cannot be used together",
"-a/--data-only", "--statistics");
pg_fatal("options %s and %s cannot be used together",
"-s/--schema-only", "--statistics");
pg_fatal("options -c/--clean and -a/--data-only cannot be used together");
pg_fatal("options -1/--single-transaction and --transaction-size cannot be used together");
pg_fatal("options -C/--create and -1/--single-transaction cannot be used together");
pg_fatal("cannot specify both --single-transaction and multiple jobs");

Most of them are pretty obvious and need no documentation. The ones
that are not obvious unless you know the inner workings are that last
two, and the last one is already documented under --jobs.

Thanks a lot for the research and pointing out missing --jobs.

So I think that your suggestion makes sense.

I tried to improve the English, and I have added the incompatibility
with multiple --jobs to the --single-transaction documentation.

Does that look alright to you?

@@ -541,7 +545,9 @@ PostgreSQL documentation
emitted commands in <command>BEGIN</command>/<command>COMMIT</command>). This
ensures that either all the commands complete successfully, or no
changes are applied. This option implies
- <option>--exit-on-error</option>.
+ <option>--exit-on-error</option>. It cannot be used together with
+ <option>--create</option>, which switches database connections, or with

I didn't understand ", which switches ..." part. The code comment says
/*
* -C is not compatible with -1, because we can't create a database inside
* a transaction block.
*/
if (opts->createDB && opts->single_txn)
pg_fatal("options -C/--create and -1/--single-transaction cannot be
used together");

It doesn't say anything about switching connections. But it's too low
level of detail, which we may just eliminate.

+ multiple <option>--jobs</option>.

This seems to be confusing. I read it as --single-transaction can not
be used when there are multiple --jobs specifications e.g.
--single-transaction --jobs 1 --jobs 2. Maybe just say " multiple
jobs". Even corresponding --jobs documentation says

pipe or standard input). Also, multiple
jobs cannot be used together with the
option <option>--single-transaction</option>.

Attached patch with those changes.

--
Best Wishes,
Ashutosh Bapat

On Tue, 2025-09-30 at 17:01 +0530, Ashutosh Bapat wrote:

Thanks a lot for the research and pointing out missing --jobs.

So I think that your suggestion makes sense.

I tried to improve the English, and I have added the incompatibility
with multiple --jobs to the --single-transaction documentation.

Does that look alright to you?

@@ -541,7 +545,9 @@ PostgreSQL documentation
emitted commands in <command>BEGIN</command>/<command>COMMIT</command>). This
ensures that either all the commands complete successfully, or no
changes are applied. This option implies
- <option>--exit-on-error</option>.
+ <option>--exit-on-error</option>. It cannot be used together with
+ <option>--create</option>, which switches database connections, or with

I didn't understand ", which switches ..." part. The code comment says
/*
* -C is not compatible with -1, because we can't create a database inside
* a transaction block.
*/
if (opts->createDB && opts->single_txn)
pg_fatal("options -C/--create and -1/--single-transaction cannot be
used together");

It doesn't say anything about switching connections. But it's too low
level of detail, which we may just eliminate.

+ multiple <option>--jobs</option>.

This seems to be confusing. I read it as --single-transaction can not
be used when there are multiple --jobs specifications e.g.
--single-transaction --jobs 1 --jobs 2. Maybe just say " multiple
jobs". Even corresponding --jobs documentation says

pipe or standard input). Also, multiple
jobs cannot be used together with the
option <option>--single-transaction</option>.

Attached patch with those changes.

I agree with your simpler version. I'll mark the patch "ready for committer".

Yours,
Laurenz Albe

On Tue, Sep 30, 2025 at 6:06 PM Laurenz Albe <laurenz.albe@cybertec.at> wrote:

On Tue, 2025-09-30 at 17:01 +0530, Ashutosh Bapat wrote:

Thanks a lot for the research and pointing out missing --jobs.

So I think that your suggestion makes sense.

I tried to improve the English, and I have added the incompatibility
with multiple --jobs to the --single-transaction documentation.

Does that look alright to you?

@@ -541,7 +545,9 @@ PostgreSQL documentation
emitted commands in <command>BEGIN</command>/<command>COMMIT</command>). This
ensures that either all the commands complete successfully, or no
changes are applied. This option implies
- <option>--exit-on-error</option>.
+ <option>--exit-on-error</option>. It cannot be used together with
+ <option>--create</option>, which switches database connections, or with

I didn't understand ", which switches ..." part. The code comment says
/*
* -C is not compatible with -1, because we can't create a database inside
* a transaction block.
*/
if (opts->createDB && opts->single_txn)
pg_fatal("options -C/--create and -1/--single-transaction cannot be
used together");

It doesn't say anything about switching connections. But it's too low
level of detail, which we may just eliminate.

+ multiple <option>--jobs</option>.

This seems to be confusing. I read it as --single-transaction can not
be used when there are multiple --jobs specifications e.g.
--single-transaction --jobs 1 --jobs 2. Maybe just say " multiple
jobs". Even corresponding --jobs documentation says

pipe or standard input). Also, multiple
jobs cannot be used together with the
option <option>--single-transaction</option>.

Attached patch with those changes.

I agree with your simpler version. I'll mark the patch "ready for committer".

Thanks.

--
Best Wishes,
Ashutosh Bapat