Feedback-ID: i0c21471d:Fastmail
MIME-Version: 1.0
Date: Fri, 12 Dec 2025 12:19:38 -0300
From: "Euler Taveira" <euler@eulerto.com>
To: "Akshay Joshi" <akshay.joshi@enterprisedb.com>
Cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@kurilemu.de>,
 "Chao Li" <li.evan.chao@gmail.com>, japin <japinli@hotmail.com>,
 "Quan Zongliang" <quanzongliang@yeah.net>,
 pgsql-hackers <pgsql-hackers@postgresql.org>
Message-Id: <4c695e76-5ab7-449f-8060-76518dd41468@app.fastmail.com>
In-Reply-To: 
 <CANxoLDeAv5M-HcVnwN6q3RNaBeXRfkR7_3LAvZ=pfLqc8=N8vw@mail.gmail.com>
References: 
 <CANxoLDdPqyTLyYhBnekFYkaSn_8DBdtf32cH=MLNJc=+BQP=MQ@mail.gmail.com>
 <202511191045.tckydhpoxumx@alvherre.pgsql>
 <CANxoLDeEzSvC8hy7oF=qZZGVD---MDwxesdoQnAQMuJQGOSaJw@mail.gmail.com>
 <4e60bcae-8222-4e1f-8e5b-d73b59c93304@app.fastmail.com>
 <CANxoLDeAv5M-HcVnwN6q3RNaBeXRfkR7_3LAvZ=pfLqc8=N8vw@mail.gmail.com>
Subject: Re: [PATCH] Add pg_get_database_ddl() function to reconstruct CREATE
 DATABASE
 statement
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/4c695e76-5ab7-449f-8060-76518dd41468%40app.fastmail.com>
Precedence: bulk

On Fri, Dec 12, 2025, at 7:52 AM, Akshay Joshi wrote:
> On Thu, Dec 11, 2025 at 7:29=E2=80=AFPM Euler Taveira <euler@eulerto.c=
om> wrote:
>>
> Is there any way to obtain the default values directly from the source
> code itself, or do I need to refer to the documentation? If we rely on
> the documentation and compare against that, then in the future, if the
> default values change, we would also need to update our logic
> accordingly.
>

No, you need to check the documentation. If you are changing the default=
 value,
you are breaking compatibility; that rarely happens. If we are really co=
ncern
about this fact, you can add a test case that creates the object without
properties (all default values) and another with all default properties =
and
then compare the output.

> Constantly having to check the documentation for default values may
> feel annoying to some users. Some users run queries with parameters
> such as encoding, connection limit, and locale using their default
> values. When they call the pg_get_database_ddl function, it
> reconstructs the short command based on those defaults.
>

Encoding and locale, ok but I doubt about connection limit.

postgres=3D# SELECT current_user;
 current_user
--------------
 euler
(1 row)

postgres=3D# CREATE DATABASE foo;
CREATE DATABASE
postgres=3D# CREATE DATABASE bar OWNER euler;
CREATE DATABASE

When you are learning a new command, you generally don't set the default=
 value
for a property just to be correct. I'm not saying this function shouldn't
include OWNER. I'm just suggesting it to be optional.  See some arguments
below.

>> * OWNER. There is no guarantee that the owner exists in the cluster y=
ou will
>>   use this output. That's something that pg_dumpall treats separately=
 (see
>>   above). Does it mean we should include the owner? No. We can make i=
t an
>>   option.
>>
> If I understand correctly, the owner should be an option provided by
> the caller of the function, and we reconstruct the Database DDL using
> that specified owner. Is that right?
> If so, then in my humble opinion, this is not truly a reconstruction
> of the existing database object.
>

No. My idea is to have something like the pg_dump --no-owner option. Thi=
s is
important if you are transporting the objects from one cluster to anothe=
r one.
Owner might be different. That's why I'm suggesting it should be optiona=
l. It
means flexibility. See pg_dump output format that always apply the OWNER=
 as a
separate ALTER command.

>> * options. Since I mentioned options for some properties (owner, stra=
tegy,
>>   template), these properties can be accommodated as a VARIADIC argum=
ent. The
>>   function signature can be something like
>>
>> pg_get_database_ddl(oid, VARIADIC options text[])
>>
>> I would include the pretty print into options too.
>>
> Same comment as the one I gave for the Owner, if you are referring to
> these as options to the function.
>

Let me elaborate a bit. As I suggested you can control the output with o=
ptions.
Why? Flexibility.

Why am I suggesting such a general purpose implementation? See some of t=
he use
cases.

1. object DDL. Check DDL to recreate the object. It is not the exact DDL=
 that
the user informed but it produces the same result.
2. clone tool. Clone the objects to recreate the environment for another
customer. These objects can be created in the same cluster or in another=
 one.
(Of course, global objects don't apply for the same cluster.)
3. dump tool. Dump the commands to recreate the existing objects.
4. diff tool. There are tools like pgquarrel [1] that queries the catalo=
g and
compare the results to create commands to turn the target database into =
the
source database. The general purpose functions can be used if the object
doesn't exist in the target database. (Of course, it doesn't apply for g=
lobal
objects but again it is a good UI to have all of these pg_get_OBJECT_ddl
functions using the same approach.)
5. logical replication. These pg_get_OBJECT_ddl functions can be good
candidates to be used in the initial schema replication and even in the =
DDL
replication (if the object doesn't exist in the target database).

The "options" parameter is to get the DDL command to serve any of these =
use
cases. There are some properties in a certain object that you *don't* wa=
nt for
whatever reason. See some --no-OBJECT options in pg_dump. Let's say you =
don't
want the TABLESPACE or the table access method while getting the CREATE =
TABLE
DDL because it is different in the other database.

> I received a review comment suggesting the use of tabs. I also looked
> up PostgreSQL best practices on google, which recommend using tabs for
> indentation and spaces for alignment. I=E2=80=99m open to updating my =
code
> accordingly.
>

I didn't check all of the possible output but the majority uses space in=
stead
of tabs. Check psql. If you check the git history (git log --grep=3Dtabs=
), you
will notice that tabs are removed from source code.

>> * permission. I don't think you need to check for permissions inside =
the
>>   function. I wouldn't want a different behavior than pg_dump(all). Y=
ou can
>>   always adjust it in system_functions.sql.
>>
> We=E2=80=99ve already had extensive discussions on this topic in the s=
ame
> email thread, and ultimately we decided to add the permission check.
>

That's fair. Again, I expect that all of these pg_get_OBJECT_ddl functio=
ns use
the same approach. We can always relax this restriction in the future.


--=20
Euler Taveira
EDB   https://www.enterprisedb.com/