Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3864.600.51.1.1\))
Subject: Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE
 statements
From: Chao Li <li.evan.chao@gmail.com>
In-Reply-To: 
 <CANxoLDejn2JFXF7kVMtcznW6XyessrJ7WToSaQau2GxBdAGSUg@mail.gmail.com>
Date: Mon, 22 Jun 2026 14:54:35 +0800
Cc: Zsolt Parragi <zsolt.parragi@percona.com>,
 pgsql-hackers@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <983C7B03-D9C3-4F15-8913-7616EC5E6414@gmail.com>
References: 
 <CANxoLDfjQnhM=E6JSyYo9s9OdjqoN8s_3wE5yL=kaDu_X8j-dA@mail.gmail.com>
 <CANxoLDcxmXNHNyPt5v+LaBJVJmyt=j95D7TDE9SepoM7y15t-w@mail.gmail.com>
 <CAN4CZFNqf41TCg1ENccq6DAYQpArCbnKUSidHnRKaVMWGj5pBA@mail.gmail.com>
 <CANxoLDfL1AxL=k9SsRNReKQ-sJUSy6RvCqeog5HWckFxff=0Pg@mail.gmail.com>
 <CAN4CZFP5-fAGwxMCyKGePgp3N8NCpoFXkruO=5xjTC=gPwqYxw@mail.gmail.com>
 <CANxoLDc6UFMsyzbXKF8SpMoXdoKE=-yRPqtvyBMKKJ2zOUKX4A@mail.gmail.com>
 <CAN4CZFMhnZyQsdLC5iZNego77JHJyP6J_4CpWu=99vKHCd-vzg@mail.gmail.com>
 <CANxoLDco7z7Fj7_pj_3YQLvjq8ZH2Ai4c3GcOamed01fZhJ77Q@mail.gmail.com>
 <CAB-JLwY=RYPJhdFid0YDqk1L1OQPuHFz9J2tOeeVCFLjov+Ktg@mail.gmail.com>
 <CANxoLDdRts-F+QxL+wtM8529Z3+TUJku8PKy3WoxcWBs7zQJ-w@mail.gmail.com>
 <CAB-JLwbyzF+JRPCoLCf-H6dQ_1+-O_wBZcESRdC6_H2z+-d-gg@mail.gmail.com>
 <CANxoLDcWwoWOxvmV_uBBUsbGf+_AZzBV1GkcjoX+omNpcrCNMQ@mail.gmail.com>
 <CAN4CZFM99cGopPJXD_t05+Y9CnocOOTipfXCzoAfBUFSC-i4+A@mail.gmail.com>
 <CANxoLDejn2JFXF7kVMtcznW6XyessrJ7WToSaQau2GxBdAGSUg@mail.gmail.com>
To: Akshay Joshi <akshay.joshi@enterprisedb.com>
Archived-At: 
 <https://www.postgresql.org/message-id/983C7B03-D9C3-4F15-8913-7616EC5E6414%40gmail.com>
Precedence: bulk


> On Jun 22, 2026, at 14:26, Akshay Joshi =
<akshay.joshi@enterprisedb.com> wrote:
>=20
> Thanks for the review; you're right, `includes_foreign_keys=3Dfalse` =
on its own is a half-measure. Re-running with the default to add FKs =
back collides with the existing CREATE TABLE, UNIQUE indexes, etc.
>=20
> I've added an only_foreign_keys option (boolean, default false) as the =
natural complement of includes_foreign_keys=3Dfalse. When set to true, =
the function emits only the ALTER TABLE ... ADD CONSTRAINT ... FOREIGN =
KEY statements and suppresses everything else (CREATE TABLE, owner, =
indexes, non-FK constraints, rules, statistics, replica identity, RLS =
toggles). Partition-child recursion still runs so child FKs are reached =
too. Combining `only_foreign_keys=3Dtrue` with =
`includes_foreign_keys=3Dfalse` is rejected upfront since it would =
produce no output.
>=20
> The documentation paragraph for `includes_foreign_keys` now directs =
users to `only_foreign_keys` as the intended second pass. Regression =
coverage adds three cases: the FK-only emission for your cons example, =
the zero-row result for a table without FKs, and the error path.=20
>=20
> The v8 patch is ready for review.
>=20
> On Sat, Jun 20, 2026 at 1:15=E2=80=AFAM Zsolt Parragi =
<zsolt.parragi@percona.com> wrote:
> The previous features all look good to me, I only have one question
> for the new flag.
>=20
> > Calling
> > pg_get_table_ddl(t, 'includes_foreign_keys', 'false') now emits =
everything
> > except FOREIGN KEY constraints. This covers the multi-tenant clone
> > workflow: create tables first without cross-table references, then =
re-run
> > with the default to add the constraints once all targets exist.
>=20
> I think this feature needs a bit more documentation, an
> "only_foreign_keys" flag, or both.
>=20
> CREATE TABLE refd (id int PRIMARY KEY);
> CREATE TABLE cons (a int CHECK(a>0), b int UNIQUE, c int REFERENCES =
refd(id));
>=20
> -- pass 1: running without foreign keys
> SELECT * FROM =
pg_get_table_ddl('cons','includes_foreign_keys','false');
> -- execute everything
>=20
> -- loading data
>=20
> -- pass 2: running with everything
> SELECT * FROM pg_get_table_ddl('cons','includes_foreign_keys','true');
> -- ERROR: relation "cons" already exists (and the unique constraint
> also collides)
>=20
> I could do a "grep FOREIGN KEY" before executing (unless it's a tricky
> schema where that phrase appears elsewhere), or since psql continues
> on error, it will simply work if I accept a significant error noise,
> but then the documentation should be clear about this limitation.
> Following the documented approach and getting a bunch of unexpected
> errors could be confusing for users.
>=20
>=20
> <v8-0001-Add-pg_get_table_ddl-to-reconstruct-CREATE-TABLE.patch>

I have a comment, or maybe a question:
```
+{ oid =3D> '8215', descr =3D> 'get DDL to recreate a table',
+  proname =3D> 'pg_get_table_ddl', prorows =3D> '50', provariadic =3D> =
'text',
+  proisstrict =3D> 'f', proretset =3D> 't', provolatile =3D> 's', =
proparallel =3D> 'r',
+  pronargdefaults =3D> '1', prorettype =3D> 'text',
+  proargtypes =3D> 'regclass text', proallargtypes =3D> =
'{regclass,text}',
+  proargmodes =3D> '{i,v}', proargdefaults =3D> '{NULL}',
+  prosrc =3D> 'pg_get_table_ddl' },
```

Since provariadic is text, I wonder if proallargtypes should be =
{regclass,_text}, with _text meaning an array of text.

I=E2=80=99m asking because I have had this suspicion for some time. I =
saw a few other procs using the same pattern, for example:
```
{ oid =3D> '6501', descr =3D> 'get DDL to recreate a role',
  proname =3D> 'pg_get_role_ddl', prorows =3D> '10', provariadic =3D> =
'text',
  proisstrict =3D> 'f', proretset =3D> 't', provolatile =3D> 's',
  pronargdefaults =3D> '1', prorettype =3D> 'text', proargtypes =3D> =
'regrole text',
  proallargtypes =3D> '{regrole,text}', proargmodes =3D> '{i,v}',
  proargdefaults =3D> '{NULL}', prosrc =3D> 'pg_get_role_ddl' },
```

But for jsonb_delete etc procs, _text is used:
```
{ oid =3D> '3343',
  proname =3D> 'jsonb_delete', provariadic =3D> 'text', prorettype =3D> =
'jsonb',
  proargtypes =3D> 'jsonb _text', proallargtypes =3D> '{jsonb,_text}',
  proargmodes =3D> '{i,v}', proargnames =3D> '{from_json,path_elems}',
  prosrc =3D> 'jsonb_delete_array' },
```

So I wonder whether =E2=80=9Ctext=E2=80=9D rather than =E2=80=9C_text" =
is intentionally used in proallargtypes, or if this was just never =
noticed.

Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/