Content-Type: multipart/alternative;
 boundary="------------0d4iPodcNNqqJpUn4Nvrgu9m"
Message-ID: <a1c4dd3e-6694-4b7b-8432-f3b584735ff3@dunslane.net>
Date: Sun, 5 Apr 2026 11:06:09 -0400
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_get__*_ddl consolidation
From: Andrew Dunstan <andrew@dunslane.net>
To: "David G. Johnston" <david.g.johnston@gmail.com>,
 Japin Li <japinli@hotmail.com>
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>
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>
 <SY7PR01MB10921A6E1E08A48F3426FE529B651A@SY7PR01MB10921.ausprd01.prod.outlook.com>
 <CAKFQuwYcppypeGBwa7ZbDAfoUXSv+kLhJuAXsdwBmKrvy8wDFw@mail.gmail.com>
 <a533fb1b-0f2c-4fc9-ab3b-6fb5ddac1800@dunslane.net>
Content-Language: en-US
Autocrypt: addr=andrew@dunslane.net; keydata=
 xsBNBE7KWFkBCAClridxur2AIc7eW2AR7izbfp3EnNefie2HbLF0izW5Ik5UjX2HBXBx4syI
 gY6b0ugohXrr274+baoAlvSbq6cAoQuEVrk5IZFzt20b1Xkx65FwGSEj526yiKLocqkJceSq
 Xr9xcA5SGY+FZv441chh5SU92v4q6z+6LPpoHOh97ptAVXZYNTtU0LevyvD5lja0TzbvJm6C
 eFXitJfnm1pLEr0DGJCR/iUOl/N62Kh4855zZC7NHIjQHPOvV5Stz/l5ilDhvGVk+xkXFPys
 SjZoUr1rXhYLpiyi5sR0X9FHXT0KnGuz1F5ERO7ZTLSSQ6fJwPj6gOk9K+vvoKvoeql5ABEB
 AAHNJEFuZHJldyBEdW5zdGFuIDxhbmRyZXdAZHVuc2xhbmUubmV0PsLAlwQTAQgAQQIbAwIX
 gAIZAQULCQgHAwUVCgkICwUWAgMBAAIeBRYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNGd
 BQkdEO8nAAoJEJn6f8tZ/DuBq74H/jkTR4Zi3stbw+xC7v2u3QozssK7MYPL2AsVfh7OealS
 h182fiWXpfvmmAB7WUHbhk9GC2RAOnHI/2d2jgKaMLAHsGYOT0YopTVIwRY43fCw/mK67yxc
 wmDcX+zyKfLaivNbf5A7QPLNwda98bEAMSJ8Sn652Uc6cA8t3uKGsVzbRBQOoYzjgvBCfSrE
 9ql3PDNg0l4BfAqabd2f70ZUm9VAMEPrgv/v2xI7M2XiL4g5BVmqLCOwxLM8RMCotCuoweUr
 VO43DeBCIDwLxotMJKvGWDjBzQYlU1NPUAtNcz/gN9ITUe1VUGjyvGj4u1lxBOcQQUw7l1+T
 5moZ4iZxXzvOwE0ETspYWQEIANGc4zQULOxhbqO2dyD51YhqCNRmm9oKWaqf+wmW4tpDe/VV
 cxAnNizd4LWCHfzpb5cHAtGkOPePMfzWVf6nvdF7d3eglbtf59+zG7O7llV0xSSoFiieQBsr
 GvqDInXYX/4mRRXMtyhM353/tixC9RWLs1oofyYmCPPXXY7h9R7en3B8BoVrRFcdzlIY/NFN
 hFGW/9dkEiGjgna2Rk6e15kln4ZvFBWUg23p93w/pqXcxY6+k/8TEk+C4R+M6w7o2PLGOjdZ
 +kPiUcw5H85zf/yZJwQXzisXaNduwWB6Vads9YC9dj6kPR1c4VGRqAaYL++LAEOqrlvm2Tvq
 QqZRtnEAEQEAAcLAfAQYAQgAJgIbDBYhBOQ+WEYd/Hy/RGkVpZn6f8tZ/DuBBQJoGNI2BQkd
 EODdAAoJEJn6f8tZ/DuBfw0IAKTsfD40teP/pp+bsLLMSxPXUYrrprTj7WFB5v61p6dkpSr/
 qXmMlyahdxQFaPmfVgVirB1Vk/kHiWNnnGjfUV9nB2Zg9LI0Xb9/ts3LsUiRWXzG3tkMY6XL
 vsVOxW4XFRND9l2q+WW93aZ1DZl+fqWfYgMvsusFRhmGFOKTRfKPta2Pkv+AhA24N4+PrR5p
 bU4k2MO8PAGiK8eaYKGFG1bHKuAvoDoF7WXJ3FHxuWqLnKEt4dfOLm5pAe3zq1Lt6q8azT9i
 QWGpSAK5vQUWQHBHpiDjdPeqKZ6HiAXIIKfSmb+jrvXBqoP+D6/K7rUjG2aXiRtTIAXms9sm
 VRu7cmw=
In-Reply-To: <a533fb1b-0f2c-4fc9-ab3b-6fb5ddac1800@dunslane.net>
Archived-At: 
 <https://www.postgresql.org/message-id/a1c4dd3e-6694-4b7b-8432-f3b584735ff3%40dunslane.net>
Precedence: bulk

This is a multi-part message in MIME format.
--------------0d4iPodcNNqqJpUn4Nvrgu9m
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit


On 2026-04-02 Th 12:27 PM, Andrew Dunstan wrote:
>
>
> On 2026-04-02 Th 9:35 AM, David G. Johnston wrote:
>> On Thursday, April 2, 2026, Japin Li <japinli@hotmail.com> wrote:
>>
>>
>>     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.
>>
>>
>> There is no way or use in constructing the original template clause, 
>> though I agree it’s worth a comment.  At the end of the day the 
>> catalog data that was found in the db01 database already exists in 
>> the db02 database when executing these DLL reconstruction functions 
>> against the existing db02 database.  Taking nothing from the template 
>> is the correct behavior - hence template0.
>>
>>
>
>
> OK, here's a v4.
>
>
>

Pushed. I have moved the remaining get_*_ddl items to PG20-1


cheers


andrew

--
Andrew Dunstan
EDB:https://www.enterprisedb.com

--------------0d4iPodcNNqqJpUn4Nvrgu9m
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <p><br>
    </p>
    <div class="moz-cite-prefix">On 2026-04-02 Th 12:27 PM, Andrew
      Dunstan wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:a533fb1b-0f2c-4fc9-ab3b-6fb5ddac1800@dunslane.net">
      <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
      <p><br>
      </p>
      <div class="moz-cite-prefix">On 2026-04-02 Th 9:35 AM, David G.
        Johnston wrote:<br>
      </div>
      <blockquote type="cite"
cite="mid:CAKFQuwYcppypeGBwa7ZbDAfoUXSv+kLhJuAXsdwBmKrvy8wDFw@mail.gmail.com">
        <meta http-equiv="content-type"
          content="text/html; charset=UTF-8">
        On Thursday, April 2, 2026, Japin Li &lt;<a
          href="mailto:japinli@hotmail.com" moz-do-not-send="true"
          class="moz-txt-link-freetext">japinli@hotmail.com</a>&gt;
        wrote:<br>
        <blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><br>
          v3-0004<br>
          ========<br>
          <br>
          1.<br>
          +       append_ddl_option(&amp;buf, pretty, 4, "WITH TEMPLATE
          = template0");<br>
          <br>
          I'm curious why WITH TEMPLATE = template0 is hardcoded. For
          example:<br>
          <br>
            [local]:1374846 postgres=# create database db01 IS_TEMPLATE
          true;<br>
            CREATE DATABASE<br>
            [local]:1374846 postgres=# create database db02 template
          db01;<br>
            CREATE DATABASE<br>
            [local]:1374846 postgres=# select
          pg_get_database_ddl('db02');<br>
                                                         
           pg_get_database_ddl<br>
            ------------------------------<wbr>------------------------------<wbr>------------------------------<wbr>-----------------------<br>
             CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING =
          'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';<br>
             ALTER DATABASE db02 OWNER TO japin;<br>
            (2 rows)<br>
          <br>
          Is this working as expected?<br>
          <br>
          It seems there's no way to reconstruct the WITH TEMPLATE
          clause, right?<br>
          A comment here would help.</blockquote>
        <div><br>
        </div>
        <div>There is no way or use in constructing the original
          template clause, though I agree it’s worth a comment.  At the
          end of the day the catalog data that was found in the db01
          database already exists in the db02 database when executing
          these DLL reconstruction functions against the existing db02
          database.  Taking nothing from the template is the correct
          behavior - hence template0.</div>
        <div><br>
        </div>
        <div><br>
        </div>
      </blockquote>
      <p><br>
      </p>
      <p><br>
      </p>
      <p>OK, here's a v4.</p>
      <p><br>
      </p>
      <br>
    </blockquote>
    <p><br>
    </p>
    <p>Pushed. I have moved the remaining get_*_ddl items to PG20-1</p>
    <p><br>
    </p>
    <p>cheers</p>
    <p><br>
    </p>
    <p>andrew<br>
    </p>
    <pre class="moz-signature"
    signature-switch-id="d0437855-2267-4610-80b3-83167ec45b0b" cols="72">--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
  </body>
</html>

--------------0d4iPodcNNqqJpUn4Nvrgu9m--