Subject: Re: Set AUTOCOMMIT to on in script output by pg_dump

From: Yugo Nagata <[email-masked]>
Date: Wed, 9 Oct 2024 12:03:14 +0900

On Wed, 09 Oct 2024 11:10:37 +0900
Shinya Kato <[email-masked]> wrote:

> Hi hackers!
> 
> When SQL scripts created with pg_dump/pg_dumpall/pg_restore are executed 
> in psql with AUTOCOMMIT turned off, they will not succeed in many cases.
> This is because the script contains SQL statements that cannot be 
> executed within a transaction block.
> 
> If you simply add set AUTOCOMMIT on to the scripts created by 
> pg_dump/pg_dumpall/pg_restore, they will work fine.
> A patch is attached
> 
> No documentation has been added as we could not find any documentation 
> on the details in the script.
> 
> Do you think?

I am not sure if it is good to include psql's meta-command in pg_dump/pg_dumpall
results. Can we assume users will always use psql to restore the pg_dump results?

Regards,
Yugo Nagata

-- 
Yugo Nagata <[email-masked]>

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 8 Oct 2024 20:53:15 -0700

> results. Can we assume users will always use psql to restore the pg_dump

Agreed.  If we aren’t already outputting psql-only stuff I am a strong -1
for making this the first such case.

It would be nice to describe exactly when there is a problem as well since
very few things require being outside of a transaction.  There might be
documentation or code patches possible here to improve matters (like adding
a switch to output begin/commit in the places we’re a user might want
single-transaction behavior) but this approach breaks well-established
encapsulation and overrides user expectations in a bad way (since
autocommit=on is the default they choose to turn it off so turning it back
on silently - not even documented - is bad.)

David J.

---

From: Tatsuo Ishii <[email-masked]>
Date: Wed, 09 Oct 2024 12:58:22 +0900 (JST)

> for making this the first such case.

I think the pg_dumpall output already includes "\connect".

> on silently - not even documented - is bad.)

+1.

Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

---

From: Tom Lane <[email-masked]>
Date: Wed, 09 Oct 2024 00:10:22 -0400

> for making this the first such case.

I really doubt that this is the only way in which you can break a
pg_dump script by executing it in a non-default psql environment.
We'd likely be better advised to spend some documentation effort
recommending that pg_dump scripts be executed under "psql --no-psqlrc".

If AUTOCOMMIT were a mainstream feature then maybe it'd be worth
doing something about this, but IMO it's a deprecated backwater,
so I'm not very excited about it.

If we do want to do something about it, the patch needs more thought
about where to put the additional output.  As an example, it looks
like it breaks the expectation that pg_dump-to-text should generate
output identical to pg_dump-to-archive followed by pg_restore-to-text.

> ... but this approach breaks well-established

That particular angle doesn't bother me so much, because pg_dump
scripts already feel free to change search_path as well as a bunch
of other server parameters.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 8 Oct 2024 21:37:38 -0700

> recommending that pg_dump scripts be executed under "psql --no-psqlrc".

+1

Reinforcing that our output script basically assumes a default execution
environment seems worth mentioning even if it seems self-evident once it’s
said.

> of other server parameters.

I wasn’t referring to the idea these should be restorable on non-PostgreSQL
systems though, only that if someone wanted to just open a connection in
their rust driver and send this text through that session it will (mostly?)
work.

pg_dumpall, though, is fundamentally tied to psql if databases are dumped,
if the resultant script has to be platform independently executable.  I’m
open to a patch addressing this more narrowly but I’m still thinking that
we should be telling the user to use defaults instead of enforcing
ourselves.

David J.

---

From: Yugo Nagata <[email-masked]>
Date: Wed, 9 Oct 2024 15:15:08 +0900

On Tue, 8 Oct 2024 21:37:38 -0700

+1

Regards,
Yugo Nagata

---

From: Shinya Kato <[email-masked]>
Date: Thu, 10 Oct 2024 14:56:29 +0900

Thank you all for the comments!

>> environment seems worth mentioning even if it seems self-evident once 

While adding to the documentation is sufficient if users use it 
correctly, users often behave unexpectedly. My intention was to 
implement it in a way that works without issues even if misused. 
However, since the prevailing opinion seems to favor simply updating the 
documentation, I will proceed with that approach.

A new patch is attached.
I am not a native English, so corrections to the texts are welcome.

-- 
Regards,
Shinya Kato
NTT DATA GROUP CORPORATION

From f2ac5156b8fc02b6b529d3aeef16d80f21bbe08c Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email-masked]>
Date: Thu, 10 Oct 2024 14:50:39 +0900
Subject: [PATCH v2] doc: Add --no-psqlrc in pg_dump/pg_dumpall docs

---
 doc/src/sgml/backup.sgml         | 16 +++++++++-------
 doc/src/sgml/ref/pg_dump.sgml    |  9 ++++++++-
 doc/src/sgml/ref/pg_dumpall.sgml | 11 ++++++++++-
 3 files changed, 27 insertions(+), 9 deletions(-)

diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml
index e4e4c56cf1..21ad34f4e0 100644
--- a/doc/src/sgml/backup.sgml
+++ b/doc/src/sgml/backup.sgml
@@ -106,10 +106,10 @@ pg_dump <replaceable class="parameter">dbname</replaceable> &gt; <replaceable cl
 
    <para>
     Text files created by <application>pg_dump</application> are intended to
-    be read in by the <application>psql</application> program. The
-    general command form to restore a dump is
+    be read in by the <application>psql</application> program with its default
+    settings. The general command form to restore a dump is
 <synopsis>
-psql <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class="parameter">dumpfile</replaceable>
+psql --no-psqlrc <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class="parameter">dumpfile</replaceable>
 </synopsis>
     where <replaceable class="parameter">dumpfile</replaceable> is the
     file output by the <application>pg_dump</application> command. The database <replaceable
@@ -117,7 +117,9 @@ psql <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class
     command, so you must create it yourself from <literal>template0</literal>
     before executing <application>psql</application> (e.g., with
     <literal>createdb -T template0 <replaceable
-    class="parameter">dbname</replaceable></literal>).  <application>psql</application>
+    class="parameter">dbname</replaceable></literal>). To use
+    <application>psql</application> with its default settings, use the
+    <option>--no-psqlrc</option> option. <application>psql</application>
     supports options similar to <application>pg_dump</application> for specifying
     the database server to connect to and the user name to use. See
     the <xref linkend="app-psql"/> reference page for more information.
@@ -141,7 +143,7 @@ psql <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class
     behavior and have <application>psql</application> exit with an
     exit status of 3 if an SQL error occurs:
 <programlisting>
-psql --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> &lt; <replaceable>dumpfile</replaceable>
+psql --no-psqlrc --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> &lt; <replaceable>dumpfile</replaceable>
 </programlisting>
     Either way, you will only have a partially restored database.
     Alternatively, you can specify that the whole dump should be
@@ -160,7 +162,7 @@ psql --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> &lt; <replaceable>
     write to or read from pipes makes it possible to dump a database
     directly from one server to another, for example:
 <programlisting>
-pg_dump -h <replaceable>host1</replaceable> <replaceable>dbname</replaceable> | psql -h <replaceable>host2</replaceable> <replaceable>dbname</replaceable>
+pg_dump -h <replaceable>host1</replaceable> <replaceable>dbname</replaceable> | psql --no-psqlrc -h <replaceable>host2</replaceable> <replaceable>dbname</replaceable>
 </programlisting>
    </para>
 
@@ -205,7 +207,7 @@ pg_dumpall &gt; <replaceable>dumpfile</replaceable>
 </synopsis>
     The resulting dump can be restored with <application>psql</application>:
 <synopsis>
-psql -f <replaceable class="parameter">dumpfile</replaceable> postgres
+psql --no-psqlrc -f <replaceable class="parameter">dumpfile</replaceable> postgres
 </synopsis>
     (Actually, you can specify any existing database name to start from,
     but if you are loading into an empty cluster then <literal>postgres</literal>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index ffc29b04fb..b6c33c3c2f 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1636,6 +1636,13 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    option will be automatically enabled by the subscriber if the subscription
    had been originally created with <literal>two_phase = true</literal> option.
   </para>
+
+  <para>
+   It is generally recommended to use the <option>--no-psqlrc</option> option
+   when restoring a database from a <application>pg_dump</application> script
+   to ensure a clean restore process and prevent potential conflicts with
+   existing <application>psql</application> configurations.
+  </para>
  </refsect1>
 
  <refsect1 id="pg-dump-examples" xreflabel="Examples">
@@ -1653,7 +1660,7 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    <literal>newdb</literal>:
 
 <screen>
-<prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
+<prompt>$</prompt> <userinput>psql --no-psqlrc -d newdb -f db.sql</userinput>
 </screen>
   </para>
 
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 4d7c046468..f8db63326a 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -817,6 +817,15 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
    database creation will fail for databases in non-default
    locations.
   </para>
+
+  <para>
+   It is generally recommended to use the <option>--no-psqlrc</option> option
+   when restoring a database from a <application>pg_dumpall</application> script
+   to ensure a clean restore process and prevent potential conflicts with
+   existing <application>psql</application> configurations. Additionally,
+   because the script includes <application>psql</application> meta-commands,
+   it is incompatible with clients other than <application>psql</application>.
+  </para>
  </refsect1>

@@ -833,7 +842,7 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
   <para>
    To restore database(s) from this file, you can use:
 <screen>
-<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
+<prompt>$</prompt> <userinput>psql --no-psqlrc -f db.out postgres</userinput>
 </screen>
    It is not important to which database you connect here since the
    script file created by <application>pg_dumpall</application> will
-- 
2.43.0

---

From: Shinya Kato <[email-masked]>
Date: Thu, 17 Oct 2024 09:32:41 +0900

> I am not a native English, so corrections to the texts are welcome.

I created a commit fest entry.
https://commitfest.postgresql.org/50/5306/

---

From: Robert Treat <[email-masked]>
Date: Fri, 17 Jan 2025 10:45:46 -0500

> Thank you all for the comments!
<snip>
> I am not a native English, so corrections to the texts are welcome.

This looks pretty good to me. I think there are a couple of minor
grammar changes that could be made, and I think the pg_dumpall section
could use a couple tweaks, specifically 1) not all incantations of
pg_dumpall emit psql meta commands (-g comes to mind quickly) and ISTR
some non-psql clients honor psql meta commands, so I would lessen the
language around incompatibility, and 2) I think adding an explicit -f
for the database name in the pg_dumpall is clearer, and mirrors the
pg_dump example.

suggested diffs attached, let me know if you would like a consolidated patch

Robert Treat
https://xzilla.net

---

From: Tom Lane <[email-masked]>
Date: Fri, 17 Jan 2025 15:11:10 -0500

> suggested diffs attached, let me know if you would like a consolidated patch

Sadly, the cfbot is now confused since it doesn't understand the idea
of an incremental patch.  Somebody please post a consolidated patch.

For myself, I'd suggest writing the examples with -X not --no-psqlrc.
-X is shorter, it's more likely to be what people would actually
type, and it's not jarringly inconsistent with the adjacent use
of -h and other short-form switches.  We can write the added
paragraphs like 

   It is generally recommended to use the <option>-X</option>
   (<option>--no-psqlrc</option>) option when restoring a database ...

to provide clarity about what the switch does.

			regards, tom lane

---

From: Shinya Kato <[email-masked]>
Date: Wed, 22 Jan 2025 22:01:48 +0900

Hi, thank you for the reviews.

> for the database name in the pg_dumpall is clearer, and mirrors the

Thanks, I had no reason to oppose it, so I reflected that in the patch.

> of an incremental patch.  Somebody please post a consolidated patch.

I've attached new consolidated patch.

> to provide clarity about what the switch does.

I agree to it and fixed the patch.

From 664f89f8467f356024fe6e4e4ac0369b65238d41 Mon Sep 17 00:00:00 2001
From: Shinya Kato <[email-masked]>
Date: Wed, 22 Jan 2025 21:53:19 +0900
Subject: [PATCH v3] doc: Add --no-psqlrc in pg_dump pg_dumpall docs

---
 doc/src/sgml/backup.sgml         | 16 +++++++++-------
 doc/src/sgml/ref/pg_dump.sgml    | 10 +++++++++-
 doc/src/sgml/ref/pg_dumpall.sgml | 14 ++++++++++++--
 3 files changed, 30 insertions(+), 10 deletions(-)

index e4e4c56cf1..dd38a8b220 100644
 
    <para>
+    be read in by the <application>psql</application> program using its default
+psql -X <replaceable class="parameter">dbname</replaceable> &lt; <replaceable class="parameter">dumpfile</replaceable>
+    <option>-X</option> (<option>--no-psqlrc</option>) option. <application>psql</application>
+psql -X --set ON_ERROR_STOP=on <replaceable>dbname</replaceable> &lt; <replaceable>dumpfile</replaceable>
+pg_dump -h <replaceable>host1</replaceable> <replaceable>dbname</replaceable> | psql -X -h <replaceable>host2</replaceable> <replaceable>dbname</replaceable>
 </programlisting>
    </para>
 
@@ -205,7 +207,7 @@ pg_dumpall &gt; <replaceable>dumpfile</replaceable>
 </synopsis>
     The resulting dump can be restored with <application>psql</application>:
 <synopsis>
-psql -f <replaceable class="parameter">dumpfile</replaceable> postgres
+psql -X -f <replaceable class="parameter">dumpfile</replaceable> postgres
 </synopsis>
     (Actually, you can specify any existing database name to start from,
     but if you are loading into an empty cluster then <literal>postgres</literal>
diff --git a/doc/src/sgml/ref/pg_dump.sgml b/doc/src/sgml/ref/pg_dump.sgml
index d66e901f51..8b572cdec0 100644
--- a/doc/src/sgml/ref/pg_dump.sgml
+++ b/doc/src/sgml/ref/pg_dump.sgml
@@ -1636,6 +1636,14 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    option will be automatically enabled by the subscriber if the subscription
    had been originally created with <literal>two_phase = true</literal> option.
   </para>
+
+  <para>
+   It is generally recommended to use the <option>-X</option>
+   (<option>--no-psqlrc</option>) option when restoring a database from a
+   <application>pg_dump</application> script to ensure a clean restore process
+   and prevent potential conflicts with existing <application>psql</application>
+   configurations.
+  </para>
  </refsect1>
 
  <refsect1 id="pg-dump-examples" xreflabel="Examples">
@@ -1653,7 +1661,7 @@ CREATE DATABASE foo WITH TEMPLATE template0;
    <literal>newdb</literal>:
 
 <screen>
-<prompt>$</prompt> <userinput>psql -d newdb -f db.sql</userinput>
+<prompt>$</prompt> <userinput>psql -X -d newdb -f db.sql</userinput>
 </screen>
   </para>
 
diff --git a/doc/src/sgml/ref/pg_dumpall.sgml b/doc/src/sgml/ref/pg_dumpall.sgml
index 014f279258..bfbe305b08 100644
--- a/doc/src/sgml/ref/pg_dumpall.sgml
+++ b/doc/src/sgml/ref/pg_dumpall.sgml
@@ -817,6 +817,16 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
    database creation will fail for databases in non-default
    locations.
   </para>
+
+  <para>
+   It is generally recommended to use the <option>-X</option>
+   (<option>--no-psqlrc</option>) option when restoring a database from a
+   <application>pg_dumpall</application> script to ensure a clean restore
+   process and prevent potential conflicts with existing
+   <application>psql</application> configurations. Additionally,
+   because the script can include <application>psql</application> meta-commands,
+   it may be incompatible with clients other than <application>psql</application>.
+  </para>
  </refsect1>

@@ -833,9 +843,9 @@ exclude database <replaceable class="parameter">PATTERN</replaceable>
   <para>
    To restore database(s) from this file, you can use:
 <screen>
-<prompt>$</prompt> <userinput>psql -f db.out postgres</userinput>
+<prompt>$</prompt> <userinput>psql -X -f db.out -d postgres</userinput>
 </screen>
-   It is not important to which database you connect here since the
+   It is not important which database you connect to here since the
    script file created by <application>pg_dumpall</application> will
    contain the appropriate commands to create and connect to the saved
    databases.  An exception is that if you specified <option>--clean</option>,
-- 
2.39.3

---

From: Robert Treat <[email-masked]>
Date: Wed, 22 Jan 2025 16:33:45 -0500

> I agree to it and fixed the patch.

LGTM

Robert Treat
https://xzilla.net

---

From: Tom Lane <[email-masked]>
Date: Sat, 25 Jan 2025 12:45:34 -0500

> LGTM

LGTM too.  Pushed with a couple of very minor tweaks.

			regards, tom lane

---

From: Shinya Kato <[email-masked]>
Date: Mon, 27 Jan 2025 09:23:15 +0900

> LGTM too.  Pushed with a couple of very minor tweaks.

Thank you for pushing!