From: Japin Li <japinli@hotmail.com>
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Cc: Chao Li <li.evan.chao@gmail.com>,  =?utf-8?Q?=C3=81lvaro?= Herrera
 <alvherre@kurilemu.de>,  Quan Zongliang <quanzongliang@yeah.net>,
  pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct
 CREATE DATABASE statement
In-Reply-To: 
 <CANxoLDdPqyTLyYhBnekFYkaSn_8DBdtf32cH=MLNJc=+BQP=MQ@mail.gmail.com>
	(Akshay Joshi's message of "Tue, 18 Nov 2025 13:33:10 +0530")
References: 
 <CANxoLDc6FHBYJvcgOnZyS+jF0NUo3Lq_83-rttBuJgs9id_UDg@mail.gmail.com>
	<d6a8bb90-9b55-4124-8c80-b5a5c1ee460c@yeah.net>
	<7daf5cec-4eae-48e0-883e-684476b57531@yeah.net>
	<CANxoLDfWexTBBVgSOabeh-z=-0BTQCEst_E037tej+DLJxtFqw@mail.gmail.com>
	<SY0P300MB153948953051E054E8F69487B6CAA@SY0P300MB1539.AUSP300.PROD.OUTLOOK.COM>
	<CANxoLDdTfsmmx6vMcZfdvW+5GSUjE9Fs5O4Jwiy3aGHiV+Vz8g@mail.gmail.com>
	<FDC0082F-3AB6-4E0F-942A-44831860F7D5@gmail.com>
	<CANxoLDdPqyTLyYhBnekFYkaSn_8DBdtf32cH=MLNJc=+BQP=MQ@mail.gmail.com>
User-Agent: mu4e 1.12.12; emacs 29.3
Date: Wed, 19 Nov 2025 18:17:49 +0800
Message-ID: 
 <SY0P300MB15399C568056B4F187A846FDB6D7A@SY0P300MB1539.AUSP300.PROD.OUTLOOK.COM>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
X-MS-Exchange-MessageSentRepresentingType: 1
X-MS-PublicTrafficType: Email
X-MS-TrafficTypeDiagnostic: SY0P300MB1539:EE_|SY0P300MB0247:EE_
X-MS-Office365-Filtering-Correlation-Id: f3066c86-fe6e-4d0b-b3f8-08de2754e63d
X-MS-Exchange-SLBlob-MailProps: 
	WaIXnCbdHrOH/Bx+3Z5NLPMXpqOo+CqlK49DPE30GYHxra+XQTphUjLM1nLP56CT2O2sGHXtTC90k0Tsaiwnl7rqt1m4Ls4LOiDs8ByOGRijw6VxQWveCzgwG6VC5q/ZE+IuYRqoQ9r3FiL3fYAn5UxtGZ/7m4PjWpsQY6YJeM/HXzdmu3zIPFxbjhJNFoc0PSB0LV6XUs8cwvJr0DlWs5m3kyazp/By9QHaYfy7KzHJe6b71QR2rxDltr+ROqNlvtkzE2O8kq7hEPxI9O8P3TR1xlz79LwGJG21fXzdHcHkGo69hBTKTMg7PHK8I3J7qP+k50WLG1NDghhetlb+N+A7cLJI3CdSHZ6lQjE2bEjD4zhy047DyLbUzl6CKh9C5LSAwyWPblRtQxm94jfGjssPFmsg/9pUbhoreRl5eIZuCjnYBfjEK96fzUn83itb/wqfho4JiuJJzXeb+c1E7zh3pLkvPtCI2XtTR2sZ1Kmw2g2EDTq6qVuEqOn/SL9iW+0ZjYWCM1bRQsi5gcrn2CmrZEV4s09XJ6FYUma3wuYRl52inwZUrmfgw4ooZfcnY3n7bC3Qxz7qgctovwoe2VkDX2CSrd1QMeEz7LXOkSY8+2gf0jpgFwjQW6Iw+ASguwxC1XR7I0hDPxwEO5YBTkSQHB3JYusZMrBT+hbrZgQ/364lpCDIk+pIRs1xYhN3nJXZP+Qbp5WmUMg/f0NVaZRc6FIMnMjTZrA0XfTLXIOInDBFxXfg2SDng8Xxeh/W6TMgCc5XX1U=
X-Microsoft-Antispam: 
	BCL:0;ARA:14566002|19110799012|6090799003|5072599009|39105399006|23021999003|15080799012|8060799015|461199028|11091999009|40105399003|440099028|3412199025;
X-Microsoft-Antispam-Message-Info: 
	=?utf-8?B?Rm5Sb0ZyMGlUczJlYUNueTlmRUlvVlRxUmRUZzJXUEc1MU12Nlc5eXAwY1ZO?=
 =?utf-8?B?UmtXM1pZL091ZFRXS24vTXBUUDgxVmYxU0lneVg3eTR1Q3F0bS8zRUVlclVQ?=
 =?utf-8?B?c3VEMmtqNDRiM293OGVUVVVRblFHdUhvbEJOSkNsdEhzYlRka004YnpNaUpk?=
 =?utf-8?B?SmVxaHVTbXhsb2lJb2JuWEkxS1hlNkJUUWo3VU93RGJkd3kwVHp6U2xvSW5x?=
 =?utf-8?B?dWMyUUl2RStyQlA0aWtSMDY0eG9rNWZPRW5lR2RwY0ttRlYyTml5Wlhsa3p2?=
 =?utf-8?B?YTZhYStMUkNGRlZYSGNHWUZueXpYYmxDdTFhYXN4aldUQXlSYzNjNXZycnFV?=
 =?utf-8?B?UDNqc1MrMFlheGVZeU1Vd1AyVWdGckxsTWVZbmFJVWhiT01qdThReW1JanN2?=
 =?utf-8?B?MytMUEYyQUNlN2kxcTJMK2QyZWRWTCsvN0YyQmFSV3hKM1p0ejdJNkJTNTFr?=
 =?utf-8?B?RlMzM1NiT3JDRUxvSnJOOFNueENSZnhNS01DSEN1TUNzUHF4dzVINDlrMUhG?=
 =?utf-8?B?dlMzR1Z4K2tBNURHM3Q5dm5TVDNLOUlzWEZZQno1NldSekFXUG5hakRCOEZH?=
 =?utf-8?B?QXQzaE1IVWVhUlEveHZBOUlPMjNuSmxyUDE2MmQwWUNOVVhHVXI2U25JUjAv?=
 =?utf-8?B?K3hpNlRFZW1zYkRMUlZqNTZHczUwYzdmWXhZWTZiTElqeVhBdDQ1L2hGd0Vz?=
 =?utf-8?B?cGduaDl2T3VTaEJSVkJTMWx0bXNpem9UbWoxZG5yL0JSWVo2cUdBZnZBR05p?=
 =?utf-8?B?TmUwQzFITnJrTEVMK2d5OGdqc0w2T3Z1Mk1JdWhLdmI1WldjRFpzbXFTc1pW?=
 =?utf-8?B?SjJKTEZUUWFRNXNQelYrOW10VStEcXRsK2I3NEQ0ZVZnMFlSVEZ3TnBzeVo1?=
 =?utf-8?B?ODBaQUtwd2JteDV4cmowWlhoUnFoT0ZuWkZIc1FOUUlQTzJxc0k0d0NCTGc5?=
 =?utf-8?B?ZVZERE1GcE5LOXNjZnJ3eW1FRG5BdmkrOFQ3eWJyWXVDTmM4Ukx5Q0l6SDVS?=
 =?utf-8?B?KzBZczNOdFFyV1lKMGZ1M1p1Mzc5aGNMUHNKcWhaS0Rxd3o4ZllpdXVXTlF0?=
 =?utf-8?B?TGFreTIzSzQ2Qk9pL2tVVGEyT3QxYWthODgvMTh5SnJDZUJqSEFETzdPbGta?=
 =?utf-8?B?a0RPdVFJQU05cXRwb1IzK3lEbnFjUjlWQ200bHhuNzZ4YkR3aXZxSmFYUTFY?=
 =?utf-8?B?R09wTk10ZGVjL0FPVld6NndWelhTZmZzK1BoYUlyR2FNSlBEY0ZDQlJGQ1g3?=
 =?utf-8?B?NnpaUUtETjVMQm8zTjg3QXpyMTZtV2dOMGdOZjhNdGhBNzl6T3RrUVE1YUFI?=
 =?utf-8?B?aDhIM1NVZUtncnhvcmVtcUpzK2pUS0h3WkhWU2M3RXdqR0dQTXhDVi9pNFNH?=
 =?utf-8?B?ajZxZVJoeC9IUzFVVEg0RTdOTXh6Q1lMV0lrRm5KYS9BeG5ldFdZd3VXaVdL?=
 =?utf-8?B?WUdnUmUwWWlVTkxXMFJscFRWN3JGelp3MG9FYWxhOTZDeDl3Mzh4eFNZa3Jl?=
 =?utf-8?B?MXNDZEo0TDIvNFZaWWp2bmhvVFVmRE9JeTFGUjA5V1pSMkRuRzZoSWpCbE8v?=
 =?utf-8?B?cmhka2NIRi9kU3NDSm1MTitkTlBHbm1rVTRmQkJXc1ZPaWtHR2IvVENPL1N2?=
 =?utf-8?Q?AXnk5YjH2NaP//yzsHFVhshiAQMPS8A04p6TbaVcnWkQ=3D?=
X-MS-Exchange-AntiSpam-MessageData-ChunkCount: 1
X-MS-Exchange-AntiSpam-MessageData-0: 
	=?utf-8?B?ZU5xNXMwenNPbytzeEtmWCtldEhaZHd5eURqK1lpWkdqdnJYMExyOEREZ2NE?=
 =?utf-8?B?ZlJ4NG0waXBIOVI5dktWQWVsNFdrQzNLMDcxVnNESkUybzFSbHdTanFDNXZM?=
 =?utf-8?B?UzdNcVdwck9FNGw2T3FJYitXeGFraGtOVndxYWlMUjI1WFgvYUJrRnAwbFI2?=
 =?utf-8?B?RE5EOWJVZU1OcUxqVlZJTlBrbUdvbW5IMmF5K0lxWXhidHZ1dnBEaGVmNFNi?=
 =?utf-8?B?cndMVFVESmhDTEFQeUJhcDAxN3hNaGEzZy9LUmU0dEtzVzUydThqSEZTU0NF?=
 =?utf-8?B?M3VGR3ZPNnRYTWs0ME8xbFJWWHRvRSs5MWNYV1hnTFltQ2dCbVhoZjZmQzNs?=
 =?utf-8?B?OE96S1FqNG0vTzhpTGRnbzVoNWFyY2VwdzJBR0RqbHRUck9vSDduRnIzempq?=
 =?utf-8?B?Vytja1UxVVlNV0FNaDVFVHZwbDVpUGFWayt1aVNNbzZRaEtVdjQrT2llOFM4?=
 =?utf-8?B?bGhkRGtDQUZJOG5iOC82RitLWTdndlg3RXVjeGdyejZnMDcvUGlRMmVBNDk4?=
 =?utf-8?B?cG8wSmVmeU9tSUtZMWFzMEsvNmF6ektUeGh0Y3R5K1c5UWZSSSs1TmYrK3g3?=
 =?utf-8?B?UnhUVWtIOVdQN2lLVUVMWXRETFR5OUw3NzZXS2ZGaGVZcmthQjYvT0hjbXZo?=
 =?utf-8?B?YXlvb1JOai80b3F5RlVINmNNWUY5dFpQR1BvTFhNL3pMMzVwV081UkZ6UElJ?=
 =?utf-8?B?MzZqTzMrN0NhSU4wWDlKdmZmK25lbTV6d3VrVnFnZFRoUWxjQy9uQWpNbkY5?=
 =?utf-8?B?RmVqSUhrZFRHOFd2N3pHbkdIVTBsN2RXVnZicm5vY0k4dDU2czZKaC9ibTBi?=
 =?utf-8?B?dzlsTzRCc21WWmNLVXVBeG5EU2JRdkFZWDRHQ1NXUXFwWDNjY2p0TEY3dnRH?=
 =?utf-8?B?cE1BQVdPcGY1eHlWTm1yTVd0enJRNGdjTVU3OU03MFY3RDdYQUtDYnNpTlVW?=
 =?utf-8?B?NjdIMVgrNHQxc2dVSURCVE9TQStIMVlBMjVudnBVQUVNNVJCbmtQNmh4eHpn?=
 =?utf-8?B?eDcwa2I0cFNOQkpOeGw1VG5IZFJaM1NUNVRlVGNXa0lKQzFXTStTZVNGdXo3?=
 =?utf-8?B?ZVlhWnNjMFJQd2dCeGgybHNOZEs1VUl3WXBkSXFwL1Vod1UrVWk5OXVQNzJp?=
 =?utf-8?B?eE00aHJiOHBUOGtPQXhTYUpmaEs3eGZtSDJKN3dVUmd3Q2IvdVRnVU1wNmgx?=
 =?utf-8?B?UnprRGVmRjk5MHdrRERWN3V5TzB5QzFxWkphZ0l1NXN2UGRjM2lMVGFlZjhr?=
 =?utf-8?B?QW1TNGpNYlR3czdzdHRvZEttOUhpOHdPYVlNWkNRZ1JkcllKdm0xN0VUdzl3?=
 =?utf-8?B?TUlJdFY5cHdOY3ZOS0ZuSlh5eXBlN1ZYVTIrV0lldms5RWdodW1QNHdTL3Bq?=
 =?utf-8?B?V1cwYkQ5MmxzbVZtVVAxUURrdGw3ZjVGalFDWld3dHRKOWk2YThway9hMVhP?=
 =?utf-8?B?elBLUFpxWmZOdVYwa3UvaUVWajYrYVgzVXQ1Wk9aQnY1OTlDdDNJWnVsU05k?=
 =?utf-8?B?Q0M3eW9pTFlkT1JtRWEvT0hxdFBJN2F3cUNlQThBWVhSVGhiSHZjeDY1Sklj?=
 =?utf-8?B?SW9SSENRZWIvNmtiQStMNFZsNUw2NnJqdnBCRC9yd3FBdVlyeW1RNzU3VEl1?=
 =?utf-8?Q?CbAz4MyJ7R9wrh2hAP8kpjc/N+DVZtmjWO6+itx5cI74=3D?=
X-OriginatorOrg: sct-15-20-7719-20-msonline-outlook-fb8bf.templateTenant
X-MS-Exchange-CrossTenant-Network-Message-Id: 
 f3066c86-fe6e-4d0b-b3f8-08de2754e63d
X-MS-Exchange-CrossTenant-AuthSource: SY0P300MB1539.AUSP300.PROD.OUTLOOK.COM
X-MS-Exchange-CrossTenant-AuthAs: Internal
X-MS-Exchange-CrossTenant-OriginalArrivalTime: 19 Nov 2025 10:17:52.9661
 (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: SY0P300MB0247
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/SY0P300MB15399C568056B4F187A846FDB6D7A%40SY0P300MB1539.AUSP300.PROD.OUTLOOK.COM>
Precedence: bulk


Hi Akshay,

Thanks for updating the patch.

On Tue, 18 Nov 2025 at 13:33, Akshay Joshi <akshay.joshi@enterprisedb.com> =
wrote:
> Hi Chao
>
> Thanks for reviewing my patch.
>
> On Tue, Nov 18, 2025 at 5:59=E2=80=AFAM Chao Li <li.evan.chao@gmail.com> =
wrote:
>
>  Hi Akshay,
>
>  I just reviewed v3 and got some comments:
>
>  > On Nov 17, 2025, at 22:34, Akshay Joshi <akshay.joshi@enterprisedb.com=
> wrote:
>  >=20
>  > All the review comments have been addressed in v3 patch.
>
>  1 - ruleutils.c
>  ```
>  +       if (dbForm->datconnlimit !=3D 0)
>  +               get_formatted_string(&buf, prettyFlags, 1, "CONNECTION L=
IMIT =3D %d",
>  +                                                        dbForm->datconn=
limit);
>  ```
>
>  I think this is wrong. Default value of CONNECTION_LIMIT is -1 rather th=
an 0. 0 means no connection is allowed, users
>  should intentionally set the value, thus 0 should be printed.
>
>  2 - ruleutils.c
>  ```
>  +       if (!attrIsNull)
>  +               get_formatted_string(&buf, prettyFlags, 1, "ICU_RULES =
=3D %s",
>  +                                                        quote_identifie=
r(TextDatumGetCString(dbValue)));
>  ```
>
>  ICU_RULES should be omitted if provider is not icu.
>
>  3 - ruleutils.c
>  ```
>  +       if (!HeapTupleIsValid(tupleDatabase))
>  +               ereport(ERROR,
>  +                               errcode(ERRCODE_UNDEFINED_OBJECT),
>  +                               errmsg("database with oid %d does not ex=
ist", dbOid));
>  ```
>
>  I believe all existing code use %u to format oid. I ever raised the same=
 comment to the other get_xxx_ddl patch.
>
>  Fixed all above in the attached v4 patch.=20

1.
Since the STRATEGY and OID parameters are not being reconstructed, should w=
e
document this behavior?

postgres=3D# CREATE DATABASE mydb WITH STRATEGY file_copy OID 19876 IS_TEMP=
LATE true;
CREATE DATABASE
postgres=3D# SELECT pg_get_database_ddl('mydb', true);
            pg_get_database_ddl
--------------------------------------------
 CREATE DATABASE mydb                      +
         WITH OWNER =3D japin                +
                 ENCODING =3D "UTF8"         +
                 LC_COLLATE =3D "en_US.UTF-8"+
                 LC_CTYPE =3D "en_US.UTF-8"  +
                 COLLATION_VERSION =3D "2.39"+
                 LOCALE_PROVIDER =3D 'libc'  +
                 TABLESPACE =3D pg_default   +
                 ALLOW_CONNECTIONS =3D true  +
                 CONNECTION LIMIT =3D -1     +
                 IS_TEMPLATE =3D true;
(1 row)

2.
We can restrict the scope of the dbTablespace variable as follows:

+	if (OidIsValid(dbForm->dattablespace))
+	{
+		char *dbTablespace =3D get_tablespace_name(dbForm->dattablespace);
+		if (dbTablespace)
+			get_formatted_string(&buf, prettyFlags, 2, "TABLESPACE =3D %s",
+								 quote_identifier(dbTablespace));
+	}

> >
> > 7 - For those parameters that have default values should be omitted. Fo=
r
> > example:
> > ```
> > evantest=3D> select pg_get_database_ddl('evantest', true);
> >         pg_get_database_ddl
> > ------------------------------------
> >  CREATE DATABASE evantest          +
> >          WITH                      +
> >          OWNER =3D chaol             +
> >          ENCODING =3D "UTF8"         +
> >          LC_COLLATE =3D "en_US.UTF-8"+
> >          LC_CTYPE =3D "en_US.UTF-8"  +
> >          LOCALE_PROVIDER =3D 'libc'  +
> >          TABLESPACE =3D pg_default   +
> >          ALLOW_CONNECTIONS =3D true  +
> >          CONNECTION LIMIT =3D -1;
> > (1 row)
> > ```
> >
> > I created the database =E2=80=9Cevantest=E2=80=9D without providing any=
 parameter. I think
> > at least OWNER, TABLESPACE and ALLOW_CNONNECTIONS should be omitted. Fo=
r
> > CONNECTION LIMIT, you already have a logic to omit it but the logic has
> > some problem as comment 1.
> >
>=20
>=20
>=20
> IMHO, parameters with default values *should not* be omitted from the
> output of the pg_get_xxx_ddl functions. The primary purpose of these
> functions is to accurately reconstruct the DDL. Including all parameters
> ensures clarity, as not everyone is familiar with the default value of
> every single parameter.

+1

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