Date: Wed, 9 Oct 2024 12:03:14 +0900
From: Yugo Nagata <nagata@sraoss.co.jp>
To: Shinya Kato <Shinya11.Kato@oss.nttdata.com>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: Set AUTOCOMMIT to on in script output by pg_dump
Message-Id: <20241009120314.118a641fa25148c3d564a6ae@sraoss.co.jp>
In-Reply-To: <96ff23a5d858ff72ca8e823a014d16fe@oss.nttdata.com>
References: <96ff23a5d858ff72ca8e823a014d16fe@oss.nttdata.com>
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
MIME-Version: 1.0
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0:
	=?us-ascii?Q?qOHtJqUfw0yJyvylgKbCJabZH7Gu7jZD9IfClnME4NIK0imUGzkXr+dmYGl+?=
 =?us-ascii?Q?PGzNEIh+Zb2YvaQmDWN68qOCxyuNIIFq35d+USj/Q3gLDEtR1mOa/hACzv44?=
 =?us-ascii?Q?F3L6ngGElVJ8454ngsjaQM4vHE0ALRlxSFB/yEDFo7RELIfaSQEwwaqQ8bVk?=
 =?us-ascii?Q?vGhUPzRL46NvaWQ3YoCmNeuEk+RzN9BwUb2kZjvq2iwPkVwg41qJgyCOJsBd?=
 =?us-ascii?Q?RnXWrY/pDBU5KJrR2JUVjzt/SVIeChXEIA93GRSaM6tAa0le6mqu2xSRWi9B?=
 =?us-ascii?Q?wbrDGXWAW/xkh086axCkY5m923UTPwNDwlrY5BR5JP/aATbTneA6t/VtLK0X?=
 =?us-ascii?Q?FrYmMNVpUn4YtrbPKEiDDpaH/HoTSlvvec+lffiGEZ5gQnOv1aO44Lb5t8J0?=
 =?us-ascii?Q?JNWVQtRIpuwe5aGG0392l/EOBxLEq+Rq6S8g3S2u0R6fqfdMfGtANhg9cyUs?=
 =?us-ascii?Q?S82oXxoJQ+mMmDjZFmMOlaIeJAnp/HBIrUD45pSDdRdEhUBLECJ0hvr0hx2G?=
 =?us-ascii?Q?cgROGfv/c8piRIi2jsVA1Dm/yluxdwrV7gPmPPtrKZUHw5vLYumjXqu3P2nt?=
 =?us-ascii?Q?P3QMAPmZk+9hniMJ98b2VwdWMwOTvP6FuSYhUong90FRYkhQl5XgyBTOsPKa?=
 =?us-ascii?Q?2cQJ2bvjKm6mizDb52aGDpAlJ2/kVEDNI/NTfmQr5uQmFJe4zAudBc14N1XH?=
 =?us-ascii?Q?Va9NSOU2xHQUZ1Xn8bEuy+4dQLoLSSPv3k9yQvoFYRLySnVZRdbcWMToEg4n?=
 =?us-ascii?Q?zNnml66lmw4Mc7z/98HrrEMib5DQWUFBtlFmPpBLFcJRgy7OzIZDeB7s7Upa?=
 =?us-ascii?Q?vKcGgIIZsik3W7gBy4Zp8xxlDgZYDr8eo73XwfJtLQpmrGJAVP3aaiKY93mp?=
 =?us-ascii?Q?mOkr4aRxLUJoMLRtySuYVtcmfRQbm+/rZrXRX8PDxcrswrGUk4hiBwg0L5A8?=
 =?us-ascii?Q?TfqdGO6GFOZ97IhP+SWrZy1tRak3COE1DsVOmYQnunHhx3JZEGS0lcSvcHeo?=
 =?us-ascii?Q?SU48O5wxCdbbzkOmTiw67XqXCSdl7OYPKWc9Gc86e0FKkseujYYKxGoICLXl?=
 =?us-ascii?Q?oULUGAlFlLk8VWls0XiYM0mZwk/oLP2DRtGPJny9AiWqhIU1d5+pzAwNmmjZ?=
 =?us-ascii?Q?R0owriFjQpK3p1lvc7w5DcURMhiJTeC2cotvHWOlmboYfARvKep6rU/QYJZm?=
 =?us-ascii?Q?b62vcIMl6NYRlAWZp/fdBR1McpsghfQk/yoy9dJLXaassNQGVRQ3NGCvmVhC?=
 =?us-ascii?Q?UN2un8y+qkDBTw2GcXY12hGT1pgFvWHa6094nLNNyJf3T1o1jMrhg7wbi6Kp?=
 =?us-ascii?Q?ymv1lwiZ6oefyVt5aruEaLhWCuEj9AzdBrYr1QC27dB7l4wKHrIASiB6atXB?=
 =?us-ascii?Q?eKgtBIty5I6ovdzeSVUw2yHP3fN4ML9UawyFFHp/tlJMcwaUs5ufq0i0jgtS?=
 =?us-ascii?Q?PGcDucT3EflODpNS8ZFntjGwx2xsJ4lxkykmTEc9HQ9Dp0kZ78x9oH0isTV7?=
 =?us-ascii?Q?9AormfPiH9845g1pc7iNtiFhRZJxb23iBHXJNJqTgphdnEnDi9MF99WGn4lq?=
 =?us-ascii?Q?l9d/RUeQ3pgbOv7QcHitSZW7F3lZyJHvy0Vbuk0raBmxeaB4G0wjjuBUEX6U?=
 =?us-ascii?Q?02exnvWRXCkhiKnSk0FHh8rMY4jbNBKkco27w9VxXub5?=
X-OriginatorOrg: sraoss.co.jp
X-MS-Exchange-CrossTenant-Network-Message-Id: 8bc53483-acac-40ec-0281-08dce80eeb21
X-MS-Exchange-CrossTenant-AuthSource: TYBP286MB0111.JPNP286.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 09 Oct 2024 03:03:15.2687
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 75db6c32-352e-4063-ae79-cafb8623f6db
X-MS-Exchange-CrossTenant-MailboxType: HOSTED
X-MS-Exchange-CrossTenant-UserPrincipalName: zY2OoxDoOaMFEJqbk/68W8iCC80srtdAk+mWYijgbyxEOe1F63QZWCGsziTjy90EDgdJZ/rkZvTKvTneBV6QJw==
X-MS-Exchange-Transport-CrossTenantHeadersStamped: TYRP286MB4231
List-Id: <pgsql-hackers.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-hackers@lists.postgresql.org>
List-Owner: <mailto:pgsql-hackers-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-hackers>
Archived-At: <https://www.postgresql.org/message-id/20241009120314.118a641fa25148c3d564a6ae%40sraoss.co.jp>
Precedence: bulk

On Wed, 09 Oct 2024 11:10:37 +0900
Shinya Kato <Shinya11.Kato@oss.nttdata.com> 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 <nagata@sraoss.co.jp>