MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="=_223e4f7c9d5d7b5427f2a21f3c60a4b6"
Content-Transfer-Encoding: 7bit
Date: Wed, 09 Oct 2024 11:10:37 +0900
From: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
To: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Set AUTOCOMMIT to on in script output by pg_dump
User-Agent: Roundcube Webmail/1.4.11
Message-ID: <96ff23a5d858ff72ca8e823a014d16fe@oss.nttdata.com>
Archived-At: 
 <https://www.postgresql.org/message-id/96ff23a5d858ff72ca8e823a014d16fe%40oss.nttdata.com>
Precedence: bulk

--=_223e4f7c9d5d7b5427f2a21f3c60a4b6
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII;
 format=flowed

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?

Regards,
Shinya Kato
NTT DATA GROUP CORPORATION
--=_223e4f7c9d5d7b5427f2a21f3c60a4b6
Content-Transfer-Encoding: base64
Content-Type: text/x-diff;
 name=v1-0001-Set-AUTOCOMMIT-to-on-in-script-output-by-pg_dump.patch
Content-Disposition: attachment;
 filename=v1-0001-Set-AUTOCOMMIT-to-on-in-script-output-by-pg_dump.patch;
 size=1595

RnJvbSBlNWI0YTZhYjk1YWI1Yzc5OGVmOGM3ZjcwNjJmYjc2NGE3NGRlMzdhIE1vbiBTZXAgMTcg
MDA6MDA6MDAgMjAwMQpGcm9tOiBTaGlueWEgS2F0byA8U2hpbnlhMTEuS2F0b0Bvc3MubnR0ZGF0
YS5jb20+CkRhdGU6IFdlZCwgOSBPY3QgMjAyNCAxMDoyODozMSArMDkwMApTdWJqZWN0OiBbUEFU
Q0ggdjFdIFNldCBBVVRPQ09NTUlUIHRvIG9uIGluIHNjcmlwdCBvdXRwdXQgYnkgcGdfZHVtcAoK
LS0tCiBzcmMvYmluL3BnX2R1bXAvcGdfYmFja3VwX2FyY2hpdmVyLmMgfCA3ICsrKysrKysKIHNy
Yy9iaW4vcGdfZHVtcC9wZ19kdW1wYWxsLmMgICAgICAgICB8IDYgKysrKysrCiAyIGZpbGVzIGNo
YW5nZWQsIDEzIGluc2VydGlvbnMoKykKCmRpZmYgLS1naXQgYS9zcmMvYmluL3BnX2R1bXAvcGdf
YmFja3VwX2FyY2hpdmVyLmMgYi9zcmMvYmluL3BnX2R1bXAvcGdfYmFja3VwX2FyY2hpdmVyLmMK
aW5kZXggOGMyMGMyNjNjNC4uYjEwNzdhNTUyMSAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9wZ19kdW1w
L3BnX2JhY2t1cF9hcmNoaXZlci5jCisrKyBiL3NyYy9iaW4vcGdfZHVtcC9wZ19iYWNrdXBfYXJj
aGl2ZXIuYwpAQCAtNDcyLDYgKzQ3MiwxMyBAQCBSZXN0b3JlQXJjaGl2ZShBcmNoaXZlICpBSFgp
CiAJCQlhaHByaW50ZihBSCwgIkJFR0lOO1xuXG4iKTsKIAl9CiAKKwkvKgorCSAqIFNldCBBVVRP
Q09NTUlUIHRvIG9uIHdoZW4gZHVtcGluZyBhIHBsYWluLXRleHQgZmlsZSB0byBwcmV2ZW50IGVy
cm9ycworCSAqIHdpdGggU1FMIHN0YXRlbWVudHMgdGhhdCBjYW5ub3QgYmUgZXhlY3V0ZWQgaW5z
aWRlIHRyYW5zYWN0aW9uIGJsb2NrLgorCSAqLworCWlmIChBSC0+Zm9ybWF0ID09IGFyY2hOdWxs
IHx8IHJvcHQtPmZpbGVuYW1lICE9IE5VTEwpCisJCWFocHJpbnRmKEFILCAiXFxzZXQgQVVUT0NP
TU1JVCBvblxuIik7CisKIAkvKgogCSAqIEVzdGFibGlzaCBpbXBvcnRhbnQgcGFyYW1ldGVyIHZh
bHVlcyByaWdodCBhd2F5LgogCSAqLwpkaWZmIC0tZ2l0IGEvc3JjL2Jpbi9wZ19kdW1wL3BnX2R1
bXBhbGwuYyBiL3NyYy9iaW4vcGdfZHVtcC9wZ19kdW1wYWxsLmMKaW5kZXggZTNhZDhmYjI5NS4u
MDA2ZDAyMDFiYyAxMDA2NDQKLS0tIGEvc3JjL2Jpbi9wZ19kdW1wL3BnX2R1bXBhbGwuYworKysg
Yi9zcmMvYmluL3BnX2R1bXAvcGdfZHVtcGFsbC5jCkBAIC01NTMsNiArNTUzLDEyIEBAIG1haW4o
aW50IGFyZ2MsIGNoYXIgKmFyZ3ZbXSkKIAkgKiBkYXRhYmFzZSB3ZSdyZSBjb25uZWN0ZWQgdG8g
YXQgdGhlIG1vbWVudCBpcyBmaW5lLgogCSAqLwogCisJLyoKKwkgKiBTZXQgQVVUT0NPTU1JVCB0
byBvbiB0byBwcmV2ZW50IGVycm9ycyB3aXRoIFNRTCBzdGF0ZW1lbnRzIHRoYXQgY2Fubm90IGJl
CisJICogZXhlY3V0ZWQgaW5zaWRlIHRyYW5zYWN0aW9uIGJsb2NrLgorCSAqLworCWZwcmludGYo
T1BGLCAiXFxzZXQgQVVUT0NPTU1JVCBvblxuXG4iKTsKKwogCS8qIFJlc3RvcmUgd2lsbCBuZWVk
IHRvIHdyaXRlIHRvIHRoZSB0YXJnZXQgY2x1c3RlciAqLwogCWZwcmludGYoT1BGLCAiU0VUIGRl
ZmF1bHRfdHJhbnNhY3Rpb25fcmVhZF9vbmx5ID0gb2ZmO1xuXG4iKTsKIAotLSAKMi40My4wCgo=
--=_223e4f7c9d5d7b5427f2a21f3c60a4b6--