From: Japin Li <japinli@hotmail.com>
To: Andrew Dunstan <andrew@dunslane.net>
Cc: Zsolt Parragi <zsolt.parragi@percona.com>,  Euler Taveira
 <euler@eulerto.com>,  =?utf-8?Q?=C3=81lvaro?= Herrera
 <alvherre@kurilemu.de>,  PostgreSQL
 Hackers <pgsql-hackers@lists.postgresql.org>
Subject: Re: pg_get__*_ddl consolidation
In-Reply-To: <555cdee4-c024-4872-9d96-82ef4216239c@dunslane.net> (Andrew
	Dunstan's message of "Tue, 31 Mar 2026 15:42:54 -0400")
References: <202603201311.yhtqmvektawm@alvherre.pgsql>
	<bf49721a-a56f-4bdc-ac48-5d4dc1d01805@dunslane.net>
	<8ec9b67d-939e-4b22-8d56-a5129f92d32d@app.fastmail.com>
	<CAN4CZFNYM5jNA_gLu9miAXg_7c8Z2zf-ePc+0rzidAV3CBs=cw@mail.gmail.com>
	<555cdee4-c024-4872-9d96-82ef4216239c@dunslane.net>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Thu, 02 Apr 2026 21:20:43 +0800
Message-ID: 
 <SY7PR01MB10921A6E1E08A48F3426FE529B651A@SY7PR01MB10921.ausprd01.prod.outlook.com>
Content-Type: text/plain
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY7PR01MB10921:EE_|SY7PR01MB9650:EE_
X-MS-Office365-Filtering-Correlation-Id: 46588418-a8b5-40e8-79a9-08de90baa781
X-MS-Exchange-SLBlob-MailProps: 
	a+H6FLLcF3qkJ+BXrfQxbsvYfyGqqDaQXONsVaK91xaFC1V6x75EXwI5qJehh93I4NtoJTotNjV0sHZq+AEh7m1m2yhNmq//3wpAfrDTrgufiZMDX0IgbgPdzJZyhOqqm42kdMSYlVVVr801I53hqBpRdxP0PjZyMM1twYfwMCbP9r4OfLxdkoFGq9/cwXyP/ePZcEbUTgxeV0ffToa8tbsf0GWp2JKIdmvj0y33wh9ApdE9+1BhfnYlhWUbTyxr3J6qCOAewtnDOBacReS0eQCyk//zP4RxovnYdLZV+AVFW8eDcVwW3WJ3pb/BBcCDDdhZjgAZ9AIXszI+5GAyDhVP9twiC4oZZnYgkNI2ZOd2CdpnkCg6MuhAeNGiBHgpQU1UBd8v3078Qpkt/XWNybd3pfHpFIZnyQ8nw0ORNo1Az6qN9wkfNzSjAy0nyBsC9yfjxLZ+tLJtUOClaC3xybr8611l6A95D3gKQM/ZzXGimW7GrQVuWTfUnpzTiP8OWO0LpMHcWPt6JW9s7/eY02ts2x3nU4own3F1Tc6UC+t1Fv29GB53/xgIIVIzIOXYPOVCGLgXm4z2i9KI4fVTsW9XFr51s7+OG7EVz0Xg3E/xFaq3poMSCJJ/grMru9I3nhviEiu3cvVtv8pjTXuNUMXna8CgsKOkZFf4NQjWeHkZkGTjYs6sR2zyOoyN87A92L6tbVjWeDmlonu63tMTOKwf1XRkqRvkYVz+w6Qm37VoMZJQREXB3TQntkoyuKcoDWWcLAWwKvpqyEYtsV7BpCeWn7p26opIRSCqZCRI0fHBEU2LoSuBJixzWbd6Ng7/
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|51005399006|461199028|12121999013|23021999003|8060799015|19110799012|24121999003|22091999003|5072599009|15080799012|40105399003|440099028|3412199025|10035399007|12091999003;
X-Microsoft-Antispam-Message-Info: 
	=?us-ascii?Q?D1sT2wLl5Y0Irtjf5ye5sEc0zumsdOctP4uS4YN2M0RBL0i4eOmJ8kzx/TRC?=
 =?us-ascii?Q?0CaVIJ/NP/YMLOYgT6fM5pArfNh0RSm0Q6IrH+kr+inVTMOI/dNsmidR9rY4?=
 =?us-ascii?Q?75/jG0Rt5qS9bjmy+Yh1u0Idsvb+V6WDmYDrYj9N3Qq0f7TauR4JGHgCPZRS?=
 =?us-ascii?Q?DK8obqE4hzjQnaoQh44ogA4X2Shkzx+29btmZCnw6LIP+jAsTH3TqYpnaox8?=
 =?us-ascii?Q?jUZQCqivYgaDnpsysGFIFPLyJzqR4t8sMjuqMAIrpA4XZWR3OvEUR9RWdNsF?=
 =?us-ascii?Q?E32Fb8ZnCPh6raggYkdySoaCQ3FoT0CBMp972QviEh6k3DwmnnuTTJ6ahXc6?=
 =?us-ascii?Q?PU8UIt/PPHz6vjvXIkF0g/+MjLaaUOoOAca2wqdzjft/1W6E0np/+0rxWDWH?=
 =?us-ascii?Q?ulFz1AgaqVWLiIp5rMtAL5EjoHt61/sKSI0gn+o+hbyB92M4SRDDmTpqagG3?=
 =?us-ascii?Q?4XMNJixRpt4qfA8PPRoM3/hsc4bnVhDb4Pe44CPSjCIi9mqK0/X7801xD7OC?=
 =?us-ascii?Q?mhPjzT1QqJFm7Puv5QTH+QrdGYDj+guxssJUc/OWOTX2C24GHm4ojFOuiSYU?=
 =?us-ascii?Q?S0BixFvh3RO/9t76CD149KyoeQg/yxCHyWXfqQkpfEqxR4hagbhza4BVhsem?=
 =?us-ascii?Q?KThKLH/7ybNZqDEjaqVZbkcCx/wTtn42Tb47+6mIxZ4rFl9UjG3zGcjwqO+6?=
 =?us-ascii?Q?W8ptbSbQayW1xRggqHn/MklTsON5GwZXSQ2wAAznROTXmL6V5cn2u/wn7GYz?=
 =?us-ascii?Q?v8fKfzfGL21hGrXDhK0FCKi0KbcZy1NoTfMhiQjJNwX0f5cTPo6Snl/8n+BN?=
 =?us-ascii?Q?YhjAUwvxw99IBEBeyufDt62T+Jx9ETzKzj5Hz7CyiZDNv9umu0+a8ZUPN917?=
 =?us-ascii?Q?96C0UE4EsX5in1BPps2QKIgaeur22nW9EQysp4hJwpzjA7WdNhAAishz9jXE?=
 =?us-ascii?Q?wf+/cLrY/TeUfLNzLsWi4rRw8cEllJYiT3Qt6o34mWCL8lrR8H0GIaX/HP7Y?=
 =?us-ascii?Q?xvZIht647HypKbLmQaZ19pNIm8HpqwY9Zaptd0M3XXPplMA=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?us-ascii?Q?pQZfRsZW5HcpmCuW7kMm9f6YXkKdm4sOSJSHDdUrmbyYWUbl1NWM54qkMkjg?=
 =?us-ascii?Q?9K8lefX1spUt8dM///B26zTnccjJERpZFDtIhYMlkp6fxEFGSejXuCk9cySY?=
 =?us-ascii?Q?QIWHFcu2ryqaWqrkolh13XAVRVNH8DMDv3trEHU7nxBjFl/nWx2J5JSRRzIg?=
 =?us-ascii?Q?6Cc1la4LBwIN54mZhW2VceKHJCGuE9kv8/Vqvd0B1dRi5E6owb6XdDoe6jpE?=
 =?us-ascii?Q?rsjHWZJ+fXe9t22GNEfmlyTtFbxXPbLYmiQJdTnGC1cB5Nob3Wji2BGBShPz?=
 =?us-ascii?Q?y4Paf+2l/1XfPVhfVP24TOWM2NZjdxpaR4b5W5QWOGKTa2URowqJ45tu0KPi?=
 =?us-ascii?Q?fAdmWj8Uk/gsInAUYZlqMe8QLUQ3daG4u6cqHehhicFnWfcrO1FF/dGUI4Ut?=
 =?us-ascii?Q?ZY1lr+fxRbUUJltq/puKIefUjL5op+fFRUMjUX/XWg+OCVoQEUdWjxMorlWX?=
 =?us-ascii?Q?ElKv1XN/x81Ybx2cbT4M30m9+cbyMuoHf8EJqq4aQNB/gO18eS8vDhVXkMQm?=
 =?us-ascii?Q?KSi3yzRADavxWKF65PaNPr/RvMArEjFDXVK7kGcBEdIdx3k0v6Z/O+QzKuYZ?=
 =?us-ascii?Q?jxwcoHtR+NBMG/ot9LIJRihp+J7QnpV8F/1ztXutX5nNespj/zE33JsA56xd?=
 =?us-ascii?Q?opoe4kW/ekPe2P68X2kTY2JIgBbwQhHTHAbFBZG/1lwB/usdBY4hAxx81574?=
 =?us-ascii?Q?dWS7A6JY+vR547vcmeAsQSvE9KRHXskzNAvRBJQE9IQtQn2io51aGNnR8ZbF?=
 =?us-ascii?Q?3bESGrCtAopJiyR5aUy3Tc6F50NaPA8wMOrd1cosw05i6bRC3l02vHIKnGpI?=
 =?us-ascii?Q?jHcDsGjgUoazuwdpviY8+XgXXg/9Jcg/NqQ5ChWHv0gC7ejmNABBrUirjwt6?=
 =?us-ascii?Q?eOjBCyeI2iRUDHxa6m16J1it8ZLlEG1IgQ6zKFumsjFrjgBPgyfeBXTkGgwK?=
 =?us-ascii?Q?wPHgoeQ70r10sO0OK023ZliMGyI6PwPeJ3rL+GIwq0S5QYrYglSStQRKtPtA?=
 =?us-ascii?Q?RoBO3//I41Sm8Nb3ld+tFI0qiw9lkSyg6k2AUvAjzBVMiJPv0e1tghY6UN+/?=
 =?us-ascii?Q?EEqngNTVXlkpO/YsJYJ+z37WytY1lcXFh6Wy5ioYC9A80gW4I1sPjwlT7i9B?=
 =?us-ascii?Q?PUdma5BrmtT9e2hm9mq0fJaL+qSQYUFEoJ7geuiuhWQMd1l1q+sC0k4nF2BJ?=
 =?us-ascii?Q?jgagK/wOUXWN3lKozRwvQWALGTxAoufQdIA8Re8X1/4YXJK8/SnzqYfhafj9?=
 =?us-ascii?Q?HCI4ukSkKcS6PBAnfudJ3SJLubWchHcdIXTVXEzj8YHn0bGIrm094tcAkbMs?=
 =?us-ascii?Q?mtuZ6hsTqVBJhuxcWoLlan4mArUwia44TIh9yOgLxAYm8YMMGrhMwwb4VblD?=
 =?us-ascii?Q?jlryg9GDqsgfiB0W6DXvIPcdjDPM?=
X-OriginatorOrg: sct-15-20-9412-4-msonline-outlook-feddd.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 46588418-a8b5-40e8-79a9-08de90baa781
X-MS-Exchange-CrossTenant-AuthSource: SY7PR01MB10921.ausprd01.prod.outlook.com
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 02 Apr 2026 13:20:48.6152
 (UTC)
X-MS-Exchange-CrossTenant-FromEntityHeader: Hosted
X-MS-Exchange-CrossTenant-Id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa
X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 
	00000000-0000-0000-0000-000000000000
X-MS-Exchange-Transport-CrossTenantHeadersStamped: SY7PR01MB9650
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/SY7PR01MB10921A6E1E08A48F3426FE529B651A%40SY7PR01MB10921.ausprd01.prod.outlook.com>
Precedence: bulk


Hi, Andrew

On Tue, 31 Mar 2026 at 15:42, Andrew Dunstan <andrew@dunslane.net> wrote:
> On 2026-03-24 Tu 5:56 PM, Zsolt Parragi wrote:
>> v2 is definitely better, I can confirm the improvements.
>>
>>> including all suggested changes in this thread
>> But I want to point out that the permission check question, and the
>> role membership question is still open.
>>
>> For the membership question
>>
>>> I'm not opposed
>>> to your idea but IMO it should be an option.
>> I'm also fine with leaving it out, but then it should be a mentioned limitation.
>>
>> For v2:
>>
>> Shouldn't the tablespace function also support an owner option
>> similarly to the database function?
>>
>> A pfree(nulls) and pfree(spcname) seem to be missing, all other
>> variables are freed properly.
>>
>> +
>> + /*
>> + * Variables that are marked GUC_LIST_QUOTE were already fully
>> + * quoted before they were put into the setconfig array.  Break
>> + * the list value apart and then quote the elements as string
>> + * literals.
>> + */
>> + if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
>> + {
>>
>> This part seems to be duplicated between the two functions, maybe
>> could be a helper?
>>
>>
>> +ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
>>
>> Maybe it would be useful to also test the "SET search_path TO
>> myschema, public" variant, without quotes?
>>
>>
>> I also want to go back to the datestyle question one more time:
>>
>>> The non-fixed DateStyle is by design. It mimics pg_dumpall.
>> Isn't pg_dump and pg_dumpall inconsistent in this? pg_dump sets it to
>> ISO, pg_dumpall uses DateStyle. So I'm not that sure about the
>> "pg_dumpall works this way" argument because pg_dump works
>> differently. Maybe either of those tools should also be fixed?
>>
>> The pg_dump behavior is actually a bugfix in cf4cee1b, which was never
>> applied to pg_dumpall, so it seems like an oversight to me?
>>
>>> At present, dates are put into a dump in the format specified by the
>>> default datestyle.  This is not portable between installations.
>>>
>>> This patch sets DATESTYLE to ISO at the start of a pg_dump, so that the
>>> dates written into the dump will be restorable onto any database,
>>> regardless of how its default datestyle is set.
>
>
> OK, I hope the attached set addresses all the outstanding
> issues. We're using ISO dates, and there are appropriate permissions
> checks. There is an option to dump role memberships.
>
>

Thanks for updating the v3 patches.  Here are some comments.

v3-0001
=======

1.
+               List       *namelist;
+               bool            first = true;
+
+               /* Parse string into list of identifiers */
+               if (!SplitGUCList(rawval, ',', &namelist))

According to SplitGUCList()'s comment, the caller should call list_free() on
the returned list even on error. Should we also call list_free() on namelist?

v3-0003
=======

1.
+       /* Add OWNER clause */
+       if (!no_owner)
+       {
+               spcowner = GetUserNameFromId(tspForm->spcowner, false);
+               append_ddl_option(&buf, pretty, 4, "OWNER %s",
+                                                 quote_identifier(spcowner));
+               pfree(spcowner);
+       }

The spcowner is only used within the if scope, so we can narrow its scope.

v3-0004
========

1.
+       append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");

I'm curious why WITH TEMPLATE = template0 is hardcoded. For example:

  [local]:1374846 postgres=# create database db01 IS_TEMPLATE true;
  CREATE DATABASE
  [local]:1374846 postgres=# create database db02 template db01;
  CREATE DATABASE
  [local]:1374846 postgres=# select pg_get_database_ddl('db02');
                                                 pg_get_database_ddl
  -----------------------------------------------------------------------------------------------------------------
   CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';
   ALTER DATABASE db02 OWNER TO japin;
  (2 rows)

Is this working as expected?

It seems there's no way to reconstruct the WITH TEMPLATE clause, right?
A comment here would help.

> cheers
>
>
> andrew
>
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com
>
> [2. text/x-patch; v3-0001-Add-infrastructure-for-pg_get_-_ddl-functions.patch]...
>
> [3. text/x-patch; v3-0002-Add-pg_get_role_ddl-function.patch]...
>
> [4. text/x-patch; v3-0003-Add-pg_get_tablespace_ddl-function.patch]...
>
> [5. text/x-patch; v3-0004-Add-pg_get_database_ddl-function.patch]...

-- 
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.