Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vL0JY-0006CX-27 for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Nov 2025 14:34:44 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1vL0JX-0007YF-0t for pgsql-hackers@arkaria.postgresql.org; Mon, 17 Nov 2025 14:34:43 +0000 Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vL0JW-0007Y5-2p for pgsql-hackers@lists.postgresql.org; Mon, 17 Nov 2025 14:34:43 +0000 Received: from mail-ej1-x62b.google.com ([2a00:1450:4864:20::62b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1vL0JT-0000fP-35 for pgsql-hackers@postgresql.org; Mon, 17 Nov 2025 14:34:41 +0000 Received: by mail-ej1-x62b.google.com with SMTP id a640c23a62f3a-b73a9592fb8so117140566b.1 for ; Mon, 17 Nov 2025 06:34:38 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1763390078; x=1763994878; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=WLglJsnoNjc19BsexyAIxGbk7c+olSlJtV+9BJ8YlwA=; b=B/pnMQx742o136VfXnWVNzxDiFwCCS1Ph9kYanwyqnqEEn/d2lketuW3K49vO9o+UL 9s1R/uZGVYLCDU/36z6IG5QOs+/e1OK52b6BjfWqRDgv/Ww8jpWdoB5VeBSwI0AN2Fkd UWHZQ1nTETciu7AzYevvkzIsXgk0OnC59vzvcyzCEVpbZqUQtk6QLcf4kl8JtABBwby0 EXJe0Nco0sKd25+lb6ksVJa8y4rikNfXq1RmTQhJA1Za0QUJfwho5NjFXUwdZE4cY1IL SH3t+PPpUJ9H78kLkhoOf40MDwC6S3N9vqd8ouEa8ucTV5EexSOVsZ7+zqIlS8WSiaLE IxFg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1763390078; x=1763994878; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=WLglJsnoNjc19BsexyAIxGbk7c+olSlJtV+9BJ8YlwA=; b=ZTTPSOjistagcQTjbEVOGXcDTNpHfuZcmscZhlbMEiqa+ptS4nUE/a+QKWjY6jCTlm x6+jeEvk7INleghoV46trpMp/P39SkDRV7axJ9GguzB+8iB8NQ+He0pH0KZEySiSqwXM f2hPfw7tc7/PwKoHZ0v9xEMXzoemvNc85Kxvtqyb+hllXlw3EANGnu+8TPrqUcWuGp1+ x3B8uhms7OFFtgxz95rj2JP+hcQNxWZ1ri2ojjinYikhsVxOGZe5VrVaHw8sShvLPd1U m+GK9gBwIZgHsLgclvi9kLIih2wmPWPddEhx7wlX2OYlSZutWOYUVpeRd4ruhtQyMTpn 1BqA== X-Forwarded-Encrypted: i=1; AJvYcCWWSIB6x0K4Vg615wLZWY2wbZGnPmc73EV5kVQnNcqYaKdj//OVvNM5b4qXF7e0VXEJ/Tx39VcYLMkAb5Xf@postgresql.org X-Gm-Message-State: AOJu0YzYsQIlA2gbJsDiKmfvi5Dwk5YC2jCb3nJFo1xnY2jKnukpMwHC L5ctvgBtIBop1U83AYAnPvsB0U4tUKhLFosHwFH9BOhi9XfR4xOD3sZQtpbCS1RWtoE9vc9SQTB pW4Tz+oMaqxoGZpVoVwaqbrs8MtbHdowjYHLRU/Rw X-Gm-Gg: ASbGnct4f3qIWNl873aDFVfDgADGF61zCAvNRf9yokgt4Bc6cRIqYOkUtMeeqq+C0nL xaWn/v7KXEBrf/La/nsEML3pEHQhebjjUl9lS8C+kQ3xB+G0y/NmvsT/5afMZj1Nhe5bVHlYwMw oMfMMjmBlAGZXDfgAC+4tjRwjEN460P2Eq5f4r7nb7Qp7WZFbSq+ScQWpNObBwBXrbVTf0T4XXq AYdIFIQ5Ti1Ek5zqiFO2+yCcdGjC5o9ksSc96YIL1KUUHmRAA2J84wbzcAiJPdR+b0TLlQAB+r5 mx3EhRM= X-Google-Smtp-Source: AGHT+IGkAAT/AKbkIOU/CemT1qSHEILpMD1qvkrKnAH8cbrygdZiRDdUWdDfazLWiOPwpzfm2DfBw0xNH0hKoJ3Jnxk= X-Received: by 2002:a17:907:9445:b0:b73:1b97:5ddd with SMTP id a640c23a62f3a-b7348103d4dmr748227166b.8.1763390077829; Mon, 17 Nov 2025 06:34:37 -0800 (PST) MIME-Version: 1.0 References: <7daf5cec-4eae-48e0-883e-684476b57531@yeah.net> In-Reply-To: From: Akshay Joshi Date: Mon, 17 Nov 2025 20:04:26 +0530 X-Gm-Features: AWmQ_bkaUvX2X0OpKabhiNB3eE2oIH-YRCQzxbY9IYRasdgqjztuxPEQ61_ENis Message-ID: Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE DATABASE statement To: Japin Li Cc: Quan Zongliang , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000309b9e0643cb3fe3" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000309b9e0643cb3fe3 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Fri, Nov 14, 2025 at 11:19=E2=80=AFAM Japin Li wro= te: > On Thu, Nov 13, 2025 at 02:02:30PM +0530, Akshay Joshi wrote: > > On Thu, Nov 13, 2025 at 10:18=E2=80=AFAM Quan Zongliang > > wrote: > > > > > > > > > > > On 11/13/25 12:17 PM, Quan Zongliang wrote: > > > > > > > > > > > > On 11/12/25 8:04 PM, Akshay Joshi wrote: > > > >> Hi Hackers, > > > >> > > > >> I=E2=80=99m submitting a patch as part of the broader Retail DDL F= unctions > > > >> project described by Andrew Dunstan https://www.postgresql.org/ > > > >> message- id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net > > > >> > > >> cb1e56f2e3e9%40dunslane.net> > > > >> > > > >> This patch adds a new system function > > > >> pg_get_database_ddl(database_name/ database_oid, pretty), which > > > >> reconstructs the CREATE DATABASE statement for a given database na= me > > > >> or database oid. When the pretty flag is set to true, the function > > > >> returns a neatly formatted, multi-line DDL statement instead of a > > > >> single-line statement. > > > >> > > > >> *Usage examples:* > > > >> > > > >> 1) SELECT pg_get_database_ddl('test_get_database_ddl_builtin'); -= - > > > >> *non-pretty formatted DDL* > > > >> pg_get_database_ddl > > > >> > > > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------ > > > >> CREATE DATABASE test_get_database_ddl_builtin WITH OWNER =3D > > > >> regress_ddl_database ENCODING =3D "UTF8" LC_COLLATE =3D "C" LC_CTY= PE =3D > "C" > > > >> BUILTIN_LOCALE =3D "C.UTF-8" COLLATION_VERSION =3D "1" LOCALE_PROV= IDER =3D > > > >> 'builtin' TABLESPACE =3D pg_default ALLOW_CONNECTIONS =3D true > CONNECTION > > > >> LIMIT =3D -1; > > > >> > > > >> > > > >> 2) SELECT pg_get_database_ddl('test_get_database_ddl_builtin', > true); > > > >> -- *pretty formatted DDL* > > > >> > > > >> CREATE DATABASE test_get_database_ddl_builtin > > > >> WITH > > > >> OWNER =3D regress_ddl_database > > > >> ENCODING =3D "UTF8" > > > >> LC_COLLATE =3D "C" > > > >> LC_CTYPE =3D "C" > > > >> BUILTIN_LOCALE =3D "C.UTF-8" > > > >> COLLATION_VERSION =3D "1" > > > >> LOCALE_PROVIDER =3D 'builtin' > > > >> TABLESPACE =3D pg_default > > > >> ALLOW_CONNECTIONS =3D true > > > >> CONNECTION LIMIT =3D -1; > > > >> > > > >> 3) SELECT pg_get_database_ddl(16835); -- *non-pretty formatte= d > > > >> DDL for OID* > > > >> 4) SELECT pg_get_database_ddl(16835, true); -- *pretty formatted > DDL > > > >> for OID* > > > >> > > > >> The patch includes documentation, in-code comments, and regression > > > >> tests, all of which pass successfully. > > > >> * > > > >> **Note:* To run the regression tests, particularly the pg_upgrade > > > >> tests successfully, I had to add a helper function, ddl_filter (in > > > >> database.sql), which removes locale and collation-related > information > > > >> from the pg_get_database_ddl output. > > > >> > > > > I think we should check the connection permissions here. Otherwise: > > > > > > > > postgres=3D> SELECT pg_database_size('testdb'); > > > > ERROR: permission denied for database testdb > > > > postgres=3D> SELECT pg_get_database_ddl('testdb'); > > > > > > > > pg_get_database_ddl > > > > > > > > -------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------- > > > > CREATE DATABASE testdb WITH OWNER =3D quanzl ENCODING =3D "UTF8" > > > > LC_COLLATE =3D "zh_CN.UTF-8" LC_CTYPE =3D "zh_CN.UTF-8" LOCALE_PROV= IDER =3D > > > > 'libc' TABLESPACE =3D pg_default ALLOW_CONNECTIONS =3D true CONNECT= ION > LIMIT > > > > =3D -1; > > > > (1 row) > > > > > > > > Users without connection permissions should not generate DDL. > > > > > > > > > > The "dbOwner" is defined as a null pointer. > > > char *dbOwner =3D NULL; > > > > > > Later, there might be a risk of it not being assigned a value. > > > if (OidIsValid(dbForm->datdba)) > > > dbOwner =3D GetUserNameFromId(dbForm->datdba, false); > > > > > > Although there is no problem in normal circumstances here. Many parts > of > > > the existing code have not been checked either. Since this possibilit= y > > > exists, it should be checked before using it. Just like the function > > > roles_is_member_of (acl.c). > > > > > > if (dbOwner) > > > get_formatted_string(&buf, prettyFlags, 1, "OWNER =3D %s", > > > quote_identifier(dbOwner)); > > > > > > > Fixed the given review comment. I've attached the v2 patch ready for > > review. > > > > Thanks for updating the patch, some comments on v2. > > 1. > Should we merge the functions pg_get_database_ddl(oid, [boolean]) and > pg_get_database_ddl(name, [boolean]) in documentation, following the > precedent set by pg_database_size in func-admin.sgml. > > 2. > + * noOfTabChars - indent with specified no of tabs. > > How about using 'indent with specified number of tab characters'? > And for variable noOfTabChars, I'd like use nTabs or nTabChars. > > 3. > +/* > + * pg_get_database_ddl_oid > + * > + * Generate a CREATE DATABASE statement for the specified database oid. > + * > + * dbName - Name of the database for which to generate the DDL. > + * pretty - If true, format the DDL with indentation and line breaks. > + */ > > A copy-paste error resulted in an incorrect comments (dbName). > > All the review comments have been addressed in v3 patch. > -- > Best regards, > Japin Li > ChengDu WenWu Information Technology Co., LTD. > --000000000000309b9e0643cb3fe3 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


On Fri, Nov 14, 2025= at 11:19=E2=80=AFAM Japin Li <japinli@hotmail.com> wrote:
On Thu, Nov 13, 2025 at 02:02:30PM +0530= , Akshay Joshi wrote:
> On Thu, Nov 13, 2025 at 10:18=E2=80=AFAM Quan Zongliang <quanzongliang@yeah.net>
> wrote:
>
> >
> >
> > On 11/13/25 12:17 PM, Quan Zongliang wrote:
> > >
> > >
> > > On 11/12/25 8:04 PM, Akshay Joshi wrote:
> > >> Hi Hackers,
> > >>
> > >> I=E2=80=99m submitting a patch as part of the broader Re= tail DDL Functions
> > >> project described by Andrew Dunstan https://www.postgr= esql.org/
> > >> message- id/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.= net
> > >> <https:// www.pos= tgresql.org/message-id/945db7c5-be75-45bf-b55b-
> > >> cb1e56f2e3e9%40dunslane.net>
> > >>
> > >> This patch adds a new system function
> > >> pg_get_database_ddl(database_name/ database_oid, pretty)= , which
> > >> reconstructs the CREATE DATABASE statement for a given d= atabase name
> > >> or database oid. When the pretty flag is set to true, th= e function
> > >> returns a neatly formatted, multi-line DDL statement ins= tead of a
> > >> single-line statement.
> > >>
> > >> *Usage examples:*
> > >>
> > >> 1) SELECT pg_get_database_ddl('test_get_database_ddl= _builtin');=C2=A0 --
> > >> *non-pretty formatted DDL*
> > >> pg_get_database_ddl
> > >>
> > -----------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------------
> > >>=C2=A0 =C2=A0 CREATE DATABASE test_get_database_ddl_built= in WITH OWNER =3D
> > >> regress_ddl_database ENCODING =3D "UTF8" LC_CO= LLATE =3D "C" LC_CTYPE =3D "C"
> > >> BUILTIN_LOCALE =3D "C.UTF-8" COLLATION_VERSION= =3D "1" LOCALE_PROVIDER =3D
> > >> 'builtin' TABLESPACE =3D pg_default ALLOW_CONNEC= TIONS =3D true CONNECTION
> > >> LIMIT =3D -1;
> > >>
> > >>
> > >> 2) SELECT pg_get_database_ddl('test_get_database_ddl= _builtin', true);
> > >> -- *pretty formatted DDL*
> > >>
> > >> CREATE DATABASE test_get_database_ddl_builtin
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0WITH
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0OWNER =3D regres= s_ddl_database
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ENCODING =3D &qu= ot;UTF8"
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0LC_COLLATE =3D &= quot;C"
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0LC_CTYPE =3D &qu= ot;C"
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0BUILTIN_LOCALE = =3D "C.UTF-8"
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0COLLATION_VERSIO= N =3D "1"
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0LOCALE_PROVIDER = =3D 'builtin'
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0TABLESPACE =3D p= g_default
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0ALLOW_CONNECTION= S =3D true
> > >>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0CONNECTION LIMIT= =3D -1;
> > >>
> > >> 3) SELECT pg_get_database_ddl(16835);=C2=A0 =C2=A0 =C2= =A0 -- *non-pretty formatted
> > >> DDL for OID*
> > >> 4) SELECT pg_get_database_ddl(16835, true);=C2=A0 -- *pr= etty formatted DDL
> > >> for OID*
> > >>
> > >> The patch includes documentation, in-code comments, and = regression
> > >> tests, all of which pass successfully.
> > >> *
> > >> **Note:* To run the regression tests, particularly the p= g_upgrade
> > >> tests successfully, I had to add a helper function, ddl_= filter (in
> > >> database.sql), which removes locale and collation-relate= d information
> > >> from the pg_get_database_ddl output.
> > >>
> > > I think we should check the connection permissions here. Oth= erwise:
> > >
> > > postgres=3D> SELECT pg_database_size('testdb'); > > > ERROR:=C2=A0 permission denied for database testdb
> > > postgres=3D> SELECT pg_get_database_ddl('testdb')= ;
> > >
> > >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 pg_get_database_ddl
> > >
> > -----------------------------------------------------------------= ---------------------------------------------------------------------------= -----------------------------------------------------------------------
> > >=C2=A0 =C2=A0CREATE DATABASE testdb WITH OWNER =3D quanzl ENC= ODING =3D "UTF8"
> > > LC_COLLATE =3D "zh_CN.UTF-8" LC_CTYPE =3D "zh= _CN.UTF-8" LOCALE_PROVIDER =3D
> > > 'libc' TABLESPACE =3D pg_default ALLOW_CONNECTIONS = =3D true CONNECTION LIMIT
> > > =3D -1;
> > > (1 row)
> > >
> > > Users without connection permissions should not generate DDL= .
> > >
> >
> > The "dbOwner" is defined as a null pointer.
> > char=C2=A0 =C2=A0 =C2=A0 =C2=A0*dbOwner =3D NULL;
> >
> > Later, there might be a risk of it not being assigned a value. > >=C2=A0 =C2=A0 =C2=A0if (OidIsValid(dbForm->datdba))
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 dbOwner =3D GetUserNameFromId(dbForm-&= gt;datdba, false);
> >
> > Although there is no problem in normal circumstances here. Many p= arts of
> > the existing code have not been checked either. Since this possib= ility
> > exists, it should be checked before using it. Just like the funct= ion
> > roles_is_member_of (acl.c).
> >
> > if (dbOwner)
> >=C2=A0 =C2=A0 get_formatted_string(&buf, prettyFlags, 1, "= ;OWNER =3D %s",
> >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 quote_identifier(dbOwner));
> >
>
>=C2=A0 Fixed the given review comment. I've attached the v2 patch r= eady for
> review.
>

Thanks for updating the patch, some comments on v2.

1.
Should we merge the functions pg_get_database_ddl(oid, [boolean]) and
pg_get_database_ddl(name, [boolean]) in documentation, following the
precedent set by pg_database_size in func-admin.sgml.

2.
+ * noOfTabChars - indent with specified no of tabs.

How about using 'indent with specified number of tab characters'? And for variable noOfTabChars, I'd like use nTabs or nTabChars.

3.
+/*
+ * pg_get_database_ddl_oid
+ *
+ * Generate a CREATE DATABASE statement for the specified database oid. + *
+ * dbName - Name of the database for which to generate the DDL.
+ * pretty - If true, format the DDL with indentation and line breaks.
+ */

A copy-paste error resulted in an incorrect comments (dbName).


=C2=A0All the review comments have been addressed= in v3 patch.
--
Best regards,
Japin Li
ChengDu WenWu Information Technology Co., LTD.
--000000000000309b9e0643cb3fe3--