Re: Add pg_get_publication_ddl function

Hi all,

On Mon, May 25, 2026 at 5:51 PM Jonathan Gonzalez V.
<jonathan(dot)abdiel(at)gmail(dot)com> wrote:
>
> Hello all!!
>
> I'm attaching the v2 of this patch! There's a lot of changes but I'll
> try to mention the biggest ones:
>
> * Adopted the new structure following the previous committed patches of
> tablespaces and databases.
>
> * Added the options for pretty and owner, also added the statement that
> will alter the publication to have the owner. I didn't know if it was
> required since the CREATE PUBLICATION doesn't have a way to add the
> OWNER, but since all the other functions were doing the same, I think
> it's the way to do it.
>
> * Some improvements on managing the string splits, there's really
> useful functions for bitmapsets and lists so I use them to decide if we
> were in the first element or not.
>
> * I decided to use the cache instead of opening the relation with a
> shared lock, I hope it was the right decision since it didn't make
> sense to me to have a lock for a DDL.
>
> * I took all the comments and check the grammar
>
> * Even more tests were added since many corner cases were not detected
> in the first version, the regress tests helped a lot on finding corner
> cases
>
> * Added the EXCEPT clause that was added after the first version.
>
> Thank you for your reviews!
>

I reviewed and tested v2 of the pg_get_publication_ddl() patch on the
current HEAD. The patch applied cleanly and built successfully without
any issues. I tested the functionality with explicit table
publications, FOR ALL TABLES publications, FOR TABLES IN SCHEMA
publications, schema-qualified relations, mixed-case identifiers, and
both the text-based and OID-based function variants. During testing,
the generated DDL looked correct in all the scenarios I tried.
Schema-qualified relation names were generated correctly, quoted
identifiers were handled properly, and both the text-based and
OID-based variants produced the same output. I did not observe any
crashes, assertion failures, or incorrect relation resolution while
testing. One thing I noticed is that the function currently returns
SETOF text, producing multiple DDL statements such as CREATE
PUBLICATION followed by ALTER PUBLICATION ... OWNER TO .... I wanted
to check whether this is the intended long-term API design, as most
existing pg_get_* functions return a single text value. I also noticed
that publication options are emitted as quoted values, for example:

publish='insert, update'
publish_generated_columns='none'
publish_via_partition_root='false'

Would it make sense to emit enum-like and boolean values in their
native SQL form instead, such as:

publish = 'insert, update'
publish_generated_columns = none
publish_via_partition_root = false

Apart from these questions, the patch worked well in my testing and
the generated DDL appeared correct for all the publication types I
tested.
Looking forward to more feedback.

Regards,
Solai V