Re: [PATCH] Add pg_get_table_ddl() to reconstruct CREATE TABLE statements

Thanks for the feedback, Zsolt! I've fixed the issues you pointed out.
The *v22* patch is ready for review and testing.

On Thu, Jul 16, 2026 at 2:39 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com>
wrote:

> I can confirm that most fixes work correctly, but there are two
> remaining issues / regressions caused by the fixes:
>
> 1.
>
> CREATE TABLE pidx (a int, b int) PARTITION BY RANGE (a);
> CREATE TABLE pidx_1 PARTITION OF pidx FOR VALUES FROM (0) TO (10);
> CREATE TABLE pidx_2 PARTITION OF pidx FOR VALUES FROM (10) TO (20);
> CREATE INDEX ON pidx (b);
>
> is emitted as:
>
> CREATE TABLE public.pidx (a integer, b integer) PARTITION BY RANGE (a);
> CREATE INDEX pidx_b_idx ON ONLY public.pidx USING btree (b);
> CREATE TABLE public.pidx_1 PARTITION OF public.pidx FOR VALUES FROM (0) TO
> (10);
> CREATE INDEX pidx_1_b_idx ON public.pidx_1 USING btree (b); <-- fails
> here
> -- ERROR: relation "pidx_1_b_idx" already exists
> ALTER INDEX public.pidx_b_idx ATTACH PARTITION pidx_1_b_idx;
> -- ...
>
> 2.
>
> CREATE TABLE idt (a int GENERATED BY DEFAULT AS IDENTITY);
> ALTER SEQUENCE idt_a_seq AS smallint;
>
> is emitted as:
>
> -- ERROR: conflicting or redundant options
> -- LINE 1: ....idt (a integer GENERATED BY DEFAULT AS IDENTITY (AS
> smallin...
> CREATE TABLE public.idt (a integer GENERATED BY DEFAULT AS IDENTITY
> (AS smallint MAXVALUE 32767) NOT NULL);
>
>
> > > Interestingly pg_dump also skips this, that looks like a pg_dump bug?
> >
> > The behavior was indeed inconsistent with what emit_indexes() already
> > did for non-constraint indexes. emit_local_constraints() now emits ALTER
> > INDEX ... ALTER COLUMN n SET STATISTICS for PK/UNIQUE/EXCLUSION
> constraint
> > backing indexes.
>
> I submitted a matching patch for pg_dump:
>
> https://www.postgresql.org/message-id/CAN4CZFMd9riOuV5LkM%2BuV%3DuF-HYp49C8Fh22xWyMxptGLBCZ3A%40mail.gmail.com
>