public inbox for [email protected]  

help / color / mirror / Atom feed

From: Tom Lane <[email protected]>
To: David G. Johnston <[email protected]>
Cc: Yugo Nagata <[email protected]>
Cc: Shinya Kato <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Subject: Re: Set AUTOCOMMIT to on in script output by pg_dump
Date: Wed, 09 Oct 2024 00:10:22 -0400
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAKFQuwbnNfH0RYcDnwYkhbXi_VK+yKHaMGvRP9J1vFrLHnze0w@mail.gmail.com>
References: <[email protected]>
	<[email protected]>
	<CAKFQuwbnNfH0RYcDnwYkhbXi_VK+yKHaMGvRP9J1vFrLHnze0w@mail.gmail.com>

"David G. Johnston" <[email protected]> writes:
> On Tuesday, October 8, 2024, Yugo Nagata <[email protected]> wrote:
>> On Wed, 09 Oct 2024 11:10:37 +0900
>> Shinya Kato <[email protected]> wrote:
>>> 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.

> Agreed.  If we aren’t already outputting psql-only stuff I am a strong -1
> 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
> 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.)

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

view thread (15+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Set AUTOCOMMIT to on in script output by pg_dump
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox