pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedAdd pg_get_publication_ddl function
22+ messages / 11 participants
[nested] [flat]
* Add pg_get_publication_ddl function
@ 2026-01-11 11:20 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
0 siblings, 4 replies; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-01-11 11:20 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Hi all!
Following the set of pg_get_{object}_ddl functions described by Andrew
Dustan[1] I'm attaching another patch that I created for the
PUBLICATION object, I had to left some stuff out and added others based
on other patches feedback for the same type of functions:
* Pretty print: There's a few patched that implemented the pretty print
using spaces or tabs, by default or not, etc. I just decided to keep
this out of this patch, the idea is to follow any decision that can
be made for all the patches at some point, and mostly probably have
a common set of functions to use in all these functions since there
will be a lot of them, and rewriting every time the same and
reviewing the same possible code may take some time and I think it's
better to have the base set of functions and later work on stuff like
pretty print.
* Testing: I've added a regress test with the name
`publiction_ddl.sql`, those, it's close to the publication object in
names on the list of files and not inside a big generic file with
many SQL for different objects. Those, we can keep a list of file in
a fixed section inside the file `parallel_schedule`.
The patch add the function, tests and documentation, but on the
documentation side I don't know if it should go anywhere else or if the
current location it's ok, I haven't found any decision on the that
specific subject.
Related to the required privileges, to CREATE or ALTER a PUBLICATION
you require a specific set of privileges, but there's no defined
privilege to "view" a publication, being really strict, this function
could leak information like columns and tables that exist inside the
database without even have permissions to see those tables, the same
will be for sequences in the future. This took me to the question, does
a kind of "view" privilege exists for these objects? Because you can
have access to the database or even some tables but not all of them at
the same time, and this could lead to a leak of data. I'm really new on
PostgreSQL development, but I couldn't find the "view" privilege that
will allow to read a PUBLICATION and check it, but in my opinion not
being allowed to create a publication doesn't mean that you cannot see
the content of it, am I missing a privilege here or if you can't create
you shouldn't be able to "view" a publication?
Related to the default values, there's a couple of things that I think
it should be there just because, either are always defined or to make
clear the value is there since it will affect the object:
* publish: this field contains a list of permissions [2] that are a
list of variables inside a struct with values always being set
* publish_generated_columns: the default is well defined and not adding
the default to the statement will make sense, but since the others
two will be there, for useful and educational purposes, this makes
sense to me.
* publish_via_partition_root: this boolean is always set, so adding it
make sense since it will make explicit to someone debugging a
publication how this will behave on a TRUNCATE situation.
Thank you in advance for the reviews and all the time on this!
[1]
https://www.postgresql.org/message-id/flat/945db7c5-be75-45bf-b55b-cb1e56f2e3e9%40dunslane.net
[2]
https://www.postgresql.org/docs/18/sql-createpublication.html#SQL-CREATEPUBLICATION-PARAMS-WITH
--
Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
EnterpriseDB
Attachments:
[text/x-patch] v1-0001-Introduce-a-new-function-pg_get_publication_ddl.patch (36.6K, ../../70d20c14d1c14f93e4200c1a9327b97d7c18a782.camel@gmail.com/2-v1-0001-Introduce-a-new-function-pg_get_publication_ddl.patch)
download | inline diff:
From b7741d5f02cee5bef210f6975c850b70123f75a4 Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>
Date: Sun, 5 Oct 2025 18:11:56 +0200
Subject: [PATCH v1 1/1] Introduce a new function pg_get_publication_ddl() that
returns the CREATE ddl statement for a giving PUBLICATION.
The functions accepts a publication name or OID indistinctively,
thuse, provides an easy way to call the function as a user or from
inside the Postgres code or any SQL statement.
Comprhensive regression tests are included covering various
possible situations for a given publication.
Signed-off-by: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 45 ++++
src/backend/utils/adt/ruleutils.c | 247 ++++++++++++++++++
src/include/catalog/pg_proc.dat | 8 +
src/test/regress/expected/publication_ddl.out | 211 +++++++++++++++
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/publication_ddl.sql | 121 +++++++++
6 files changed, 636 insertions(+)
create mode 100644 src/test/regress/expected/publication_ddl.out
create mode 100644 src/test/regress/sql/publication_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 175f18315cd..600534ff11f 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3830,4 +3830,49 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
</sect2>
+ <sect2 id="functions-get-object-ddl">
+ <title>Get Object DDL Functions</title>
+
+ <para>
+ The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+ print the DDL statements for various database objects.
+ (This is a decompiled reconstruction, not the original text
+ of the command.)
+ </para>
+
+ <table id="functions-get-object-ddl-table">
+ <title>Get Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_publication_ddl</primary>
+ </indexterm>
+ <function>pg_get_publication_ddl</function> ( <parameter>publication</parameter> <type>text</type> or <type>oid</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Recreate the CREATE statement for a giving PUBLICATION.
+ The result is a complete <command>CREATE PUBLICATION</command> statement.
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ </sect2>
+
+
</sect1>
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 033b625f3fc..b4e06d3f883 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -30,10 +30,12 @@
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
+#include "catalog/pg_namespace_d.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
#include "catalog/pg_proc.h"
+#include "catalog/pg_publication_rel.h"
#include "catalog/pg_statistic_ext.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -546,6 +548,7 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
deparse_context *context,
bool showimplicit,
bool needcomma);
+static Datum pg_do_publication_ddl(Publication *pub);
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
@@ -13743,3 +13746,247 @@ get_range_partbound_string(List *bound_datums)
return buf.data;
}
+
+/*
+ * pg_do_publication_ddl
+ * Common code to get the DDL fro a PUBLICATION
+ * by pg_get_publication_ddl and pg_get_publication_ddl_name
+ */
+Datum
+pg_do_publication_ddl(Publication *pub)
+{
+ StringInfo buf;
+ List *publications;
+ List *pub_schemas;
+ bool first_perm;
+
+ if (pub == NULL)
+ return (Datum) NULL;
+
+ publications = GetPublicationRelations(pub->oid, PUBLICATION_PART_ALL);
+ pub_schemas = GetPublicationSchemas(pub->oid);
+
+ buf = makeStringInfo();
+
+ /*
+ * we add the publication name, this isn't covered by the schema, yet?
+ */
+ appendStringInfo(buf, "CREATE PUBLICATION %s", quote_identifier(pub->name));
+
+ /*
+ * having all tables or all sequence means there's not publications per
+ * table
+ */
+ if (pub->alltables || pub->allsequences)
+ {
+ appendStringInfo(buf, " FOR ");
+
+ if (pub->alltables)
+ appendStringInfo(buf, "ALL TABLES");
+
+ /* The sequence are published only on versions 19+ */
+ if (pub->allsequences)
+ appendStringInfo(buf,
+ "%sALL SEQUENCE",
+ pub->alltables ? ", " : " ");
+ }
+ else if (publications != NIL)
+ {
+
+ ListCell *pub_cell;
+ Relation rel;
+ char *schemaname = NULL;
+ char *tablename;
+ bool first = true;
+
+ appendStringInfo(buf, " FOR TABLE ");
+
+ /*
+ * publication an have relations of tables, in schema or current
+ * schema
+ *
+ */
+ foreach(pub_cell, publications)
+ {
+ HeapTuple pubtuple = NULL;
+ Datum columns;
+ Datum conditions;
+ bool cols_nulls,
+ condition_nulls;
+
+ /* Open the table relation for this publication */
+ rel = relation_open(pub_cell->oid_value, AccessShareLock);
+ tablename = RelationGetRelationName(rel);
+
+ if (RelationGetNamespace(rel) != PG_PUBLIC_NAMESPACE)
+ schemaname = get_namespace_name(RelationGetNamespace(rel));
+
+ appendStringInfo(buf, "%s%s",
+ first ? "" : ", ",
+ quote_qualified_identifier(schemaname, tablename));
+ first = false;
+
+ pubtuple = SearchSysCacheCopy2(PUBLICATIONRELMAP,
+ ObjectIdGetDatum(rel->rd_id),
+ ObjectIdGetDatum(pub->oid));
+ columns = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &cols_nulls);
+
+ conditions = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prqual,
+ &condition_nulls);
+
+ /* If no null, means we have a list of columns to publish */
+ if (!cols_nulls)
+ {
+ ArrayType *arr;
+ int ncolumns;
+ int16 *cols;
+ bool fcol = true;
+
+ arr = DatumGetArrayTypeP(columns);
+ ncolumns = ARR_DIMS(arr)[0];
+ cols = (int16 *) ARR_DATA_PTR(arr);
+
+ appendStringInfoChar(buf, '(');
+ for (int i = 0; i < ncolumns; i++)
+ {
+ appendStringInfo(buf, "%s%s",
+ fcol ? "" : ", ",
+ get_attname(rel->rd_id, cols[i], true));
+ fcol = false;
+ }
+ appendStringInfoChar(buf, ')');
+ }
+
+ /*
+ * If there's a condition goes after the columns but if there's no
+ * column we can have conditions too
+ */
+ if (!condition_nulls)
+ {
+ Node *node;
+ List *context;
+ char *str;
+
+ node = stringToNode(TextDatumGetCString(conditions));
+ context = deparse_context_for(
+ get_relation_name(rel->rd_id),
+ rel->rd_id);
+ str = deparse_expression_pretty(
+ node, context, false, false,
+ GET_PRETTY_FLAGS(false), 0);
+
+ appendStringInfo(buf, " WHERE %s ", str);
+ }
+
+ /* Close the relation */
+ relation_close(rel, AccessShareLock);
+ }
+ }
+
+ /* if we have schemas, for sure this will go right before the WITH */
+ if (pub_schemas != NIL)
+ {
+ ListCell *schema_cell;
+ bool first_schema = true;
+
+ /*
+ * schemas can be preceeded by a list of tables
+ */
+ appendStringInfo(buf, "%s TABLES IN SCHEMA",
+ publications == NIL ? " FOR" : ",");
+
+ /* schemas can be one or a list */
+ foreach(schema_cell, pub_schemas)
+ {
+ appendStringInfo(buf, "%s %s",
+ first_schema ? "" : ",",
+ quote_identifier(get_namespace_name(schema_cell->oid_value)));
+ first_schema = false;
+ }
+ }
+
+ /* always add the WITH options */
+ appendStringInfo(buf, " WITH (");
+
+ /* publish string */
+ appendStringInfo(buf, "publish='");
+
+ /*
+ * we need to know if we're the second permission added to prefix with a
+ * ", " string
+ */
+ first_perm = true;
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * by precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfo(buf, "insert");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
+ }
+
+ appendStringInfo(buf, "', ");
+
+ /* publish_generated_columns string */
+ appendStringInfo(buf, "publish_generated_columns='%s', ",
+ pub->pubgencols_type == PUBLISH_GENCOLS_NONE ? "none" : "stored");
+
+ /* publish_via_partition_root value */
+ appendStringInfo(buf, "publish_via_partition_root='%s')",
+ pub->pubviaroot ? "true" : "false");
+
+ /* the statement should always finish with a ; */
+ appendStringInfoChar(buf, ';');
+
+ PG_RETURN_TEXT_P(string_to_text(buf->data));
+}
+
+/*
+ * pg_get_publication_ddl_name
+ * Get the CREATE statement for PUBLICATION by name
+ */
+Datum
+pg_get_publication_ddl_name(PG_FUNCTION_ARGS)
+{
+ char *pub_name = text_to_cstring(PG_GETARG_TEXT_PP(0));
+ Publication *pub;
+
+ pub = GetPublicationByName(pub_name, false);
+
+ return pg_do_publication_ddl(pub);
+}
+
+/*
+ * pg_get_publication_ddl
+ * Get the CREATE statement for PUBLICATION by OID
+ */
+Datum
+pg_get_publication_ddl(PG_FUNCTION_ARGS)
+{
+ Oid pub_oid = PG_GETARG_OID(0);
+ Publication *pub;
+
+ pub = GetPublication(pub_oid);
+
+ return pg_do_publication_ddl(pub);
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 2ac69bf2df5..7bbb1d2751f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12336,6 +12336,14 @@
proname => 'pg_relation_is_publishable', provolatile => 's',
prorettype => 'bool', proargtypes => 'regclass',
prosrc => 'pg_relation_is_publishable' },
+{ oid => '6122',
+ descr => 'returns the create statement for a publication',
+ proname => 'pg_get_publication_ddl', prorettype => 'text',
+ proargtypes => 'oid', prosrc => 'pg_get_publication_ddl' },
+{ oid => '6123',
+ descr => 'returns the create statement for a publication',
+ proname => 'pg_get_publication_ddl', prorettype => 'text',
+ proargtypes => 'text', prosrc => 'pg_get_publication_ddl_name' },
# rls
{ oid => '3298',
diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regress/expected/publication_ddl.out
new file mode 100644
index 00000000000..9d162c07d53
--- /dev/null
+++ b/src/test/regress/expected/publication_ddl.out
@@ -0,0 +1,211 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+-- supress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create base table to test basic table publication
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+(1 row)
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+(1 row)
+
+-- create publication for one table with one column and a condition
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1 (bar) WHERE (bar = 1);
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE testpub_ddl_tbl1(bar) WHERE (bar = 1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE testpub_ddl_tbl1(bar) WHERE (bar = 1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publication for one table with two columns and a condition
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publication for one table with two columns and a condition with an expresion
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publation for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL TABLES, ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL TABLES, ALL SEQUENCE WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL TABLES, ALL SEQUENCE WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+-- create a publication for a list of tables
+CREATE PUBLICATION testpub_ddl_8 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2;
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_9 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_10 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_11 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_11');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_11 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_11'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_11 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+(1 row)
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+DROP PUBLICATION testpub_ddl_2;
+DROP PUBLICATION testpub_ddl_3;
+DROP PUBLICATION testpub_ddl_4;
+DROP PUBLICATION testpub_ddl_5;
+DROP PUBLICATION testpub_ddl_6;
+DROP PUBLICATION testpub_ddl_7;
+DROP PUBLICATION testpub_ddl_8;
+DROP PUBLICATION testpub_ddl_9;
+DROP PUBLICATION testpub_ddl_10;
+DROP PUBLICATION testpub_ddl_11;
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl;
+DROP SCHEMA pub_schema_test_ddl_2;
+RESET client_min_messages;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 021d57f66bb..ce5351759b4 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -137,6 +137,10 @@ test: event_trigger_login
# this test also uses event triggers, so likewise run it by itself
test: fast_default
+# run the retail DDL tests at the end make sense to not interrupt with other
+# tests in case the object names are the same to other objects previously used.
+test: publication_ddl
+
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sql/publication_ddl.sql
new file mode 100644
index 00000000000..e93bcf70692
--- /dev/null
+++ b/src/test/regress/sql/publication_ddl.sql
@@ -0,0 +1,121 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+
+-- supress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+
+-- create base table to test basic table publication
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+
+-- create publication for one table with one column and a condition
+
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1 (bar) WHERE (bar = 1);
+
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+
+-- create publication for one table with two columns and a condition
+
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+
+-- create publication for one table with two columns and a condition with an expresion
+
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+
+-- create publation for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL TABLES, ALL SEQUENCES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+
+-- create a publication for a list of tables
+CREATE PUBLICATION testpub_ddl_8 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2;
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_9 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_10 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_11 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_11');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_11'));
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+DROP PUBLICATION testpub_ddl_2;
+DROP PUBLICATION testpub_ddl_3;
+DROP PUBLICATION testpub_ddl_4;
+DROP PUBLICATION testpub_ddl_5;
+DROP PUBLICATION testpub_ddl_6;
+DROP PUBLICATION testpub_ddl_7;
+DROP PUBLICATION testpub_ddl_8;
+DROP PUBLICATION testpub_ddl_9;
+DROP PUBLICATION testpub_ddl_10;
+DROP PUBLICATION testpub_ddl_11;
+
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl;
+DROP SCHEMA pub_schema_test_ddl_2;
+RESET client_min_messages;
--
2.51.0
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re:Add pg_get_publication_ddl function
@ 2026-01-11 12:18 =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
3 siblings, 0 replies; 22+ messages in thread
From: zengman @ 2026-01-11 12:18 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; pgsql-hackers <pgsql-hackers@lists.postgresql.org>
Hi,
I haven't tested your patch yet, but I noticed a few points that may need adjustment:
Based on your code logic, the pub parameter passed here can never be NULL ― if the corresponding PUBLICATION does not exist, an error should have already been thrown earlier in the code flow.
Therefore, the following code block can be removed, and the usage of return (Datum) NULL; in this block is also incorrect:
```
if (pub == NULL)
return (Datum) NULL;
```
pubtuple is not being freed ― please use heap_freetuple to release it.
The format string "%sALL SEQUENCE" should be corrected to "%sALL SEQUENCES".
--
Regards,
Man Zeng
www.openhalo.org
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-01-13 02:46 Peter Smith <smithpb2250@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
3 siblings, 1 reply; 22+ messages in thread
From: Peter Smith @ 2026-01-13 02:46 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi Jonathan,
Here are some initial review comments for patch v1-0001.
(I haven't applied or run anything -- this is all just by inspection).
======
1. GENERAL - typos
There are many typos and upper/lower capitalisation mistakes in the
comments / commit message/docs.
I won't list them all, but below is a sample. Consider using a
spelling/grammar checker to discover all of them.
typo /for a giving PUBLICATION./
typo /thuse/
typo /Comprhensive/
typo /CREATE statement for a giving PUBLICATION/
typo /DDL fro a PUBLICATION/
typo /The sequence are/
typo /publication an have relations/
typo /If no null/
typo /at the end make sense to/
typo /create publation/
======
doc/src/sgml/func/func-info.sgml
2.
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_publication_ddl</primary>
+ </indexterm>
+ <function>pg_get_publication_ddl</function> (
<parameter>publication</parameter> <type>text</type> or
<type>oid</type> )
+ <returnvalue>text</returnvalue>
+ </para>
Maybe say "name" instead of "text" for the parameter; otherwise, there
is no indication of what the text should represent.
~~~
3.
+ <para>
+ Recreate the CREATE statement for a giving PUBLICATION.
+ The result is a complete <command>CREATE
PUBLICATION</command> statement.
+ </para></entry>
+ </row>
+ </tbody>
SUGGESTION
Returns the <command>CREATE PUBLICATION</command> command that would
create this publication.
======
src/backend/utils/adt/ruleutils.c
pg_do_publication_ddl:
4.
+ if (pub == NULL)
+ return (Datum) NULL;
Was a cast needed?
~~~
5.
+ publications = GetPublicationRelations(pub->oid, PUBLICATION_PART_ALL);
AFAIK, this returns relids of the published tables. So this variable
name 'publications' is very misleading.
~~~
6.
+ /*
+ * we add the publication name, this isn't covered by the schema, yet?
+ */
What does that "covered by the schema" part mean? Pubnames are not
schema-qualified (??)
~~~
7.
+ /*
+ * having all tables or all sequence means there's not publications per
+ * table
+ */
This might be true for now, but it might be wise to avoid making this
assumption if it is possible to do so, because one day, "FOR ALL
SEQUENCES, FOR TABLE t1" syntax may be added, and then your assumption
will be broken.
IOW, would it be better not to say 'else'?
~~~
8.
+ if (pub->allsequences)
+ appendStringInfo(buf,
+ "%sALL SEQUENCE",
+ pub->alltables ? ", " : " ");
This should say "ALL SEQUENCES", not "ALL SEQUENCE"
~~~
9.
+ /*
+ * publication an have relations of tables, in schema or current
+ * schema
+ *
+ */
9a.
Unnecessary newline.
~
9b.
Why are you mentioning FOR TABLES IN SCHEMA here? That seems
unnecessary. That clause gets handled separately later.
~~~~
10.
+ /* If no null, means we have a list of columns to publish */
+ if (!cols_nulls)
+ {
+ ArrayType *arr;
+ int ncolumns;
+ int16 *cols;
+ bool fcol = true;
+
+ arr = DatumGetArrayTypeP(columns);
+ ncolumns = ARR_DIMS(arr)[0];
+ cols = (int16 *) ARR_DATA_PTR(arr);
+
+ appendStringInfoChar(buf, '(');
+ for (int i = 0; i < ncolumns; i++)
+ {
+ appendStringInfo(buf, "%s%s",
+ fcol ? "" : ", ",
+ get_attname(rel->rd_id, cols[i], true));
+ fcol = false;
+ }
+ appendStringInfoChar(buf, ')');
+ }
10a.
SUGGESTION
Handle any Column List for this table
~
10b.
Variable 'fcol' is unnecessary; just check i == 0.
~~~
11.
+ /*
+ * If there's a condition goes after the columns but if there's no
+ * column we can have conditions too
+ */
For everything related to Anum_pg_publication_rel_prattrs and
Anum_pg_publication_rel_prqual, the well-known terminology is "Column
Lists" and "Row Filters", so all the variables and comments using
those should be calling them by the proper names.
IIUC, Row Filters are independent of Column Lists, so that code
comment is not very helpful.
SUGGESTION
Handle any Row Filter for this table
~~~
12.
+ appendStringInfo(buf, " WHERE %s ", str);
Why the trailing space? You didn't have one if there was only a Column
List, but no Row Filter.
~~~
13.
+ /* if we have schemas, for sure this will go right before the WITH */
Overcomplicated comment, you don't need to say stuff like "for sure
this will go right before the WITH" because the DDL order of clauses
is obvious from the logic.
SUGGESTION
Handle FOR TABLES IN SCHEMA
~~~
14.
+
+
+ /* always add the WITH options */
+ appendStringInfo(buf, " WITH (");
Double blank line.
~~~
15.
+ /* publish string */
+ appendStringInfo(buf, "publish='");
SUGGESTION
Parameter: 'publish'
~~~
16.
+ /*
+ * by precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
There is no "precedence". The 'publish' values are in this order only
because you chose to put them in this order. This whole comment seems
unnecessary.
~~~
17.
+ /* publish_generated_columns string */
+ appendStringInfo(buf, "publish_generated_columns='%s', ",
+ pub->pubgencols_type == PUBLISH_GENCOLS_NONE ? "none" : "stored");
SUGGESTION
Parameter: 'publish_generated_columns'
~~~
18.
+ /* publish_via_partition_root value */
+ appendStringInfo(buf, "publish_via_partition_root='%s')",
+ pub->pubviaroot ? "true" : "false");
18a
SUGGESTION
Parameter: 'publish_via_partition_root'
~
18b.
Putting the true/false in single quotes is unnecessary, and it looks a
bit strange
======
src/test/regress/sql/publication_ddl.sql
19. GENERAL
I have not checked the tests in detail, but here are some general comments:
19a. There is a mix of upper/lowercase -- e/g/sometimes you say
"create publication", other times you say "ALTER PUBLICATION". Be
consistent.
~
19b.
Some tests are missing comments to introduce them. The spacing
grouping the tests is also inconsistent.
~
19c.
Use proper terminology of "Column Lists" and "Row Filters" instead of
just calling them columns and conditions
~
19d.
All of those cleanup DROPs can be combined; You don't need a separate
DROP statement for every object.
~~~
20.
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
This could've been done earlier same place where you created the other
test schema/tables.
======
FYI, last year I wrote a blog [1] claiming to be the "complete syntax
guide" for CREATE PUBLICATION. Perhaps it is useful as a reference?
======
[1] https://www.postgresql.fastware.com/pzone/tailoring-create-publication-for-logical-replication-a-com...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-02-14 00:21 Cary Huang <cary.huang@highgo.ca>
parent: Peter Smith <smithpb2250@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Cary Huang @ 2026-02-14 00:21 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
The following review has been posted through the commitfest application:
make installcheck-world: not tested
Implements feature: not tested
Spec compliant: not tested
Documentation: not tested
Hello
> +# run the retail DDL tests at the end make sense to not interrupt with other
> +# tests in case the object names are the same to other objects previously used.
> +test: publication_ddl
retail DDL tests is unclear to me. Might as well remove the comment.
>+ if (pub->alltables || pub->allsequences)
>+ {
>+ appendStringInfo(buf, " FOR ");
>+
>+ if (pub->alltables)
>+ appendStringInfo(buf, "ALL TABLES");
>+
>+ /* The sequence are published only on versions 19+ */
>+ if (pub->allsequences)
>+ appendStringInfo(buf,
>+ "%sALL SEQUENCE",
>+ pub->alltables ? ", " : " ");
>+ }
Critical bug / typo here, I think you meant ALL SEQUENCES instead
of ALL SEQUENCE.
>+ if (pub == NULL)
>+ return (Datum) NULL;
use PG_RETURN_NULL() instead like everywhere else for consistency.
Typos in your commit message and everywhere else in code.. For
example:
"giving" → "given", "thuse" → "thus",
"Comprhensive" → "Comprehensive", "fro" → "for"
Need to run the patch through a spelling / typo check tool
Also, you should test pg_get_publication_ddl() with non-existent
publication as well.
Best regards
Cary Huang
Highgo Software
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-02-27 07:53 Hüseyin Demir <huseyin.d3r@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
3 siblings, 0 replies; 22+ messages in thread
From: Hüseyin Demir @ 2026-02-27 07:53 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org; +Cc: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
Hi,
Thanks for the patch. I reviewed v1 and found a few issues that need
to be addressed before commit.
1. In pg_do_publication_ddl(), schemaname is initialized to NULL before
the loop but never reset per iteration. When the first table is in a
non-default schema and the second is in public, the public table
incorrectly inherits the previous schema prefix:
```
CREATE SCHEMA s1;
CREATE TABLE s1.t1 (id int);
CREATE TABLE public.t2 (id int);
CREATE PUBLICATION p FOR TABLE s1.t1, public.t2;
SELECT pg_get_publication_ddl('p');
-- Got: ... FOR TABLE s1.t1, s1.t2 ← WRONG
-- Want: ... FOR TABLE s1.t1, t2
```
2. FOR TABLE ONLY is silently dropped, changing semantics for publications
on tables with inheritance or partitioning:
```
CREATE PUBLICATION p FOR TABLE ONLY t (col) WHERE (col > 0);
SELECT pg_get_publication_ddl('p');
-- Got: FOR TABLE t(col) WHERE (col > 0) WITH ...
-- Want: FOR TABLE ONLY t(col) WHERE (col > 0) WITH ...
```
As already mentioned there are also typos in the code. Otherwise, the idea and way lgtm.
Regards.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-05-17 21:27 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
3 siblings, 3 replies; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-05-17 21:27 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
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!
--
Jonathan Gonzalez V.
EDB: https://www.enterprisedb.com
Attachments:
[text/x-patch] v2-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch (88.8K, ../../defc1876a11710f61228835471c0cdf402167314.camel@gmail.com/2-v2-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch)
download | inline diff:
From 626f15915820da1053c89f546caf4c5ae3419b0d Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>
Date: Sun, 5 Oct 2025 18:11:56 +0200
Subject: [PATCH v2 1/1] Introduce a new function pg_get_publication_ddl() that
returns the CREATE ddl statement for a given PUBLICATION.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
The function accepts a publication name or OID indistinctively,
providing an easy way to call the function as a user from
inside the Postgres code, or any SQL statement.
Comprehensive regression tests are included covering various
possible situations for a given publication.
Reviewed-by: Hüseyin Demir <huseyin.d3r@gmail.com>
Reviewed-by: Cary Huang <cary.huang@highgo.ca>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Reviewed-by: Man Zeng <zengman@halodbtech.com>
Signed-off-by: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 35 +
src/backend/utils/adt/ddlutils.c | 383 +++++++++-
src/include/catalog/pg_proc.dat | 16 +
src/test/regress/expected/publication_ddl.out | 671 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/publication_ddl.sql | 248 +++++++
6 files changed, 1356 insertions(+), 2 deletions(-)
create mode 100644 src/test/regress/expected/publication_ddl.out
create mode 100644 src/test/regress/sql/publication_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..705bf32a17f 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,41 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_publication_ddl</primary>
+ </indexterm>
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>oid</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <command>CREATE PUBLICATION</command> statement for
+ the specified publication (by OID or name), followed by an
+ <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
+ <command>CREATE PUBLICATION</command> grammar has no
+ <literal>OWNER</literal> clause). Each statement is returned as a
+ separate row. An error is raised if no publication with the supplied
+ OID or name exists. When the publication was created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
+ statement always lists <literal>ALL TABLES</literal> before
+ <literal>ALL SEQUENCES</literal> regardless of the original order.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output and
+ <literal>owner</literal> (boolean) to include
+ <literal>OWNER</literal>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..39cdcdf5a63 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,12 +20,14 @@
#include "access/genam.h"
#include "access/htup_details.h"
+#include "access/relation.h"
#include "access/table.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_publication_rel.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,7 +88,10 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
-
+static Datum pg_get_publication_ddl_srf(FunctionCallInfo fcinfo,
+ Oid puboid, bool isnull);
+static List *pg_get_publication_ddl_internal(Oid puboid, bool pretty,
+ bool no_owner);
/*
* parse_ddl_options
@@ -1185,3 +1190,379 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * pg_get_publication_ddl_srf - common SRF logic for publication DDL
+ */
+static Datum
+pg_get_publication_ddl_srf(FunctionCallInfo fcinfo, Oid puboid, bool isnull)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (isnull)
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_publication_ddl_internal(puboid,
+ opts[0].isset && opts[0].boolval,
+ opts[1].isset && !opts[1].boolval);
+
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = (char *) list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+/*
+ * pg_get_publication_ddl_oid
+ * Return DDL to recreate a publication, taking OID.
+ */
+Datum
+pg_get_publication_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid puboid = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ puboid = PG_GETARG_OID(0);
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_name
+ * Return DDL to recreate a publication, taking name.
+ */
+Datum
+pg_get_publication_ddl_name(PG_FUNCTION_ARGS)
+{
+ Oid puboid = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ {
+ char *pubname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ puboid = get_publication_oid(pubname, false);
+ pfree(pubname);
+ }
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_internal
+ * Common code for pg_get_publication_ddl_oid and
+ * pg_get_publication_ddl_name.
+ *
+ * Returns a List of palloc'd strings. The first element is the
+ * CREATE PUBLICATION statement; if no_owner is false a second element
+ * carries an ALTER PUBLICATION ... OWNER TO statement (the CREATE
+ * PUBLICATION grammar has no OWNER clause, so ownership must be applied
+ * as a follow-on statement).
+ */
+static List *
+pg_get_publication_ddl_internal(Oid puboid, bool pretty, bool no_owner)
+{
+ Publication *pub;
+ StringInfo buf;
+ List *statements = NIL;
+ List *pub_incl_relids = NIL;
+ List *pub_excl_relids = NIL;
+ List *pub_schemas = NIL;
+ bool first_perm = true;
+
+ if (!SearchSysCacheExists1(PUBLICATIONOID, ObjectIdGetDatum(puboid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication with OID %u does not exist", puboid)));
+
+ pub = GetPublication(puboid);
+
+ buf = makeStringInfo();
+
+ appendStringInfo(buf, "CREATE PUBLICATION %s", quote_identifier(pub->name));
+
+ /*
+ * Having all tables or all sequences means that there are no per-table
+ * publications
+ */
+ if (pub->alltables || pub->allsequences)
+ {
+ append_ddl_option(buf, pretty, 4, "FOR ");
+
+ if (pub->alltables)
+ {
+ appendStringInfoString(buf, "ALL TABLES");
+ pub_excl_relids = GetExcludedPublicationTables(pub->oid, PUBLICATION_PART_ROOT);
+ }
+ if (pub->allsequences)
+ appendStringInfo(buf,
+ "%sALL SEQUENCES",
+ pub->alltables ? ", " : "");
+ }
+ else
+ {
+ pub_incl_relids = GetIncludedPublicationRelations(pub->oid, PUBLICATION_PART_ROOT);
+ pub_schemas = GetPublicationSchemas(pub->oid);
+ }
+
+ if (pub_incl_relids != NIL)
+ {
+ ListCell *pub_cell;
+ char *schemaname = NULL;
+ char *tablename;
+
+ append_ddl_option(buf, pretty, 4, "FOR TABLE ");
+
+ /*
+ * Publication can have table relations
+ */
+ foreach(pub_cell, pub_incl_relids)
+ {
+ HeapTuple pubtuple = NULL;
+ HeapTuple reltup;
+ Form_pg_class relform;
+ Datum columns,
+ conditions;
+ Oid relid = pub_cell->oid_value;
+ bool cols_nulls,
+ condition_nulls;
+
+ reltup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(reltup))
+ elog(ERROR,
+ "cache lookup failed for relation %u",
+ relid);
+
+ relform = (Form_pg_class) GETSTRUCT(reltup);
+ tablename = NameStr(relform->relname);
+ schemaname = get_namespace_name(relform->relnamespace);
+
+ appendStringInfo(buf, "%s%s",
+ foreach_current_index(pub_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, tablename));
+
+ pfree(schemaname);
+
+ pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (!HeapTupleIsValid(pubtuple))
+ elog(ERROR,
+ "cache lookup failed for publication relation %u in publication %u",
+ relid, pub->oid);
+
+ columns = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &cols_nulls);
+
+ conditions = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prqual,
+ &condition_nulls);
+
+ /* If non-null, we have a list of columns to publish */
+ if (!cols_nulls)
+ {
+ Bitmapset *attmap;
+ int attnum = -1;
+
+ attmap = pub_collist_to_bitmapset(NULL, columns, NULL);
+
+ appendStringInfoChar(buf, '(');
+ while ((attnum = bms_next_member(attmap, attnum)) >= 0)
+ {
+ appendStringInfo(buf, "%s%s",
+ bms_member_index(attmap, attnum) ? ", " : "",
+ quote_identifier(get_attname(relid, attnum, true)));
+ }
+ appendStringInfoChar(buf, ')');
+
+ bms_free(attmap);
+ }
+
+ /*
+ * If there is a condition it goes after the columns. We can have
+ * conditions without columns as well.
+ */
+ if (!condition_nulls)
+ {
+ Node *node;
+ List *context;
+ char *str;
+
+ node = stringToNode(TextDatumGetCString(conditions));
+ context = deparse_context_for(tablename, relid);
+ str = deparse_expression(node, context, false, false);
+ appendStringInfo(buf, " WHERE %s", str);
+ }
+
+ ReleaseSysCache(pubtuple);
+ ReleaseSysCache(reltup);
+ }
+ }
+
+ /* If we have schemas, they will go right before the WITH */
+ if (pub_schemas != NIL)
+ {
+ ListCell *schema_cell;
+
+ /*
+ * Schemas can be preceded by a list of tables. When they are, the
+ * "TABLES IN SCHEMA" stays inline as a continuation of the existing
+ * FOR clause; otherwise it starts the FOR clause on its own line in
+ * pretty mode.
+ */
+ if (pub_incl_relids == NIL)
+ append_ddl_option(buf, pretty, 4, "FOR TABLES IN SCHEMA");
+ else
+ appendStringInfoString(buf, ", TABLES IN SCHEMA");
+
+ foreach(schema_cell, pub_schemas)
+ {
+ char *nspname = get_namespace_name(schema_cell->oid_value);
+
+ appendStringInfo(buf, "%s %s",
+ foreach_current_index(schema_cell) > 0 ? "," : "",
+ quote_identifier(nspname));
+ pfree(nspname);
+ }
+ }
+
+ if (pub_excl_relids != NIL)
+ {
+ ListCell *excl_cell;
+ char *schemaname = NULL;
+
+ appendStringInfoString(buf, " EXCEPT (TABLE ");
+
+ foreach(excl_cell, pub_excl_relids)
+ {
+ HeapTuple tp = SearchSysCache1(RELOID, ObjectIdGetDatum(excl_cell->oid_value));
+ Form_pg_class reltup;
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", excl_cell->oid_value);
+
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ schemaname = get_namespace_name(reltup->relnamespace);
+
+ appendStringInfo(buf, "%s%s",
+ foreach_current_index(excl_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, NameStr(reltup->relname)));
+
+ pfree(schemaname);
+ ReleaseSysCache(tp);
+ }
+
+ appendStringInfoChar(buf, ')');
+ }
+
+ /* Always add the WITH options */
+ append_ddl_option(buf, pretty, 4, "WITH (");
+
+ /* Publish string */
+ appendStringInfoString(buf, "publish='");
+
+ /*
+ * We need to know if we're the second permission added to prefix with a
+ * ", " string
+ */
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * By precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfoString(buf, "insert");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
+ }
+
+ appendStringInfoString(buf, "', ");
+
+ /* publish_generated_columns string */
+ appendStringInfo(buf, "publish_generated_columns='%s', ",
+ pub->pubgencols_type == PUBLISH_GENCOLS_NONE ? "none" : "stored");
+
+ /* publish_via_partition_root value */
+ appendStringInfo(buf, "publish_via_partition_root='%s')",
+ pub->pubviaroot ? "true" : "false");
+
+ appendStringInfoChar(buf, ';');
+ statements = lappend(statements, pstrdup(buf->data));
+
+ /* OWNER */
+ if (!no_owner)
+ {
+ HeapTuple tup;
+ Form_pg_publication pubform;
+ char *owner;
+
+ tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for publication %u", puboid);
+ pubform = (Form_pg_publication) GETSTRUCT(tup);
+ owner = GetUserNameFromId(pubform->pubowner, false);
+ ReleaseSysCache(tup);
+
+ resetStringInfo(buf);
+ appendStringInfo(buf, "ALTER PUBLICATION %s OWNER TO %s;",
+ quote_identifier(pub->name), quote_identifier(owner));
+ pfree(owner);
+ statements = lappend(statements, pstrdup(buf->data));
+ }
+
+ return statements;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..2579b530e28 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12317,6 +12317,22 @@
proname => 'pg_relation_is_publishable', provolatile => 's',
prorettype => 'bool', proargtypes => 'regclass',
prosrc => 'pg_relation_is_publishable' },
+{ oid => '6122', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'oid text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{oid,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_oid' },
+{ oid => '6123', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'text text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{text,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_name' },
# rls
{ oid => '3298',
diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regress/expected/publication_ddl.out
new file mode 100644
index 00000000000..9fcb6582205
--- /dev/null
+++ b/src/test/regress/expected/publication_ddl.out
@@ -0,0 +1,671 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+ERROR: publication "non-existing" does not exist
+SELECT pg_get_publication_ddl(0::oid);
+ERROR: publication with OID 0 does not exist
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+ is_null
+---------
+ t
+(1 row)
+
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+ is_null
+---------
+ t
+(1 row)
+
+-- create base table to test basic table publication
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='update, delete', publish_generated_columns='stored', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 +
+ FOR TABLE public.testpub_ddl_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a condition
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 +
+ FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE (bar = baz) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a condition with an expression
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 +
+ FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE (beque IS TRUE) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 +
+ FOR ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 +
+ FOR ALL TABLES, ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- columns in publication must be quoted
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl5("bar beque");
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl5("bar beque") WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl5("bar beque") WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 +
+ FOR TABLE public.testpub_ddl_tbl5("bar beque") +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+-- create a publication for a list of tables
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_1'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_2'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_3'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_4'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_5'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES FROM (10) TO (20);
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part1'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part3'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='true');
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part4'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 +
+ FOR TABLE public.testpub_ddl_part_p1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('Quoted Pub');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='Quoted Pub'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('Quoted Pub', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "Quoted Pub" +
+ FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE ("Col One" > 0) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION "Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- Tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 +
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except2'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 +
+ FOR ALL TABLES, ALL SEQUENCES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns='none', publish_via_partition_root='false');
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+DROP PUBLICATION testpub_ddl_2;
+DROP PUBLICATION testpub_ddl_3;
+DROP PUBLICATION testpub_ddl_4;
+DROP PUBLICATION testpub_ddl_5;
+DROP PUBLICATION testpub_ddl_6;
+DROP PUBLICATION testpub_ddl_7;
+DROP PUBLICATION testpub_ddl_8;
+DROP PUBLICATION testpub_ddl_9;
+DROP PUBLICATION testpub_ddl_10;
+DROP PUBLICATION testpub_ddl_schema_1;
+DROP PUBLICATION testpub_ddl_schema_2;
+DROP PUBLICATION testpub_ddl_schema_3;
+DROP PUBLICATION testpub_ddl_schema_4;
+DROP PUBLICATION testpub_ddl_schema_5;
+DROP PUBLICATION testpub_ddl_part1;
+DROP PUBLICATION testpub_ddl_part2;
+DROP PUBLICATION testpub_ddl_part3;
+DROP PUBLICATION testpub_ddl_part4;
+DROP PUBLICATION "Quoted Pub";
+DROP PUBLICATION testpub_ddl_except1;
+DROP PUBLICATION testpub_ddl_except2;
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+DROP TABLE schema_tbl1;
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl;
+DROP SCHEMA pub_schema_test_ddl_2;
+DROP SCHEMA "Pub Schema";
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+RESET client_min_messages;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..3d3a6c57af5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -135,7 +135,6 @@ test: compression compression_lz4 compression_pglz cluster
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
-
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
test: event_trigger_login
@@ -143,6 +142,10 @@ test: event_trigger_login
# this test also uses event triggers, so likewise run it by itself
test: fast_default
+# run retail DDL tests last to avoid object name collisions and
+# interference with previous tests.
+test: publication_ddl
+
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sql/publication_ddl.sql
new file mode 100644
index 00000000000..9ec41bc0ae0
--- /dev/null
+++ b/src/test/regress/sql/publication_ddl.sql
@@ -0,0 +1,248 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+SELECT pg_get_publication_ddl(0::oid);
+
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+
+-- create base table to test basic table publication
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+
+-- create publication for one table with two columns and a condition
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+
+-- create publication for one table with two columns and a condition with an expression
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+
+-- columns in publication must be quoted
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl5("bar beque");
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+
+-- create a publication for a list of tables
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES FROM (10) TO (20);
+
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part1'));
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part3'));
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part4'));
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('Quoted Pub');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='Quoted Pub'));
+SELECT pg_get_publication_ddl('Quoted Pub', 'pretty', 'true');
+
+-- Tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+
+-- create publication for all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except2'));
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+DROP PUBLICATION testpub_ddl_2;
+DROP PUBLICATION testpub_ddl_3;
+DROP PUBLICATION testpub_ddl_4;
+DROP PUBLICATION testpub_ddl_5;
+DROP PUBLICATION testpub_ddl_6;
+DROP PUBLICATION testpub_ddl_7;
+DROP PUBLICATION testpub_ddl_8;
+DROP PUBLICATION testpub_ddl_9;
+DROP PUBLICATION testpub_ddl_10;
+DROP PUBLICATION testpub_ddl_schema_1;
+DROP PUBLICATION testpub_ddl_schema_2;
+DROP PUBLICATION testpub_ddl_schema_3;
+DROP PUBLICATION testpub_ddl_schema_4;
+DROP PUBLICATION testpub_ddl_schema_5;
+DROP PUBLICATION testpub_ddl_part1;
+DROP PUBLICATION testpub_ddl_part2;
+DROP PUBLICATION testpub_ddl_part3;
+DROP PUBLICATION testpub_ddl_part4;
+DROP PUBLICATION "Quoted Pub";
+DROP PUBLICATION testpub_ddl_except1;
+DROP PUBLICATION testpub_ddl_except2;
+
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+
+-- cleanup tables in schemas
+DROP TABLE pub_schema_test_ddl.schema_tbl1;
+DROP TABLE pub_schema_test_ddl.schema_tbl2;
+DROP TABLE pub_schema_test_ddl.schema_tbl3;
+DROP TABLE pub_schema_test_ddl_2.schema_tbl1;
+DROP TABLE schema_tbl1;
+
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl;
+DROP SCHEMA pub_schema_test_ddl_2;
+DROP SCHEMA "Pub Schema";
+
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+
+RESET client_min_messages;
--
2.53.0
[application/pgp-signature] signature.asc (832B, ../../defc1876a11710f61228835471c0cdf402167314.camel@gmail.com/3-signature.asc)
download
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-05-20 09:39 Peter Smith <smithpb2250@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 3 replies; 22+ messages in thread
From: Peter Smith @ 2026-05-20 09:39 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi.
Some review comments for the v2-0001 patch.
======
doc/src/sgml/func/func-info.sgml
(9.28.13. Get Object DDL Functions)
1.
+ <para role="func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
I think "pubname" might be a more meaningful name for the first parameter.
~~~
2.
+ <para>
+ Reconstructs the <command>CREATE PUBLICATION</command> statement for
+ the specified publication (by OID or name), followed by an
+ <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
+ <command>CREATE PUBLICATION</command> grammar has no
+ <literal>OWNER</literal> clause). Each statement is returned as a
+ separate row. An error is raised if no publication with the supplied
+ OID or name exists. When the publication was created with
+ <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
+ statement always lists <literal>ALL TABLES</literal> before
+ <literal>ALL SEQUENCES</literal> regardless of the original order.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output and
+ <literal>owner</literal> (boolean) to include
+ <literal>OWNER</literal>.
+ </para></entry>
2a.
That "CREATE PUBLICATION" should <link> back to the CREATE PUBLICATION
docs page.
~
2b.
It is overkill to mention about the potential reordering of ALL TABLES
and ALL SEQUENCES.
Apart from being unnecessary, there are many other things can also be
rearranged which are not mentioned:
- TABLES and ALL TABLES IN SCHEMA clauses might be different order
than specified
- The publication parameters might be in a different order than specified
- The values of 'publish' parameter might be different order than specified
- etc.
~~~
GENERAL
3.
It would be better if the the rows of "Table 9.96" were in alphabetical order.
======
src/backend/utils/adt/ddlutils.c
pg_get_publication_ddl_internal:
4.
+ if (pub->allsequences)
+ appendStringInfo(buf,
+ "%sALL SEQUENCES",
+ pub->alltables ? ", " : "");
Maybe better to avoid tricky format strings.
SUGGESTION
if (pub->allsequences)
{
if (pub->alltables)
appendStringInfo(buf, ", ");
appendStringInfo("ALL SEQUENCES");
}
~~~
5.
+ if (pub_incl_relids != NIL)
+ {
+ ListCell *pub_cell;
+ char *schemaname = NULL;
+ char *tablename;
+
+ append_ddl_option(buf, pretty, 4, "FOR TABLE ");
+
+ /*
+ * Publication can have table relations
+ */
+ foreach(pub_cell, pub_incl_relids)
Maybe that comment belongs earlier (above the if).
~~~
6.
+ appendStringInfo(buf, "%s%s",
+ foreach_current_index(pub_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, tablename));
Another place where avoiding a tricky format string may be tidier.
SUGGESTION
if (foreach_current_index(pub_cell) > 0)
appendStringInfo(buf, ", ");
appendStringInfo(buf, "%s", quote_qualified_identifier(schemaname, tablename));
~~~
7.
+ pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (!HeapTupleIsValid(pubtuple))
+ elog(ERROR,
+ "cache lookup failed for publication relation %u in publication %u",
+ relid, pub->oid);
7a.
Maybe blank line here is not wanted.
~
7b.
Don't need to say "publication" 2x.
/publication relation/relation/
~~~
8.
+ /* If non-null, we have a list of columns to publish */
+ if (!cols_nulls)
SUGGESTION FOR THE COMMENT
Does this table specify a column-list?
~~~
9.
+ appendStringInfo(buf, "%s%s",
+ bms_member_index(attmap, attnum) ? ", " : "",
+ quote_identifier(get_attname(relid, attnum, true)));
Another place where avoiding a tricky format string may be tidier.
(change similar to previous review comments)
~~~
10.
+ /*
+ * If there is a condition it goes after the columns. We can have
+ * conditions without columns as well.
+ */
+ if (!condition_nulls)
10a.
The earlier assignment to 'conditions' should be moved to be directly
above here.
~
10b.
BTW, it is called a "row filter" so maybe it is better to refer to
that in the comments/vars instead of the generic sounding "condition".
~~~
11.
+ /* If we have schemas, they will go right before the WITH */
The kind of comments that just say "this-goes-after-that" or
"this-goes-after-that" are not very useful, because it is obvious from
the code logic that some appendStringInfo comes before or after
another one.
~~~
12.
+ /*
+ * Schemas can be preceded by a list of tables. When they are, the
+ * "TABLES IN SCHEMA" stays inline as a continuation of the existing
+ * FOR clause; otherwise it starts the FOR clause on its own line in
+ * pretty mode.
+ */
IMO it would be better for the FOR TABLE IN SCHEMA to come *before*
the specific tables in FOR TABLE.
e.g. For the case when there are specified tables "absorbed" into the
same named schemas I think it is more natural to see the schemas
first.
CREATE PUBLICATION mypub FOR TABLES IN SCHEMA s, TABLE s.t1;
~~~
13.
+ appendStringInfo(buf, "%s %s",
+ foreach_current_index(schema_cell) > 0 ? "," : "",
+ quote_identifier(nspname));
Another place where avoiding a tricky format string may be tidier.
(change similar to previous review comments)
~~~
14.
+ if (pub_excl_relids != NIL)
+ {
+ ListCell *excl_cell;
+ char *schemaname = NULL;
+
+ appendStringInfoString(buf, " EXCEPT (TABLE ");
The EXCEPT clause is currently permitted only with FOR ALL TABLES, so
it would be better moving this to earlier in this function where
pub->alltables was handled.
~~~
15.
+ appendStringInfo(buf, "%s%s",
+ foreach_current_index(excl_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, NameStr(reltup->relname)));
Another place where avoiding a tricky format string may be tidier.
(change similar to previous review comments)
~~~
16.
+ /*
+ * We need to know if we're the second permission added to prefix with a
+ * ", " string
+ */
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * By precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfoString(buf, "insert");
Both these comments are doing little more than just saying the same as
the code. IMO they are not needed.
~~~
17.
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * By precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfoString(buf, "insert");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
+ }
+
17a.
There are some random blank lines that seem unnecessary.
~
17b.
IMO it is tidier to simply append the string you want, instead of
using a trick format string.
SUGGESTION (compare with patch)
if (pub->pubactions.pubinsert)
{
appendStringInfoString(buf, "insert");
first_perm = false;
}
if (pub->pubactions.pubupdate)
{
appendStringInfo(buf, first_perm ? "update" : ",update");
first_perm = false;
}
if (pub->pubactions.pubdelete)
{
appendStringInfo(buf, first_perm ? "delete" : ",delete");
first_perm = false;
}
if (pub->pubactions.pubtruncate)
{
appendStringInfo(buf, first_perm ? "truncate" : ",truncate");
}
======
src/test/regress/expected/publication_ddl.out
18.
+ CREATE PUBLICATION testpub_ddl_1
+
+ WITH (publish='insert, update, delete, truncate',
publish_generated_columns='none', publish_via_partition_root='false');
~
This "pretty" output appears to have "+" garbage in it. What's that
about -- it looks like some sort of line continuation character? Can
it be removed?
~~~
19.
The "pretty" output might be improved if each of the publication
options was on a new line.
~~~
20.
The generated boolean values (e.g. 'true'/'false') do not need to be quoted.
======
src/test/regress/sql/publication_ddl.sql
(Here are lots of test review comments; the first group are are
general so might apply to multiple test cases).
21.
I think you can create all the necessary schema and tables together
up-front instead of scatering them through the file.
~~~
22.
Make use of the proper publication teminology like "Column Lists" and
"Row Filters" instead of vague
"columns" and "conditions".
~~~
23.
Here is an idea:
Instead of having dozens of test publications, just have 1 test
publication, which you CREATE/DROP for each test case.
Then, since there is a fixed name publication (e.g. "mypub") for
everything, you can make a subroutine to encapsulate the common code:
+SELECT pg_get_publication_ddl('mypub');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE
pubname='mypub'));
+SELECT pg_get_publication_ddl('mypub', 'pretty', 'true');
It means your test .sql file can become much shorter/simpler I think.
~~~
24.
There is duplication of some tests:
e.g.
+-- columns in publication must be quoted
and
+-- identifiers that require quoting: publication, schema, table and column
~~~
25.
It is not needed to quote the booleans 'true'/'false' for the options.
//////
26.
+-- create base table to test basic table publication
What does "basic table publication" mean? I expect it means different
things to different people. Better to be explicit about what this is
really testing.
~~~
27.
+-- create publication for one table with two columns and a condition
with an expression
What does "with an expression" mean? All Row-Filters are expressions
aren't they?
~~~
28.
+-- create a publication for a list of tables
Not really describing what this test is doing, which is mixing FOR
TABLE and FOR TABLES IN SCHEMA.
~~~
29.
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH
(publish_via_partition_root='true');
I guess it make no difference since these are only DDL syntax tests,
but it didn't really make much sense to set
publish_via_partition_root=true when testpub_ddl_part is the ROOT
anyway.
~~~
30.
+-- create publication for all tables except two tables
Actually this is also combining with an ALL SEQUENCES test.
~~~
31.
+-- cleanup publications
+DROP PUBLICATION testpub_ddl_1;
+DROP PUBLICATION testpub_ddl_2;
+DROP PUBLICATION testpub_ddl_3;
+DROP PUBLICATION testpub_ddl_4;
+DROP PUBLICATION testpub_ddl_5;
+DROP PUBLICATION testpub_ddl_6;
+DROP PUBLICATION testpub_ddl_7;
+DROP PUBLICATION testpub_ddl_8;
+DROP PUBLICATION testpub_ddl_9;
+DROP PUBLICATION testpub_ddl_10;
+DROP PUBLICATION testpub_ddl_schema_1;
+DROP PUBLICATION testpub_ddl_schema_2;
+DROP PUBLICATION testpub_ddl_schema_3;
+DROP PUBLICATION testpub_ddl_schema_4;
+DROP PUBLICATION testpub_ddl_schema_5;
+DROP PUBLICATION testpub_ddl_part1;
+DROP PUBLICATION testpub_ddl_part2;
+DROP PUBLICATION testpub_ddl_part3;
+DROP PUBLICATION testpub_ddl_part4;
+DROP PUBLICATION "Quoted Pub";
+DROP PUBLICATION testpub_ddl_except1;
+DROP PUBLICATION testpub_ddl_except2;
As per earlier review comment IMO it would make the test code simpler
to have just 1 publication that you CREATE/DROP om the fly.
~~~
32.
+-- cleanup tables in schemas
Not sure why this is done separately. Probably easier just to drop the
schemas with CASCADE so their tables will be auto-deleted.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-05-20 10:00 Japin Li <japinli@hotmail.com>
parent: Peter Smith <smithpb2250@gmail.com>
2 siblings, 1 reply; 22+ messages in thread
From: Japin Li @ 2026-05-20 10:00 UTC (permalink / raw)
To: Peter Smith <smithpb2250@gmail.com>; +Cc: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; pgsql-hackers@lists.postgresql.org
On Wed, 20 May 2026 at 19:39, Peter Smith <smithpb2250@gmail.com> wrote:
> Hi.
>
> Some review comments for the v2-0001 patch.
>
> ======
> doc/src/sgml/func/func-info.sgml
>
> (9.28.13. Get Object DDL Functions)
>
> 1.
> + <para role="func_signature">
> + <function>pg_get_publication_ddl</function>
> + ( <parameter>publication</parameter> <type>text</type>
> + <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
> + <type>text</type> </optional> )
> + <returnvalue>setof text</returnvalue>
> + </para>
>
> I think "pubname" might be a more meaningful name for the first parameter.
>
> ~~~
>
> 2.
> + <para>
> + Reconstructs the <command>CREATE PUBLICATION</command> statement for
> + the specified publication (by OID or name), followed by an
> + <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
> + <command>CREATE PUBLICATION</command> grammar has no
> + <literal>OWNER</literal> clause). Each statement is returned as a
> + separate row. An error is raised if no publication with the supplied
> + OID or name exists. When the publication was created with
> + <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
> + statement always lists <literal>ALL TABLES</literal> before
> + <literal>ALL SEQUENCES</literal> regardless of the original order.
> + The following options are supported:
> + <literal>pretty</literal> (boolean) for formatted output and
> + <literal>owner</literal> (boolean) to include
> + <literal>OWNER</literal>.
> + </para></entry>
>
> 2a.
> That "CREATE PUBLICATION" should <link> back to the CREATE PUBLICATION
> docs page.
>
> ~
>
> 2b.
> It is overkill to mention about the potential reordering of ALL TABLES
> and ALL SEQUENCES.
>
> Apart from being unnecessary, there are many other things can also be
> rearranged which are not mentioned:
> - TABLES and ALL TABLES IN SCHEMA clauses might be different order
> than specified
> - The publication parameters might be in a different order than specified
> - The values of 'publish' parameter might be different order than specified
> - etc.
>
> ~~~
>
> GENERAL
>
> 3.
> It would be better if the the rows of "Table 9.96" were in alphabetical order.
>
> ======
> src/backend/utils/adt/ddlutils.c
>
> pg_get_publication_ddl_internal:
>
> 4.
> + if (pub->allsequences)
> + appendStringInfo(buf,
> + "%sALL SEQUENCES",
> + pub->alltables ? ", " : "");
>
> Maybe better to avoid tricky format strings.
>
> SUGGESTION
> if (pub->allsequences)
> {
> if (pub->alltables)
> appendStringInfo(buf, ", ");
>
> appendStringInfo("ALL SEQUENCES");
> }
>
> ~~~
>
> 5.
> + if (pub_incl_relids != NIL)
> + {
> + ListCell *pub_cell;
> + char *schemaname = NULL;
> + char *tablename;
> +
> + append_ddl_option(buf, pretty, 4, "FOR TABLE ");
> +
> + /*
> + * Publication can have table relations
> + */
> + foreach(pub_cell, pub_incl_relids)
>
> Maybe that comment belongs earlier (above the if).
>
> ~~~
>
> 6.
> + appendStringInfo(buf, "%s%s",
> + foreach_current_index(pub_cell) > 0 ? ", " : "",
> + quote_qualified_identifier(schemaname, tablename));
>
> Another place where avoiding a tricky format string may be tidier.
>
> SUGGESTION
> if (foreach_current_index(pub_cell) > 0)
> appendStringInfo(buf, ", ");
>
> appendStringInfo(buf, "%s", quote_qualified_identifier(schemaname, tablename));
>
> ~~~
>
> 7.
> + pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
> + ObjectIdGetDatum(pub->oid));
> +
> + if (!HeapTupleIsValid(pubtuple))
> + elog(ERROR,
> + "cache lookup failed for publication relation %u in publication %u",
> + relid, pub->oid);
>
> 7a.
> Maybe blank line here is not wanted.
>
> ~
>
> 7b.
> Don't need to say "publication" 2x.
>
> /publication relation/relation/
>
> ~~~
>
> 8.
> + /* If non-null, we have a list of columns to publish */
> + if (!cols_nulls)
>
> SUGGESTION FOR THE COMMENT
> Does this table specify a column-list?
>
> ~~~
>
> 9.
> + appendStringInfo(buf, "%s%s",
> + bms_member_index(attmap, attnum) ? ", " : "",
> + quote_identifier(get_attname(relid, attnum, true)));
>
> Another place where avoiding a tricky format string may be tidier.
>
> (change similar to previous review comments)
>
> ~~~
>
> 10.
> + /*
> + * If there is a condition it goes after the columns. We can have
> + * conditions without columns as well.
> + */
> + if (!condition_nulls)
>
> 10a.
> The earlier assignment to 'conditions' should be moved to be directly
> above here.
>
> ~
>
> 10b.
> BTW, it is called a "row filter" so maybe it is better to refer to
> that in the comments/vars instead of the generic sounding "condition".
>
> ~~~
>
> 11.
> + /* If we have schemas, they will go right before the WITH */
>
> The kind of comments that just say "this-goes-after-that" or
> "this-goes-after-that" are not very useful, because it is obvious from
> the code logic that some appendStringInfo comes before or after
> another one.
>
> ~~~
>
> 12.
> + /*
> + * Schemas can be preceded by a list of tables. When they are, the
> + * "TABLES IN SCHEMA" stays inline as a continuation of the existing
> + * FOR clause; otherwise it starts the FOR clause on its own line in
> + * pretty mode.
> + */
>
> IMO it would be better for the FOR TABLE IN SCHEMA to come *before*
> the specific tables in FOR TABLE.
>
> e.g. For the case when there are specified tables "absorbed" into the
> same named schemas I think it is more natural to see the schemas
> first.
> CREATE PUBLICATION mypub FOR TABLES IN SCHEMA s, TABLE s.t1;
>
> ~~~
>
> 13.
> + appendStringInfo(buf, "%s %s",
> + foreach_current_index(schema_cell) > 0 ? "," : "",
> + quote_identifier(nspname));
>
> Another place where avoiding a tricky format string may be tidier.
>
> (change similar to previous review comments)
>
> ~~~
>
> 14.
> + if (pub_excl_relids != NIL)
> + {
> + ListCell *excl_cell;
> + char *schemaname = NULL;
> +
> + appendStringInfoString(buf, " EXCEPT (TABLE ");
>
> The EXCEPT clause is currently permitted only with FOR ALL TABLES, so
> it would be better moving this to earlier in this function where
> pub->alltables was handled.
>
> ~~~
>
> 15.
> + appendStringInfo(buf, "%s%s",
> + foreach_current_index(excl_cell) > 0 ? ", " : "",
> + quote_qualified_identifier(schemaname, NameStr(reltup->relname)));
>
> Another place where avoiding a tricky format string may be tidier.
>
> (change similar to previous review comments)
>
> ~~~
>
> 16.
> + /*
> + * We need to know if we're the second permission added to prefix with a
> + * ", " string
> + */
> + if (pub->pubactions.pubinsert)
> + {
> + /*
> + * By precedence we know that the insert will always be first, no need
> + * to check previous values
> + */
> + appendStringInfoString(buf, "insert");
>
> Both these comments are doing little more than just saying the same as
> the code. IMO they are not needed.
>
> ~~~
>
> 17.
> + if (pub->pubactions.pubinsert)
> + {
> + /*
> + * By precedence we know that the insert will always be first, no need
> + * to check previous values
> + */
> + appendStringInfoString(buf, "insert");
> + first_perm = false;
> + }
> +
> + if (pub->pubactions.pubupdate)
> + {
> + appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
> + first_perm = false;
> + }
> + if (pub->pubactions.pubdelete)
> + {
> + appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
> + first_perm = false;
> + }
> +
> + if (pub->pubactions.pubtruncate)
> + {
> + appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
> + }
> +
>
> 17a.
> There are some random blank lines that seem unnecessary.
>
> ~
>
> 17b.
> IMO it is tidier to simply append the string you want, instead of
> using a trick format string.
>
> SUGGESTION (compare with patch)
>
> if (pub->pubactions.pubinsert)
> {
> appendStringInfoString(buf, "insert");
> first_perm = false;
> }
> if (pub->pubactions.pubupdate)
> {
> appendStringInfo(buf, first_perm ? "update" : ",update");
> first_perm = false;
> }
> if (pub->pubactions.pubdelete)
> {
> appendStringInfo(buf, first_perm ? "delete" : ",delete");
> first_perm = false;
> }
> if (pub->pubactions.pubtruncate)
> {
> appendStringInfo(buf, first_perm ? "truncate" : ",truncate");
> }
>
> ======
> src/test/regress/expected/publication_ddl.out
>
> 18.
> + CREATE PUBLICATION testpub_ddl_1
> +
> + WITH (publish='insert, update, delete, truncate',
> publish_generated_columns='none', publish_via_partition_root='false');
>
> ~
>
> This "pretty" output appears to have "+" garbage in it. What's that
> about -- it looks like some sort of line continuation character? Can
> it be removed?
>
> ~~~
>
> 19.
> The "pretty" output might be improved if each of the publication
> options was on a new line.
>
> ~~~
>
> 20.
> The generated boolean values (e.g. 'true'/'false') do not need to be quoted.
>
> ======
> src/test/regress/sql/publication_ddl.sql
>
> (Here are lots of test review comments; the first group are are
> general so might apply to multiple test cases).
>
> 21.
> I think you can create all the necessary schema and tables together
> up-front instead of scatering them through the file.
>
> ~~~
>
> 22.
> Make use of the proper publication teminology like "Column Lists" and
> "Row Filters" instead of vague
> "columns" and "conditions".
>
> ~~~
>
> 23.
> Here is an idea:
>
> Instead of having dozens of test publications, just have 1 test
> publication, which you CREATE/DROP for each test case.
>
> Then, since there is a fixed name publication (e.g. "mypub") for
> everything, you can make a subroutine to encapsulate the common code:
>
> +SELECT pg_get_publication_ddl('mypub');
> +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE
> pubname='mypub'));
> +SELECT pg_get_publication_ddl('mypub', 'pretty', 'true');
>
> It means your test .sql file can become much shorter/simpler I think.
>
> ~~~
>
> 24.
> There is duplication of some tests:
>
> e.g.
> +-- columns in publication must be quoted
> and
> +-- identifiers that require quoting: publication, schema, table and column
>
> ~~~
>
> 25.
> It is not needed to quote the booleans 'true'/'false' for the options.
>
> //////
>
> 26.
> +-- create base table to test basic table publication
>
> What does "basic table publication" mean? I expect it means different
> things to different people. Better to be explicit about what this is
> really testing.
>
> ~~~
>
> 27.
> +-- create publication for one table with two columns and a condition
> with an expression
>
> What does "with an expression" mean? All Row-Filters are expressions
> aren't they?
>
> ~~~
>
> 28.
> +-- create a publication for a list of tables
>
> Not really describing what this test is doing, which is mixing FOR
> TABLE and FOR TABLES IN SCHEMA.
>
> ~~~
>
> 29.
> +CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH
> (publish_via_partition_root='true');
>
> I guess it make no difference since these are only DDL syntax tests,
> but it didn't really make much sense to set
> publish_via_partition_root=true when testpub_ddl_part is the ROOT
> anyway.
>
> ~~~
>
> 30.
> +-- create publication for all tables except two tables
>
> Actually this is also combining with an ALL SEQUENCES test.
>
> ~~~
>
> 31.
> +-- cleanup publications
> +DROP PUBLICATION testpub_ddl_1;
> +DROP PUBLICATION testpub_ddl_2;
> +DROP PUBLICATION testpub_ddl_3;
> +DROP PUBLICATION testpub_ddl_4;
> +DROP PUBLICATION testpub_ddl_5;
> +DROP PUBLICATION testpub_ddl_6;
> +DROP PUBLICATION testpub_ddl_7;
> +DROP PUBLICATION testpub_ddl_8;
> +DROP PUBLICATION testpub_ddl_9;
> +DROP PUBLICATION testpub_ddl_10;
> +DROP PUBLICATION testpub_ddl_schema_1;
> +DROP PUBLICATION testpub_ddl_schema_2;
> +DROP PUBLICATION testpub_ddl_schema_3;
> +DROP PUBLICATION testpub_ddl_schema_4;
> +DROP PUBLICATION testpub_ddl_schema_5;
> +DROP PUBLICATION testpub_ddl_part1;
> +DROP PUBLICATION testpub_ddl_part2;
> +DROP PUBLICATION testpub_ddl_part3;
> +DROP PUBLICATION testpub_ddl_part4;
> +DROP PUBLICATION "Quoted Pub";
> +DROP PUBLICATION testpub_ddl_except1;
> +DROP PUBLICATION testpub_ddl_except2;
>
> As per earlier review comment IMO it would make the test code simpler
> to have just 1 publication that you CREATE/DROP om the fly.
>
> ~~~
>
> 32.
> +-- cleanup tables in schemas
>
> Not sure why this is done separately. Probably easier just to drop the
> schemas with CASCADE so their tables will be auto-deleted.
>
+ buf = makeStringInfo();
I have one more comment: where possible, we should use stack-allocated
StringInfoData objects, as was done in commits a63bbc811d4 and 6d0eba66275.
> ======
> Kind Regards,
> Peter Smith.
> Fujitsu Australia
--
Regards,
Japin Li
ChengDu WenWu Information Technology Co., Ltd.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-05-20 22:27 Peter Smith <smithpb2250@gmail.com>
parent: Peter Smith <smithpb2250@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: Peter Smith @ 2026-05-20 22:27 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Wed, May 20, 2026 at 7:39 PM Peter Smith <smithpb2250@gmail.com> wrote:
...
>
> 18.
> + CREATE PUBLICATION testpub_ddl_1
> +
> + WITH (publish='insert, update, delete, truncate',
> publish_generated_columns='none', publish_via_partition_root='false');
>
> ~
>
> This "pretty" output appears to have "+" garbage in it. What's that
> about -- it looks like some sort of line continuation character? Can
> it be removed?
>
Later, I learned that this is a character added by psql to indicate
rows that span multiple lines. So, please ignore this comment.
======
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-02 11:10 solai v <solai.cdac@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 1 reply; 22+ messages in thread
From: solai v @ 2026-06-02 11:10 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi all,
On Mon, May 25, 2026 at 5:51 PM Jonathan Gonzalez V.
<jonathan.abdiel@gmail.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
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 11:15 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
parent: Peter Smith <smithpb2250@gmail.com>
2 siblings, 2 replies; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-06-09 11:15 UTC (permalink / raw)
To: Peter Smith <smithpb2250@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Peter Smith <smithpb2250@gmail.com> writes:
Hi!
> ======
> doc/src/sgml/func/func-info.sgml
>
> (9.28.13. Get Object DDL Functions)
>
> 1.
> + <para role="func_signature">
> + <function>pg_get_publication_ddl</function>
> + ( <parameter>publication</parameter> <type>text</type>
> + <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
> + <type>text</type> </optional> )
> + <returnvalue>setof text</returnvalue>
> + </para>
>
> I think "pubname" might be a more meaningful name for the first parameter.
This will make sense, but since the already pushed DDL functions are
using the direct name like `database` or `tablespace` directly, make
sense to me to follow the same pattern, probably we can ask more people
what they think about this?
> ~~~
>
> 2.
> + <para>
> + Reconstructs the <command>CREATE PUBLICATION</command> statement for
> + the specified publication (by OID or name), followed by an
> + <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
> + <command>CREATE PUBLICATION</command> grammar has no
> + <literal>OWNER</literal> clause). Each statement is returned as a
> + separate row. An error is raised if no publication with the supplied
> + OID or name exists. When the publication was created with
> + <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
> + statement always lists <literal>ALL TABLES</literal> before
> + <literal>ALL SEQUENCES</literal> regardless of the original order.
> + The following options are supported:
> + <literal>pretty</literal> (boolean) for formatted output and
> + <literal>owner</literal> (boolean) to include
> + <literal>OWNER</literal>.
> + </para></entry>
>
> 2a.
> That "CREATE PUBLICATION" should <link> back to the CREATE PUBLICATION
> docs page.
This is indeed a good idea, I would love to see this also in the other
patchs, probably another patch to update all the functions will be good.
Applied for the next version
> ~
>
> 2b.
> It is overkill to mention about the potential reordering of ALL TABLES
> and ALL SEQUENCES.
>
> Apart from being unnecessary, there are many other things can also be
> rearranged which are not mentioned:
> - TABLES and ALL TABLES IN SCHEMA clauses might be different order
> than specified
> - The publication parameters might be in a different order than specified
> - The values of 'publish' parameter might be different order than specified
> - etc.
Agree, removed.
> ~~~
>
> GENERAL
>
> 3.
> It would be better if the the rows of "Table 9.96" were in alphabetical order.
I think that this should be done in a different patch when all or a big
part of the functions are merged.
> ======
> src/backend/utils/adt/ddlutils.c
>
> pg_get_publication_ddl_internal:
>
> 4.
> + if (pub->allsequences)
> + appendStringInfo(buf,
> + "%sALL SEQUENCES",
> + pub->alltables ? ", " : "");
>
> Maybe better to avoid tricky format strings.
>
> SUGGESTION
> if (pub->allsequences)
> {
> if (pub->alltables)
> appendStringInfo(buf, ", ");
>
> appendStringInfo("ALL SEQUENCES");
> }
I don't have a strong opinion on this being "tricky" but it's being used
in many places already, specially in the pg_get_*_ddl functions, but
it's clear where the comma should be if the condition is true, with your
suggestion it's a bit harder to read I think. I would like to have more
opinions on this
> ~~~
>
> 5.
> + if (pub_incl_relids != NIL)
> + {
> + ListCell *pub_cell;
> + char *schemaname = NULL;
> + char *tablename;
> +
> + append_ddl_option(buf, pretty, 4, "FOR TABLE ");
> +
> + /*
> + * Publication can have table relations
> + */
> + foreach(pub_cell, pub_incl_relids)
>
> Maybe that comment belongs earlier (above the if).
Yes! Indeed, it was a leftover from the previous refactor, thank you!
> ~~~
>
> 6.
> + appendStringInfo(buf, "%s%s",
> + foreach_current_index(pub_cell) > 0 ? ", " : "",
> + quote_qualified_identifier(schemaname, tablename));
>
> Another place where avoiding a tricky format string may be tidier.
>
> SUGGESTION
> if (foreach_current_index(pub_cell) > 0)
> appendStringInfo(buf, ", ");
>
> appendStringInfo(buf, "%s", quote_qualified_identifier(schemaname, tablename));
Same as above
> ~~~
>
> 7.
> + pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
> + ObjectIdGetDatum(pub->oid));
> +
> + if (!HeapTupleIsValid(pubtuple))
> + elog(ERROR,
> + "cache lookup failed for publication relation %u in publication %u",
> + relid, pub->oid);
>
> 7a.
> Maybe blank line here is not wanted.
It gives some space, it was intentional.
> ~
>
> 7b.
> Don't need to say "publication" 2x.
>
> /publication relation/relation/
Indeed.
> ~~~
>
> 10.
> + /*
> + * If there is a condition it goes after the columns. We can have
> + * conditions without columns as well.
> + */
> + if (!condition_nulls)
>
> 10a.
> The earlier assignment to 'conditions' should be moved to be directly
> above here.
In this case I think it's better to have both calls to SysCacheGetAttr()
together since both depends on the previous call of SearchSysCache2()
> ~
>
> 10b.
> BTW, it is called a "row filter" so maybe it is better to refer to
> that in the comments/vars instead of the generic sounding "condition".
Changed!
> ~~~
>
> 11.
> + /* If we have schemas, they will go right before the WITH */
>
> The kind of comments that just say "this-goes-after-that" or
> "this-goes-after-that" are not very useful, because it is obvious from
> the code logic that some appendStringInfo comes before or after
> another one.
I don't agree on this one, since you're building a DDL, while reading
the code these message helped me a lot of keep in mind what's go first
and after, the comment born as a helper for the order of the strings.
> ~~~
>
> 12.
> + /*
> + * Schemas can be preceded by a list of tables. When they are, the
> + * "TABLES IN SCHEMA" stays inline as a continuation of the existing
> + * FOR clause; otherwise it starts the FOR clause on its own line in
> + * pretty mode.
> + */
>
> IMO it would be better for the FOR TABLE IN SCHEMA to come *before*
> the specific tables in FOR TABLE.
>
> e.g. For the case when there are specified tables "absorbed" into the
> same named schemas I think it is more natural to see the schemas
> first.
> CREATE PUBLICATION mypub FOR TABLES IN SCHEMA s, TABLE s.t1;
This decision was made based on the documentation[1] and the order comes
from the appearance there. I don't have an strong opinion on the order,
but I notice something, in the documentation it says:
where publication_object is one of:
So in theory it should be FOR TABLE or FOR TABLES IN SCHEMA, so now I
have a confusion, because having both it is actually possible.
> ~~~
>
> 14.
> + if (pub_excl_relids != NIL)
> + {
> + ListCell *excl_cell;
> + char *schemaname = NULL;
> +
> + appendStringInfoString(buf, " EXCEPT (TABLE ");
>
> The EXCEPT clause is currently permitted only with FOR ALL TABLES, so
> it would be better moving this to earlier in this function where
> pub->alltables was handled.
Probably this will make sense and also will require to refactor the
entire code, but there's discussions[2][3] about extending that already.
Attaching all the EXCEPT clause only to the FOR ALL TABLES looks to me
that it will create just more work for the future.
> 16.
> + /*
> + * We need to know if we're the second permission added to prefix with a
> + * ", " string
> + */
> + if (pub->pubactions.pubinsert)
> + {
> + /*
> + * By precedence we know that the insert will always be first, no need
> + * to check previous values
> + */
> + appendStringInfoString(buf, "insert");
>
> Both these comments are doing little more than just saying the same as
> the code. IMO they are not needed.
Agree, removed the first one.
> ~~~
>
> 17.
> + if (pub->pubactions.pubinsert)
> + {
> + /*
> + * By precedence we know that the insert will always be first, no need
> + * to check previous values
> + */
> + appendStringInfoString(buf, "insert");
> + first_perm = false;
> + }
> +
> + if (pub->pubactions.pubupdate)
> + {
> + appendStringInfo(buf, "%supdate", first_perm ? "" : ", ");
> + first_perm = false;
> + }
> + if (pub->pubactions.pubdelete)
> + {
> + appendStringInfo(buf, "%sdelete", first_perm ? "" : ", ");
> + first_perm = false;
> + }
> +
> + if (pub->pubactions.pubtruncate)
> + {
> + appendStringInfo(buf, "%struncate", first_perm ? "" : ", ");
> + }
> +
>
> 17a.
> There are some random blank lines that seem unnecessary.
True!
> ~
>
> 17b.
> IMO it is tidier to simply append the string you want, instead of
> using a trick format string.
>
> SUGGESTION (compare with patch)
>
> if (pub->pubactions.pubinsert)
> {
> appendStringInfoString(buf, "insert");
> first_perm = false;
> }
> if (pub->pubactions.pubupdate)
> {
> appendStringInfo(buf, first_perm ? "update" : ",update");
> first_perm = false;
> }
> if (pub->pubactions.pubdelete)
> {
> appendStringInfo(buf, first_perm ? "delete" : ",delete");
> first_perm = false;
> }
> if (pub->pubactions.pubtruncate)
> {
> appendStringInfo(buf, first_perm ? "truncate" : ",truncate");
> }
I really prefer not to repeat a word twice in the same line just to add
a `,` at the beginning.
> ~~~
>
> 20.
> The generated boolean values (e.g. 'true'/'false') do not need to be quoted.
Changed
> ======
> src/test/regress/sql/publication_ddl.sql
>
> (Here are lots of test review comments; the first group are are
> general so might apply to multiple test cases).
>
> 21.
> I think you can create all the necessary schema and tables together
> up-front instead of scatering them through the file.
Probably, but I prefer to create and use, rather than create and expect
to use it or if in the future a test is removed should also remove the
created tables and schemas.
> ~~~
>
> 22.
> Make use of the proper publication teminology like "Column Lists" and
> "Row Filters" instead of vague
> "columns" and "conditions".
Agree on use row filters, changed.
> ~~~
>
> 23.
> Here is an idea:
>
> Instead of having dozens of test publications, just have 1 test
> publication, which you CREATE/DROP for each test case.
>
> Then, since there is a fixed name publication (e.g. "mypub") for
> everything, you can make a subroutine to encapsulate the common code:
>
> +SELECT pg_get_publication_ddl('mypub');
> +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE
> pubname='mypub'));
> +SELECT pg_get_publication_ddl('mypub', 'pretty', 'true');
>
> It means your test .sql file can become much shorter/simpler I think.
I tried that at the beginning, but after having many similar tests and
lot's of them, finding the one with error of "mypub" was really hard, so
using a name plus a number make it easy to find which one was failing
rather than look for the same name multiple times
> ~~~
>
> 24.
> There is duplication of some tests:
>
> e.g.
> +-- columns in publication must be quoted
> and
> +-- identifiers that require quoting: publication, schema, table and column
Indeed the first one should be removed since the second one will cover
more cases.
> ~~~
>
> 25.
> It is not needed to quote the booleans 'true'/'false' for the options.
>
> //////
Can you provide an example? didn't understood this one.
> 26.
> +-- create base table to test basic table publication
>
> What does "basic table publication" mean? I expect it means different
> things to different people. Better to be explicit about what this is
> really testing.
True, changed.
> ~~~
>
> 27.
> +-- create publication for one table with two columns and a condition
> with an expression
>
> What does "with an expression" mean? All Row-Filters are expressions
> aren't they?
Changed.
> ~~~
>
> 28.
> +-- create a publication for a list of tables
>
> Not really describing what this test is doing, which is mixing FOR
> TABLE and FOR TABLES IN SCHEMA.
Changed
> ~~~
>
> 30.
> +-- create publication for all tables except two tables
>
> Actually this is also combining with an ALL SEQUENCES test.
Changed
> ~~~
>
> 32.
> +-- cleanup tables in schemas
>
> Not sure why this is done separately. Probably easier just to drop the
> schemas with CASCADE so their tables will be auto-deleted.
Separately to not create tables that aren't used, but sure a CASCADE now
can be useful since I don't think more tests will be added.
[1] https://www.postgresql.org/docs/devel/sql-createpublication.html
[2] https://www.postgresql.org/message-id/flat/CANhcyEVSXyQkvmrsOWPdQqnm2J3GMyQQrKhyCJiBQzqs6AvSow%40mai...
[3] https://www.postgresql.org/message-id/flat/CABdArM5sw4Q1ZU8HGdo4BSc1A_%2B8xtUNq17j6wcir%3DyMUy19Cg%4...
--
Jonathan Gonzalez V.
EDB
https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 11:35 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
parent: solai v <solai.cdac@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-06-09 11:35 UTC (permalink / raw)
To: solai v <solai.cdac@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
solai v <solai.cdac@gmail.com> writes:
Hello,
>
> [...] 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.
Yes this is by design, it's documented that they will return the
OWNER[1] unless that publications doesn't intend to have an OWNER in the
DDL.
> 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
Yes, this was already handled by Peter Smith[1] email
Regards!
[1] https://www.postgresql.org/docs/19/functions-info.html#FUNCTIONS-GET-OBJECT-DDL
[2] https://www.postgresql.org/message-id/CAHut%2BPvyd3vN_LV8ppyX6Vu7pKdBhC5M3_zHN7gdJCvz1%3DkKHQ%40mail...
--
Jonathan Gonzalez V.
EDB
https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 11:43 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
parent: Japin Li <japinli@hotmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-06-09 11:43 UTC (permalink / raw)
To: Japin Li <japinli@hotmail.com>; +Cc: Peter Smith <smithpb2250@gmail.com>; pgsql-hackers@lists.postgresql.org
Japin Li <japinli@hotmail.com> writes:
Hi!
>
> + buf = makeStringInfo();
>
> I have one more comment: where possible, we should use stack-allocated
> StringInfoData objects, as was done in commits a63bbc811d4 and 6d0eba66275.
Makes sense! Will do the changes for the v3
Thank you!
--
Jonathan Gonzalez V.
EDB
https://www.enterprisedb.com
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 18:17 Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 3 replies; 22+ messages in thread
From: Jonathan Gonzalez V. @ 2026-06-09 18:17 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
"Jonathan Gonzalez V." <jonathan.abdiel@gmail.com> writes:
Hi!
I'm attaching the v3 of this patch. I've added a new set of regress test
that execute the query generated by pg_get_publication_ddl(), this
helped to find a couple of issues with EXCEPT and WHERE.
I've also applied the reviews previously done and discussed early today.
Regards
--
Jonathan Gonzalez V.
EDB
https://www.enterprisedb.com
Attachments:
[text/x-diff] v3-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch (94.9K, ../../87o6hjy3we.fsf@gmail.com/2-v3-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch)
download | inline diff:
From bc57378ca127a43d5cd20260cdcb11f790a148ca Mon Sep 17 00:00:00 2001
From: "Jonathan Gonzalez V." <jonathan.abdiel@gmail.com>
Date: Sun, 5 Oct 2025 18:11:56 +0200
Subject: [PATCH v3 1/1] Introduce a new function pg_get_publication_ddl() that
returns the CREATE ddl statement for a given PUBLICATION.
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
The function accepts a publication name or OID indistinctively,
providing an easy way to call the function as a user from
inside the Postgres code, or any SQL statement.
Comprehensive regression tests are included covering various
possible situations for a given publication.
Reviewed-by: Hüseyin Demir <huseyin.d3r@gmail.com>
Reviewed-by: Cary Huang <cary.huang@highgo.ca>
Reviewed-by: Peter Smith <smithpb2250@gmail.com>
Reviewed-by: Man Zeng <zengman@halodbtech.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Solai v <solai.cdac@gmail.com>
Signed-off-by: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
---
doc/src/sgml/func/func-info.sgml | 32 +
src/backend/utils/adt/ddlutils.c | 381 ++++++++-
src/include/catalog/pg_proc.dat | 16 +
src/test/regress/expected/publication_ddl.out | 747 ++++++++++++++++++
src/test/regress/parallel_schedule | 5 +-
src/test/regress/sql/publication_ddl.sql | 239 ++++++
6 files changed, 1418 insertions(+), 2 deletions(-)
create mode 100644 src/test/regress/expected/publication_ddl.out
create mode 100644 src/test/regress/sql/publication_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index 00f64f50ceb..9d570b50822 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3961,6 +3961,38 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres}
<literal>TABLESPACE</literal>.
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_get_publication_ddl</primary>
+ </indexterm>
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>oid</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para role="func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
+ <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+ <type>text</type> </optional> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Reconstructs the <link linkend="sql-createpublication"><command>CREATE PUBLICATION</command></link> statement for
+ the specified publication (by OID or name), followed by an
+ <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
+ <command>CREATE PUBLICATION</command> grammar has no
+ <literal>OWNER</literal> clause). Each statement is returned as a
+ separate row. An error is raised if no publication with the supplied
+ OID or name exists.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output and
+ <literal>owner</literal> (boolean) to include
+ <literal>OWNER</literal>.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index f32fcd453ef..2994f7a21f2 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -20,12 +20,14 @@
#include "access/genam.h"
#include "access/htup_details.h"
+#include "access/relation.h"
#include "access/table.h"
#include "catalog/pg_auth_members.h"
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_database.h"
#include "catalog/pg_db_role_setting.h"
+#include "catalog/pg_publication_rel.h"
#include "catalog/pg_tablespace.h"
#include "commands/tablespace.h"
#include "common/relpath.h"
@@ -86,7 +88,10 @@ static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner
static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
bool no_owner, bool no_tablespace);
-
+static Datum pg_get_publication_ddl_srf(FunctionCallInfo fcinfo,
+ Oid puboid, bool isnull);
+static List *pg_get_publication_ddl_internal(Oid puboid, bool pretty,
+ bool no_owner);
/*
* parse_ddl_options
@@ -1185,3 +1190,377 @@ pg_get_database_ddl(PG_FUNCTION_ARGS)
SRF_RETURN_DONE(funcctx);
}
}
+
+/*
+ * pg_get_publication_ddl_srf - common SRF logic for publication DDL
+ */
+static Datum
+pg_get_publication_ddl_srf(FunctionCallInfo fcinfo, Oid puboid, bool isnull)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ DdlOption opts[] = {
+ {"pretty", DDL_OPT_BOOL},
+ {"owner", DDL_OPT_BOOL},
+ };
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ if (isnull)
+ {
+ MemoryContextSwitchTo(oldcontext);
+ SRF_RETURN_DONE(funcctx);
+ }
+
+ parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+ statements = pg_get_publication_ddl_internal(puboid,
+ opts[0].isset && opts[0].boolval,
+ opts[1].isset && !opts[1].boolval);
+
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ stmt = (char *) list_nth(statements, funcctx->call_cntr);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
+/*
+ * pg_get_publication_ddl_oid
+ * Return DDL to recreate a publication, taking OID.
+ */
+Datum
+pg_get_publication_ddl_oid(PG_FUNCTION_ARGS)
+{
+ Oid puboid = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ puboid = PG_GETARG_OID(0);
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_name
+ * Return DDL to recreate a publication, taking name.
+ */
+Datum
+pg_get_publication_ddl_name(PG_FUNCTION_ARGS)
+{
+ Oid puboid = InvalidOid;
+ bool isnull;
+
+ isnull = PG_ARGISNULL(0);
+ if (!isnull)
+ {
+ char *pubname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+ puboid = get_publication_oid(pubname, false);
+ pfree(pubname);
+ }
+
+ return pg_get_publication_ddl_srf(fcinfo, puboid, isnull);
+}
+
+/*
+ * pg_get_publication_ddl_internal
+ * Common code for pg_get_publication_ddl_oid and
+ * pg_get_publication_ddl_name.
+ *
+ * Returns a List of palloc'd strings. The first element is the
+ * CREATE PUBLICATION statement; if no_owner is false a second element
+ * carries an ALTER PUBLICATION ... OWNER TO statement (the CREATE
+ * PUBLICATION grammar has no OWNER clause, so ownership must be applied
+ * as a follow-on statement).
+ */
+static List *
+pg_get_publication_ddl_internal(Oid puboid, bool pretty, bool no_owner)
+{
+ Publication *pub;
+ StringInfoData buf;
+ List *statements = NIL;
+ List *pub_incl_relids = NIL;
+ List *pub_excl_relids = NIL;
+ List *pub_schemas = NIL;
+ bool first_perm = true;
+
+ if (!SearchSysCacheExists1(PUBLICATIONOID, ObjectIdGetDatum(puboid)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_OBJECT),
+ errmsg("publication with OID %u does not exist", puboid)));
+
+ pub = GetPublication(puboid);
+
+ initStringInfo(&buf);
+
+ appendStringInfo(&buf, "CREATE PUBLICATION %s", quote_identifier(pub->name));
+
+ /*
+ * Having all tables or all sequences means that there are no per-table
+ * publications
+ */
+ if (pub->alltables || pub->allsequences)
+ {
+ append_ddl_option(&buf, pretty, 4, "FOR ");
+
+ if (pub->alltables)
+ {
+ appendStringInfoString(&buf, "ALL TABLES");
+ pub_excl_relids = GetExcludedPublicationTables(
+ pub->oid, PUBLICATION_PART_ROOT);
+
+ if (pub_excl_relids != NIL)
+ {
+ ListCell *excl_cell;
+ char *schemaname = NULL;
+
+ appendStringInfoString(&buf, " EXCEPT (TABLE ");
+
+ foreach(excl_cell, pub_excl_relids)
+ {
+ HeapTuple tp = SearchSysCache1(RELOID, ObjectIdGetDatum(excl_cell->oid_value));
+ Form_pg_class reltup;
+
+ if (!HeapTupleIsValid(tp))
+ elog(ERROR, "cache lookup failed for relation %u", excl_cell->oid_value);
+
+ reltup = (Form_pg_class) GETSTRUCT(tp);
+ schemaname = get_namespace_name(reltup->relnamespace);
+
+ appendStringInfo(&buf, "%s%s",
+ foreach_current_index(excl_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, NameStr(reltup->relname)));
+
+ pfree(schemaname);
+ ReleaseSysCache(tp);
+ }
+
+ appendStringInfoChar(&buf, ')');
+ }
+ }
+ if (pub->allsequences)
+ appendStringInfo(&buf,
+ "%sALL SEQUENCES",
+ pub->alltables ? ", " : "");
+ }
+ else
+ {
+ pub_incl_relids = GetIncludedPublicationRelations(pub->oid, PUBLICATION_PART_ROOT);
+ pub_schemas = GetPublicationSchemas(pub->oid);
+ }
+
+ /*
+ * Publication can have table relations
+ */
+ if (pub_incl_relids != NIL)
+ {
+ ListCell *pub_cell;
+ char *schemaname = NULL;
+ char *tablename;
+
+ append_ddl_option(&buf, pretty, 4, "FOR TABLE ");
+
+ foreach(pub_cell, pub_incl_relids)
+ {
+ HeapTuple pubtuple = NULL;
+ HeapTuple reltup;
+ Form_pg_class relform;
+ Datum columns,
+ rowfilter;
+ Oid relid = pub_cell->oid_value;
+ bool cols_nulls,
+ condition_nulls;
+
+ reltup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(reltup))
+ elog(ERROR,
+ "cache lookup failed for relation %u",
+ relid);
+
+ relform = (Form_pg_class) GETSTRUCT(reltup);
+ tablename = NameStr(relform->relname);
+ schemaname = get_namespace_name(relform->relnamespace);
+
+ appendStringInfo(&buf, "%s%s",
+ foreach_current_index(pub_cell) > 0 ? ", " : "",
+ quote_qualified_identifier(schemaname, tablename));
+
+ pfree(schemaname);
+
+ pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
+ ObjectIdGetDatum(pub->oid));
+
+ if (!HeapTupleIsValid(pubtuple))
+ elog(ERROR,
+ "cache lookup failed for relation %u in publication %u",
+ relid, pub->oid);
+
+ columns = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prattrs,
+ &cols_nulls);
+
+ rowfilter = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
+ Anum_pg_publication_rel_prqual,
+ &condition_nulls);
+
+ /* If non-null, we have a list of columns to publish */
+ if (!cols_nulls)
+ {
+ Bitmapset *attmap;
+ int attnum = -1;
+
+ attmap = pub_collist_to_bitmapset(NULL, columns, NULL);
+
+ appendStringInfoChar(&buf, '(');
+ while ((attnum = bms_next_member(attmap, attnum)) >= 0)
+ {
+ appendStringInfo(&buf, "%s%s",
+ bms_member_index(attmap, attnum) ? ", " : "",
+ quote_identifier(get_attname(relid, attnum, false)));
+ }
+ appendStringInfoChar(&buf, ')');
+
+ bms_free(attmap);
+ }
+
+ /*
+ * If there is a condition it goes after the columns. We can have
+ * conditions without columns as well.
+ */
+ if (!condition_nulls)
+ {
+ Node *node;
+ List *context;
+ char *str;
+
+ node = stringToNode(TextDatumGetCString(rowfilter));
+ context = deparse_context_for(tablename, relid);
+ str = deparse_expression(node, context, false, false);
+ appendStringInfo(&buf, " WHERE (%s)", str);
+ }
+
+ ReleaseSysCache(pubtuple);
+ ReleaseSysCache(reltup);
+ }
+ }
+
+ /* If we have schemas, they will go right before the EXCEPT and/or WITH */
+ if (pub_schemas != NIL)
+ {
+ ListCell *schema_cell;
+
+ /*
+ * Schemas can be preceded by a list of tables. When they are, the
+ * "TABLES IN SCHEMA" stays inline as a continuation of the existing
+ * FOR clause; otherwise it starts the FOR clause on its own line in
+ * pretty mode.
+ */
+ if (pub_incl_relids == NIL)
+ append_ddl_option(&buf, pretty, 4, "FOR TABLES IN SCHEMA");
+ else
+ appendStringInfoString(&buf, ", TABLES IN SCHEMA");
+
+ foreach(schema_cell, pub_schemas)
+ {
+ char *nspname = get_namespace_name(schema_cell->oid_value);
+
+ appendStringInfo(&buf, "%s %s",
+ foreach_current_index(schema_cell) > 0 ? "," : "",
+ quote_identifier(nspname));
+ pfree(nspname);
+ }
+ }
+
+ /* Always add the WITH options */
+ append_ddl_option(&buf, pretty, 4, "WITH (");
+
+ /* Publish string */
+ appendStringInfoString(&buf, "publish='");
+
+
+ if (pub->pubactions.pubinsert)
+ {
+ /*
+ * By precedence we know that the insert will always be first, no need
+ * to check previous values
+ */
+ appendStringInfoString(&buf, "insert");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubupdate)
+ {
+ appendStringInfo(&buf, "%supdate", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubdelete)
+ {
+ appendStringInfo(&buf, "%sdelete", first_perm ? "" : ", ");
+ first_perm = false;
+ }
+ if (pub->pubactions.pubtruncate)
+ {
+ appendStringInfo(&buf, "%struncate", first_perm ? "" : ", ");
+ }
+
+ appendStringInfoString(&buf, "', ");
+
+ /* publish_generated_columns string */
+ appendStringInfo(&buf, "publish_generated_columns=%s, ",
+ pub->pubgencols_type == PUBLISH_GENCOLS_NONE ? "none" : "stored");
+
+ /* publish_via_partition_root value */
+ appendStringInfo(&buf, "publish_via_partition_root=%s)",
+ pub->pubviaroot ? "true" : "false");
+
+ appendStringInfoChar(&buf, ';');
+ statements = lappend(statements, pstrdup(buf.data));
+ pfree(buf.data);
+
+ /* OWNER */
+ if (!no_owner)
+ {
+ HeapTuple tup;
+ Form_pg_publication pubform;
+ char *owner;
+
+ tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
+ if (!HeapTupleIsValid(tup))
+ elog(ERROR, "cache lookup failed for publication %u", puboid);
+ pubform = (Form_pg_publication) GETSTRUCT(tup);
+ owner = GetUserNameFromId(pubform->pubowner, false);
+ ReleaseSysCache(tup);
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER PUBLICATION %s OWNER TO %s;",
+ quote_identifier(pub->name), quote_identifier(owner));
+ statements = lappend(statements, pstrdup(buf.data));
+ pfree(buf.data);
+ pfree(owner);
+ }
+
+ return statements;
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index be157a5fbe9..2579b530e28 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12317,6 +12317,22 @@
proname => 'pg_relation_is_publishable', provolatile => 's',
prorettype => 'bool', proargtypes => 'regclass',
prosrc => 'pg_relation_is_publishable' },
+{ oid => '6122', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'oid text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{oid,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_oid' },
+{ oid => '6123', descr => 'get DDL to recreate a publication',
+ proname => 'pg_get_publication_ddl', provariadic => 'text', proisstrict => 'f',
+ provolatile => 's', proretset => 't', prorows => '2', prorettype => 'text',
+ proargtypes => 'text text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{text,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_name' },
# rls
{ oid => '3298',
diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regress/expected/publication_ddl.out
new file mode 100644
index 00000000000..4e76624ae39
--- /dev/null
+++ b/src/test/regress/expected/publication_ddl.out
@@ -0,0 +1,747 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+ERROR: publication "non-existing" does not exist
+SELECT pg_get_publication_ddl(0::oid);
+ERROR: publication with OID 0 does not exist
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+ is_null
+---------
+ t
+(1 row)
+
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+ is_null
+---------
+ t
+(1 row)
+
+-- create set of tables for publications
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_2 +
+ FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 +
+ WITH (publish='update, delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_3 +
+ FOR TABLE public.testpub_ddl_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar = baz)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar = baz)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_4 +
+ FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar = baz)) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_5 +
+ FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE)) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_6 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_7 +
+ FOR ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_8 +
+ FOR ALL TABLES, ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_9 +
+ FOR ALL TABLES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create a publication with a bare bolean in the row filter
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl4 WHERE (beque);
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_10 +
+ FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+-- create a publication for a list of tables and schema
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_1'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_1 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_2'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_2 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_3'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_3 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_4'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_4 +
+ FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_5'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_schema_5 +
+ FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES FROM (10) TO (20);
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part1'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part1 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+ pg_get_publication_ddl
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part2 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part3'));
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part3 +
+ FOR TABLE public.testpub_ddl_part +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=true);
+ ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part4'));
+ pg_get_publication_ddl
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_part4 +
+ FOR TABLE public.testpub_ddl_part_p1 +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('testpub Quoted Pub');
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub Quoted Pub'));
+ pg_get_publication_ddl
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub Quoted Pub', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION "testpub Quoted Pub" +
+ FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("Col One" > 0)) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+ pg_get_publication_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except1 +
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- create publication for all sequences and all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except2'));
+ pg_get_publication_ddl
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+ pg_get_publication_ddl
+--------------------------------------------------------------------------------------------------------------------------
+ CREATE PUBLICATION testpub_ddl_except2 +
+ FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES +
+ WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+(2 rows)
+
+-- get all the created publications DDL into the table
+CREATE TEMP TABLE pub_ddl AS
+SELECT p.pubname, t.n, t.stmt
+FROM pg_publication p,
+LATERAL pg_get_publication_ddl(p.pubname) WITH ORDINALITY AS t(stmt, n)
+WHERE p.pubname LIKE 'testpub%';
+-- drop the publications to be recreated
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+DROP PUBLICATION "testpub Quoted Pub"
+DROP PUBLICATION testpub_ddl_1
+DROP PUBLICATION testpub_ddl_10
+DROP PUBLICATION testpub_ddl_2
+DROP PUBLICATION testpub_ddl_3
+DROP PUBLICATION testpub_ddl_4
+DROP PUBLICATION testpub_ddl_5
+DROP PUBLICATION testpub_ddl_6
+DROP PUBLICATION testpub_ddl_7
+DROP PUBLICATION testpub_ddl_8
+DROP PUBLICATION testpub_ddl_9
+DROP PUBLICATION testpub_ddl_except1
+DROP PUBLICATION testpub_ddl_except2
+DROP PUBLICATION testpub_ddl_part1
+DROP PUBLICATION testpub_ddl_part2
+DROP PUBLICATION testpub_ddl_part3
+DROP PUBLICATION testpub_ddl_part4
+DROP PUBLICATION testpub_ddl_schema_1
+DROP PUBLICATION testpub_ddl_schema_2
+DROP PUBLICATION testpub_ddl_schema_3
+DROP PUBLICATION testpub_ddl_schema_4
+DROP PUBLICATION testpub_ddl_schema_5
+-- recreate all the publications using the ddl from pg_get_publication_ddl()
+SELECT stmt FROM pub_ddl ORDER BY pubname, n \gexec
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish='update, delete', publish_generated_columns=stored, publish_via_partition_root=true);
+ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar = baz)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE)) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=true);
+ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_user;
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 WITH (publish='insert, update, delete, truncate', publish_generated_columns=none, publish_via_partition_root=false);
+ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_user;
+-- cleanup publications
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+DROP PUBLICATION "testpub Quoted Pub"
+DROP PUBLICATION testpub_ddl_1
+DROP PUBLICATION testpub_ddl_10
+DROP PUBLICATION testpub_ddl_2
+DROP PUBLICATION testpub_ddl_3
+DROP PUBLICATION testpub_ddl_4
+DROP PUBLICATION testpub_ddl_5
+DROP PUBLICATION testpub_ddl_6
+DROP PUBLICATION testpub_ddl_7
+DROP PUBLICATION testpub_ddl_8
+DROP PUBLICATION testpub_ddl_9
+DROP PUBLICATION testpub_ddl_except1
+DROP PUBLICATION testpub_ddl_except2
+DROP PUBLICATION testpub_ddl_part1
+DROP PUBLICATION testpub_ddl_part2
+DROP PUBLICATION testpub_ddl_part3
+DROP PUBLICATION testpub_ddl_part4
+DROP PUBLICATION testpub_ddl_schema_1
+DROP PUBLICATION testpub_ddl_schema_2
+DROP PUBLICATION testpub_ddl_schema_3
+DROP PUBLICATION testpub_ddl_schema_4
+DROP PUBLICATION testpub_ddl_schema_5
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+DROP TABLE pub_ddl;
+--- cleanup tables for schema tests
+DROP TABLE schema_tbl1;
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl CASCADE;
+DROP SCHEMA pub_schema_test_ddl_2 CASCADE;
+DROP SCHEMA "Pub Schema";
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+RESET client_min_messages;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8fa0a6c47fb..3d3a6c57af5 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -135,7 +135,6 @@ test: compression compression_lz4 compression_pglz cluster
# oidjoins is read-only, though, and should run late for best coverage
test: oidjoins event_trigger
-
# event_trigger_login cannot run concurrently with any other tests because
# on-login event handling could catch connection of a concurrent test.
test: event_trigger_login
@@ -143,6 +142,10 @@ test: event_trigger_login
# this test also uses event triggers, so likewise run it by itself
test: fast_default
+# run retail DDL tests last to avoid object name collisions and
+# interference with previous tests.
+test: publication_ddl
+
# run tablespace test at the end because it drops the tablespace created during
# setup that other tests may use.
test: tablespace
diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sql/publication_ddl.sql
new file mode 100644
index 00000000000..3543447e2c5
--- /dev/null
+++ b/src/test/regress/sql/publication_ddl.sql
@@ -0,0 +1,239 @@
+--
+-- Test for DDL statement from:
+-- - pg_get_publication_ddl
+--
+
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+
+-- Run the body under a stable role so the ALTER PUBLICATION ... OWNER TO
+-- output is deterministic across environments.
+CREATE ROLE regress_publication_ddl_user LOGIN SUPERUSER;
+SET SESSION AUTHORIZATION 'regress_publication_ddl_user';
+
+-- test with a non-existing publication
+SELECT pg_get_publication_ddl('non-existing');
+SELECT pg_get_publication_ddl(0::oid);
+
+-- empty publication is possible and allowed
+CREATE PUBLICATION testpub_ddl_1;
+SELECT pg_get_publication_ddl('testpub_ddl_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
+
+-- NULL input should produce an empty result set
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::oid);
+SELECT count(*) = 0 AS is_null FROM pg_get_publication_ddl(NULL::text);
+
+-- create set of tables for publications
+CREATE TABLE testpub_ddl_tbl1 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl2 (foo int, bar int);
+CREATE TABLE testpub_ddl_tbl3 (foo int, bar int, beque int, baz int);
+CREATE TABLE testpub_ddl_tbl4 (foo int, bar int, beque bool);
+CREATE TABLE testpub_ddl_tbl5 (foo int, "bar beque" int);
+
+CREATE PUBLICATION testpub_ddl_2 FOR TABLE testpub_ddl_tbl1, testpub_ddl_tbl2, testpub_ddl_tbl3 WITH (publish='delete', publish_generated_columns='stored', publish_via_partition_root='true');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+ALTER PUBLICATION testpub_ddl_2 SET (publish = 'delete, update');
+
+SELECT pg_get_publication_ddl('testpub_ddl_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true');
+
+-- create publication for one table
+CREATE PUBLICATION testpub_ddl_3 FOR TABLE ONLY testpub_ddl_tbl1;
+
+SELECT pg_get_publication_ddl('testpub_ddl_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_3', 'pretty', 'true');
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3 (bar,baz) WHERE (bar = baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4 (bar,beque) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+
+-- create publication for all tables
+CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_6');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_6'));
+SELECT pg_get_publication_ddl('testpub_ddl_6', 'pretty', 'true');
+
+-- create publication for all sequences
+CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES;
+SELECT pg_get_publication_ddl('testpub_ddl_7');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_7'));
+SELECT pg_get_publication_ddl('testpub_ddl_7', 'pretty', 'true');
+
+-- create publication for all tables and all sequences
+CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES;
+
+SELECT pg_get_publication_ddl('testpub_ddl_8');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_8'));
+SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true');
+
+-- explicit publish_generated_columns='none'
+CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_columns='none');
+SELECT pg_get_publication_ddl('testpub_ddl_9');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_9'));
+SELECT pg_get_publication_ddl('testpub_ddl_9', 'pretty', 'true');
+
+-- create a publication with a bare bolean in the row filter
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl4 WHERE (beque);
+SELECT pg_get_publication_ddl('testpub_ddl_10');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_10'));
+SELECT pg_get_publication_ddl('testpub_ddl_10', 'pretty', 'true');
+
+-- create schema for schema publication
+CREATE SCHEMA pub_schema_test_ddl;
+CREATE TABLE pub_schema_test_ddl.schema_tbl1 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl2 (foo int, bar int);
+CREATE TABLE pub_schema_test_ddl.schema_tbl3 (foo int, bar int, baz int);
+
+-- create a publication for a list of tables and schema
+CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.schema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_1'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true');
+
+-- create publication in schema only for table
+CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_test_ddl, TABLE pub_schema_test_ddl.schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_2'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true');
+
+-- create publication for all tables in schema
+CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_test_ddl;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_3'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true');
+
+-- a new schema for multiple schemas
+CREATE SCHEMA pub_schema_test_ddl_2;
+CREATE TABLE pub_schema_test_ddl_2.schema_tbl1 (foo int, bar int);
+
+-- create a publication for a list of schemas
+CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true');
+
+-- create a publication for a specific schema and a table in public schema
+-- both with the same name
+CREATE TABLE schema_tbl1 (foo int, bar int);
+CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.schema_tbl1, schema_tbl1;
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_schema_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true');
+
+-- create tables for partition test
+CREATE TABLE testpub_ddl_part (foo int, bar int) PARTITION BY RANGE (foo);
+CREATE TABLE testpub_ddl_part_p1 PARTITION OF testpub_ddl_part FOR VALUES FROM (0) TO (10);
+CREATE TABLE testpub_ddl_part_p2 PARTITION OF testpub_ddl_part FOR VALUES FROM (10) TO (20);
+
+CREATE PUBLICATION testpub_ddl_part1 FOR TABLE testpub_ddl_part;
+SELECT pg_get_publication_ddl('testpub_ddl_part1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part1'));
+SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='false');
+SELECT pg_get_publication_ddl('testpub_ddl_part2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part2'));
+SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publish_via_partition_root='true');
+SELECT pg_get_publication_ddl('testpub_ddl_part3');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part3'));
+SELECT pg_get_publication_ddl('testpub_ddl_part3', 'pretty', 'true');
+
+CREATE PUBLICATION testpub_ddl_part4 FOR TABLE testpub_ddl_part_p1;
+SELECT pg_get_publication_ddl('testpub_ddl_part4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_part4'));
+SELECT pg_get_publication_ddl('testpub_ddl_part4', 'pretty', 'true');
+
+-- identifiers that require quoting: publication, schema, table and column
+CREATE SCHEMA "Pub Schema";
+CREATE TABLE "Pub Schema"."Quoted Table" ("Col One" int, "select" int);
+CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Table" ("Col One", "select") WHERE ("Col One" > 0);
+SELECT pg_get_publication_ddl('testpub Quoted Pub');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub Quoted Pub'));
+SELECT pg_get_publication_ddl('testpub Quoted Pub', 'pretty', 'true');
+
+-- tables for EXCEPT
+CREATE TABLE testpub_ddl_except1 (foo int, bar int);
+CREATE TABLE testpub_ddl_except2 (foo int, bar int);
+
+-- create publication for all tables except one
+CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE testpub_ddl_except1);
+SELECT pg_get_publication_ddl('testpub_ddl_except1');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except1'));
+SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true');
+
+-- create publication for all sequences and all tables except two tables
+CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEPT (TABLE testpub_ddl_except1, testpub_ddl_except2);
+SELECT pg_get_publication_ddl('testpub_ddl_except2');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubname='testpub_ddl_except2'));
+SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true');
+
+-- get all the created publications DDL into the table
+CREATE TEMP TABLE pub_ddl AS
+SELECT p.pubname, t.n, t.stmt
+FROM pg_publication p,
+LATERAL pg_get_publication_ddl(p.pubname) WITH ORDINALITY AS t(stmt, n)
+WHERE p.pubname LIKE 'testpub%';
+
+-- drop the publications to be recreated
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+
+-- recreate all the publications using the ddl from pg_get_publication_ddl()
+SELECT stmt FROM pub_ddl ORDER BY pubname, n \gexec
+
+-- cleanup publications
+SELECT format('DROP PUBLICATION %I', pubname)
+FROM (SELECT DISTINCT pubname FROM pub_ddl) ORDER BY pubname \gexec
+
+-- cleanup tables
+DROP TABLE testpub_ddl_tbl1;
+DROP TABLE testpub_ddl_tbl2;
+DROP TABLE testpub_ddl_tbl3;
+DROP TABLE testpub_ddl_tbl4;
+DROP TABLE testpub_ddl_tbl5;
+DROP TABLE pub_ddl;
+
+--- cleanup tables for schema tests
+DROP TABLE schema_tbl1;
+
+-- cleanup tables for partitions
+DROP TABLE testpub_ddl_part;
+
+-- cleanup tables for quoted names
+DROP TABLE "Pub Schema"."Quoted Table";
+
+-- cleanup tables for except
+DROP TABLE testpub_ddl_except1;
+DROP TABLE testpub_ddl_except2;
+
+-- cleanup schemas
+DROP SCHEMA pub_schema_test_ddl CASCADE;
+DROP SCHEMA pub_schema_test_ddl_2 CASCADE;
+DROP SCHEMA "Pub Schema";
+
+-- cleanup role
+RESET SESSION AUTHORIZATION;
+DROP ROLE regress_publication_ddl_user;
+
+RESET client_min_messages;
--
2.53.0
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 21:06 Álvaro Herrera <alvherre@kurilemu.de>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: Álvaro Herrera @ 2026-06-09 21:06 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi,
On 2026-Jun-09, Jonathan Gonzalez V. wrote:
> I'm attaching the v3 of this patch. I've added a new set of regress test
> that execute the query generated by pg_get_publication_ddl(), this
> helped to find a couple of issues with EXCEPT and WHERE.
That's good; however ...
> doc/src/sgml/func/func-info.sgml | 32 +
> src/backend/utils/adt/ddlutils.c | 381 ++++++++-
> src/include/catalog/pg_proc.dat | 16 +
> src/test/regress/expected/publication_ddl.out | 747 ++++++++++++++++++
> src/test/regress/parallel_schedule | 5 +-
> src/test/regress/sql/publication_ddl.sql | 239 ++++++
I think this patch file is a bit too large, and the problem might be
that you added too many tests. I would suggest to trim them down,
keeping an eye on the coverage report so that you don't lose anything
important. You don't need to have the function dump every single
publication both by OID and by name, for instance. Just test that it
works by OID once, and then the rest of the tests (just 3 or 4 to test
the features that you care about, not dozens of them) can do it by name
only.
> +static List *
> +pg_get_publication_ddl_internal(Oid puboid, bool pretty, bool no_owner)
> +{
> + Publication *pub;
> + StringInfoData buf;
> + List *statements = NIL;
> + List *pub_incl_relids = NIL;
> + List *pub_excl_relids = NIL;
> + List *pub_schemas = NIL;
> + bool first_perm = true;
I think initializing this first_perm variable so far from the place it's
used is error-prone. I would remove the initialization here and move it
down, just before pubactions.pubinsert is tested. Also, the name makes
no sense considering what it's used for.
> + if (pub->alltables)
> + {
> + appendStringInfoString(&buf, "ALL TABLES");
> + pub_excl_relids = GetExcludedPublicationTables(
> + pub->oid, PUBLICATION_PART_ROOT);
Newline after parenthesis looks ugly.
> + if (pub_excl_relids != NIL)
> + {
> + ListCell *excl_cell;
> + char *schemaname = NULL;
> +
> + appendStringInfoString(&buf, " EXCEPT (TABLE ");
> +
> + foreach(excl_cell, pub_excl_relids)
> + {
> + HeapTuple tp = SearchSysCache1(RELOID, ObjectIdGetDatum(excl_cell->oid_value));
> + Form_pg_class reltup;
> +
> + if (!HeapTupleIsValid(tp))
> + elog(ERROR, "cache lookup failed for relation %u", excl_cell->oid_value);
> +
> + reltup = (Form_pg_class) GETSTRUCT(tp);
> + schemaname = get_namespace_name(reltup->relnamespace);
> +
> + appendStringInfo(&buf, "%s%s",
> + foreach_current_index(excl_cell) > 0 ? ", " : "",
> + quote_qualified_identifier(schemaname, NameStr(reltup->relname)));
I wonder if we'll regret this syntax for the EXCEPT clause. (Unrelated
to your patch.)
> + pfree(schemaname);
> + ReleaseSysCache(tp);
> + }
There's a lot of pfreeing going on all over this code, but I'm not sure
it's good style. There are also lots of leaked allocations, and
randomly freeing some of them but not others is distracting. I think
you could probably remove all the pfreeing, and just rely on the memory
context from the caller releasing the memory at once when the function
is finished.
> + /*
> + * Publication can have table relations
> + */
> + if (pub_incl_relids != NIL)
> + {
I don't think this style of comment is very useful. I would write
something like "if there are table relations in this publication,
produce a FOR TABLE clause to list them" or something like that. I
mean, your comment is stating some fact (you could as well say "the sun
is hot as is my babe" or whatever), rather than what are you going to do
about it. In the code comments we care more about what you do and why,
rather than truism or simple fact.
> + columns = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
> + Anum_pg_publication_rel_prattrs,
> + &cols_nulls);
> +
> + rowfilter = SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple,
> + Anum_pg_publication_rel_prqual,
> + &condition_nulls);
> + /* If non-null, we have a list of columns to publish */
> + if (!cols_nulls)
> + {
Here it looks like you have two variables just for the heck of it. I
mean, you could do simply "datum = SysCacheGetAttr(..., prattrs, &isnull)" and
immediately after do "if (!isnull) then add the colum list clause", and
afterwards do the syscache lookup for prqual and "if (!isnull) then add
the WHERE clause"; the code is more straightforward that way and you
avoid additional variables with names that are pointlessly specific.
> + /* If we have schemas, they will go right before the EXCEPT and/or WITH */
> + if (pub_schemas != NIL)
> + {
> + ListCell *schema_cell;
> +
> + /*
> + * Schemas can be preceded by a list of tables. When they are, the
> + * "TABLES IN SCHEMA" stays inline as a continuation of the existing
> + * FOR clause; otherwise it starts the FOR clause on its own line in
> + * pretty mode.
> + */
> + if (pub_incl_relids == NIL)
> + append_ddl_option(&buf, pretty, 4, "FOR TABLES IN SCHEMA");
> + else
> + appendStringInfoString(&buf, ", TABLES IN SCHEMA");
I think it's weird to mix the concern of pretty mode with the concern of
whether there are tables or not. I think this should just do
append_ddl_option, and if it results in one newline too many, then
that's too bad.
> + appendStringInfoChar(&buf, ';');
> + statements = lappend(statements, pstrdup(buf.data));
> + pfree(buf.data);
> +
> + /* OWNER */
> + if (!no_owner)
> + {
> + HeapTuple tup;
> + Form_pg_publication pubform;
> + char *owner;
> +
> + tup = SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid));
> + if (!HeapTupleIsValid(tup))
> + elog(ERROR, "cache lookup failed for publication %u", puboid);
> + pubform = (Form_pg_publication) GETSTRUCT(tup);
> + owner = GetUserNameFromId(pubform->pubowner, false);
> + ReleaseSysCache(tup);
> +
> + initStringInfo(&buf);
It's ugly to do pfree(buf.data) and immediately follow with
initStringInfo(&buf). I would remove the pfree() and use
resetStringInfo() instead. Also, the comment here is not very nice.
Why do a SearchSysCacheExists1() at the top of the function, and later
do a real syscache fetch at the bottom? I think it would be smarter to
do the SearchSysCache1() at the top and keep the tuple until you need it
at the bottom instead. Which also suggests to me that doing
GetPublication (which does its own SearchSysCache!) is kinda lame, since
it doesn't do anything special. You could just do the
SearchSysCache1(), then process everything directly from the
Form_pg_publication struct without going through the Publication
abstraction (which, given what it does, which is to say nothing at all,
looks a pretty embarrasing one to me. We should fix that sometime, maybe.)
> + appendStringInfo(&buf, "ALTER PUBLICATION %s OWNER TO %s;",
> + quote_identifier(pub->name), quote_identifier(owner));
> + statements = lappend(statements, pstrdup(buf.data));
> + pfree(buf.data);
> + pfree(owner);
Pointless pfrees again.
> @@ -143,6 +142,10 @@ test: event_trigger_login
> # this test also uses event triggers, so likewise run it by itself
> test: fast_default
>
> +# run retail DDL tests last to avoid object name collisions and
> +# interference with previous tests.
> +test: publication_ddl
Hmm, see commit c529ee38b9eb.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
"Entristecido, Wutra (canción de Las Barreras)
echa a Freyr a rodar
y a nosotros al mar"
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-09 22:06 Zsolt Parragi <zsolt.parragi@percona.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: Zsolt Parragi @ 2026-06-09 22:06 UTC (permalink / raw)
To: pgsql-hackers@lists.postgresql.org
Hello!
This generates an invalid statement:
CREATE TABLE rt_ex1 (a int, b int);
CREATE TABLE rt_ex2 (a int, b int);
CREATE PUBLICATION rt_combo FOR ALL SEQUENCES, ALL TABLES EXCEPT
(TABLE rt_ex1, rt_ex2);
SELECT pg_get_publication_ddl('rt_combo');
It switches the tables/sequences order in the output: ".. FOR ALL
TABLES, ALL SEQUENCES EXCEPT .."
And if I only specify "FOR ALL SEQUENCES", it still emits WITH options
that causes a notice. The query succeeds, but maybe this could be
improved?
CREATE PUBLICATION rt_allseq FOR ALL SEQUENCES;
SELECT pg_get_publication_ddl('rt_allseq');
Outputs:
CREATE PUBLICATION rt_allseq FOR ALL SEQUENCES WITH (publish='insert,
update, delete, truncate', publish_generated_columns='none',
publish_via_partition_root='false');
-- NOTICE: publication parameters are not applicable to sequence
synchronization and will be ignored for sequences
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-11 00:04 Peter Smith <smithpb2250@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
1 sibling, 0 replies; 22+ messages in thread
From: Peter Smith @ 2026-06-11 00:04 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Some review comments for patch v3-0001.
======
doc/src/sgml/func/func-info.sgml
1.
+ <para role="func_signature">
+ <function>pg_get_publication_ddl</function>
+ ( <parameter>publication</parameter> <type>text</type>
Why is the first parameter type 'text' instead of 'name' like
pg_get_tablespace_ddl was?
~~~
2.
+ <para>
+ Reconstructs the <link
linkend="sql-createpublication"><command>CREATE
PUBLICATION</command></link> statement for
+ the specified publication (by OID or name), followed by an
+ <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
+ <command>CREATE PUBLICATION</command> grammar has no
+ <literal>OWNER</literal> clause). Each statement is returned as a
+ separate row. An error is raised if no publication with the supplied
+ OID or name exists.
+ The following options are supported:
+ <literal>pretty</literal> (boolean) for formatted output and
+ <literal>owner</literal> (boolean) to include
+ <literal>OWNER</literal>.
+ </para></entry>
Thanks for adding the link to CREATE. Consider doing the same for the
ALTER ... OWNER.
======
src/backend/utils/adt/ddlutils.c
3.
+static List *pg_get_publication_ddl_internal(Oid puboid, bool pretty,
+ bool no_owner);
The `no_owner` parameter is backwards. I suppose it's copying an
existing pattern, but that doesn't make it right. IMO this parameter
ought to have the same positive/negative polarity as per the
documentation.
So, the parameter should be 'owner' or 'include_owner'. It avoids
mental gymnastics to read, and ! operations in the function and
caller.
e.g.
BEFORE
+ /* OWNER */
+ if (!no_owner)
SUGGESTION
/* OWNER */
if (owner)
~~~
pg_get_publication_ddl_internal:
> > 7.
> > + pubtuple = SearchSysCache2(PUBLICATIONRELMAP, ObjectIdGetDatum(relid),
> > + ObjectIdGetDatum(pub->oid));
> > +
> > + if (!HeapTupleIsValid(pubtuple))
> > + elog(ERROR,
> > + "cache lookup failed for publication relation %u in publication %u",
> > + relid, pub->oid);
> >
> > 7a.
> > Maybe blank line here is not wanted.
>
> It gives some space, it was intentional.
REPLY: Fine, but it's not consistent with the 'reltup' immediate prior this.
> > 10.
> > + /*
> > + * If there is a condition it goes after the columns. We can have
> > + * conditions without columns as well.
> > + */
> > + if (!condition_nulls)
> >
> > 10a.
> > The earlier assignment to 'conditions' should be moved to be directly
> > above here.
>
> In this case I think it's better to have both calls to SysCacheGetAttr()
> together since both depends on the previous call of SearchSysCache2()
REPLY: Moving the code also has the advantage that then you can just
use a common variables like `datum` and `isnull` as per Alvaro's
review [1].
> > 10b.
> > BTW, it is called a "row filter" so maybe it is better to refer to
> > that in the comments/vars instead of the generic sounding "condition".
>
> Changed!
REPLY: The accompanying `condition_nulls` was not changed.
~~~
4.
+ if (!condition_nulls)
+ {
+ Node *node;
+ List *context;
+ char *str;
+
+ node = stringToNode(TextDatumGetCString(rowfilter));
+ context = deparse_context_for(tablename, relid);
+ str = deparse_expression(node, context, false, false);
+ appendStringInfo(&buf, " WHERE (%s)", str);
+ }
This is causing double parentheses in the WHERE. See the test output:
+ CREATE PUBLICATION testpub_ddl_4
+
+ FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar = baz))
+
+ WITH (publish='insert, update, delete, truncate',
publish_generated_columns=none, publish_via_partition_root=false);
Can it be fixed to add parentheses only when it's necessary? e.g. I
see some code in ruleutil.c that is checking to add parens or not --
perhaps you need something similar.
Furthermore, I saw ruleutil.c has a 'deparse_expression_pretty'.
Shouldn't we be trying to pass the 'pretty' flag into that? AFAICT the
'deparse_expression' always says pretty is false.
~~~
5.
+ /* If we have schemas, they will go right before the EXCEPT and/or WITH */
In PG19, EXCEPT is only allowed with FOR ALL TABLES. Furthermore, FOR
ALL TABLES and FOR TABLES IN SCHEMA cannot coexist. So, mentioning
EXCEPT in this comment about schemas seems meaningless. Schema won't
go right before an EXCEPT because schemas cannot (yet) coexist with
EXCEPT.
~~~
6.
+ /* Always add the WITH options */
+ append_ddl_option(&buf, pretty, 4, "WITH (");
+
+ /* Publish string */
+ appendStringInfoString(&buf, "publish='");
+
+
+ if (pub->pubactions.pubinsert)
Double spacing?
======
src/include/catalog/pg_proc.dat
7.
+ proargtypes => 'text text',
+ proargmodes => '{i,v}',
+ proallargtypes => '{text,text}',
+ pronargdefaults => '1', proargdefaults => '{NULL}',
+ prosrc => 'pg_get_publication_ddl_name' },
Shouldn't this be 'name text', same as "pg_get_tablespace_ddl_name"?
======
src/test/regress/sql/publication_ddl.sql
8.
According to the documentation the output of ALTER ... OWNER depends
on the 'owner' parameter. AFAICT every test here is passing 'owner' as
true.
e.g
+SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true');
This results in more work and more output than is necessary. Probably,
there only needs to be only 1 test passing owner 'true', and every
other test case can pass it 'false'.
~~~
9.
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_4 FOR TABLE ONLY testpub_ddl_tbl3
(bar,baz) WHERE (bar = baz);
+
+SELECT pg_get_publication_ddl('testpub_ddl_4');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE
pubname='testpub_ddl_4'));
+SELECT pg_get_publication_ddl('testpub_ddl_4', 'pretty', 'true');
+
+-- create publication for one table with two columns and a rowfilter
+CREATE PUBLICATION testpub_ddl_5 FOR TABLE ONLY testpub_ddl_tbl4
(bar,beque) WHERE (beque IS TRUE);
+
+SELECT pg_get_publication_ddl('testpub_ddl_5');
+SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE
pubname='testpub_ddl_5'));
+SELECT pg_get_publication_ddl('testpub_ddl_5', 'pretty', 'true');
+
9a.
There are 2 test case here with exactly the same comment
~
9b.
/rowfilter/row filter/
~~~
10.
+-- create a publication with a bare bolean in the row filter
+CREATE PUBLICATION testpub_ddl_10 FOR TABLE testpub_ddl_tbl4 WHERE (beque);
/bolean/boolean/
======
[1] Alvaro - https://www.postgresql.org/message-id/aihkCoNg3evwHYUj%40alvherre.pgsql
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-11 02:01 Peter Smith <smithpb2250@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
1 sibling, 1 reply; 22+ messages in thread
From: Peter Smith @ 2026-06-11 02:01 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
On Tue, Jun 9, 2026 at 9:15 PM Jonathan Gonzalez V.
<jonathan.abdiel@gmail.com> wrote:
>
> Peter Smith <smithpb2250@gmail.com> writes:
>
...
> > 2.
> > + <para>
> > + Reconstructs the <command>CREATE PUBLICATION</command> statement for
> > + the specified publication (by OID or name), followed by an
> > + <command>ALTER PUBLICATION ... OWNER TO</command> statement (the
> > + <command>CREATE PUBLICATION</command> grammar has no
> > + <literal>OWNER</literal> clause). Each statement is returned as a
> > + separate row. An error is raised if no publication with the supplied
> > + OID or name exists. When the publication was created with
> > + <literal>FOR ALL TABLES, ALL SEQUENCES</literal>, the emitted
> > + statement always lists <literal>ALL TABLES</literal> before
> > + <literal>ALL SEQUENCES</literal> regardless of the original order.
> > + The following options are supported:
> > + <literal>pretty</literal> (boolean) for formatted output and
> > + <literal>owner</literal> (boolean) to include
> > + <literal>OWNER</literal>.
> > + </para></entry>
> >
> > 2a.
> > That "CREATE PUBLICATION" should <link> back to the CREATE PUBLICATION
> > docs page.
>
> This is indeed a good idea, I would love to see this also in the other
> patchs, probably another patch to update all the functions will be good.
> Applied for the next version
>
...
>
> > ~~~
> >
> > GENERAL
> >
> > 3.
> > It would be better if the the rows of "Table 9.96" were in alphabetical order.
>
> I think that this should be done in a different patch when all or a big
> part of the functions are merged.
>
FYI, I've created another thread [1] to address those things above,
======
[1] https://www.postgresql.org/message-id/CAHut+Pun9Z8qZFJTa9fLgdhM=Cip9d-cnx2YXDW6eFrSwbQj1g@mail.gmail...
Kind Regards,
Peter Smith.
Fujitsu Australia
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-11 02:11 Philip Alger <paalger0@gmail.com>
parent: Peter Smith <smithpb2250@gmail.com>
0 siblings, 1 reply; 22+ messages in thread
From: Philip Alger @ 2026-06-11 02:11 UTC (permalink / raw)
To: Peter Smith <smithpb2250@gmail.com>; +Cc: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; pgsql-hackers@lists.postgresql.org
> > >
> > > 3.
> > > It would be better if the the rows of "Table 9.96" were in
> alphabetical order.
> >
> > I think that this should be done in a different patch when all or a big
> > part of the functions are merged.
> >
>
> FYI, I've created another thread [1] to address those things above,
>
> ======
> [1]
> https://www.postgresql.org/message-id/CAHut+Pun9Z8qZFJTa9fLgdhM=Cip9d-cnx2YXDW6eFrSwbQj1g@mail.gmail...
>
>
It's a good idea, but the author of this patch should either fix it or you
should propose your patch after this one is committed. Otherwise, you're
going to make the author create another version based on a fix you already
suggested for the current patch.
--
Best,
Phil Alger
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-11 07:37 Peter Smith <smithpb2250@gmail.com>
parent: Philip Alger <paalger0@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: Peter Smith @ 2026-06-11 07:37 UTC (permalink / raw)
To: Philip Alger <paalger0@gmail.com>; +Cc: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; pgsql-hackers@lists.postgresql.org
On Thu, Jun 11, 2026 at 12:11 PM Philip Alger <paalger0@gmail.com> wrote:
>
>
>>
>> > >
>> > > 3.
>> > > It would be better if the the rows of "Table 9.96" were in alphabetical order.
>> >
>> > I think that this should be done in a different patch when all or a big
>> > part of the functions are merged.
>> >
>>
>> FYI, I've created another thread [1] to address those things above,
>>
>> ======
>> [1] https://www.postgresql.org/message-id/CAHut+Pun9Z8qZFJTa9fLgdhM=Cip9d-cnx2YXDW6eFrSwbQj1g@mail.gmail...
>>
>
> It's a good idea, but the author of this patch should either fix it or you should propose your patch after this one is committed. Otherwise, you're going to make the author create another version based on a fix you already suggested for the current patch.
>
The other thread patch is addressing things already in PG19 docs.
OTOH, this pg_publication_ddl patch is introducing a new function for
PG20. I'd expect my PG19 patch to be pushed first, but who knows. A
committer will determine that. Or maybe it will be rejected, and not
be pushed at all.
Yes, whatever patch is pushed first will mean the other one will need
rebasing, but that's just a normal part of the process. For example,
already this pg_publication_ddl thread is in a race with the other
publication EXCEPT syntax enhancement threads to see who gets pushed
first....and whoever comes second will have some extra rebasing work
to do to enhance this DDL function. C'est la vie.
======
Kind Regards,
Peter Smith.
Fujitsu Australia.
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-11 09:01 solai v <solai.cdac@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
0 siblings, 0 replies; 22+ messages in thread
From: solai v @ 2026-06-11 09:01 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi all,
On Tue, Jun 9, 2026 at 5:05 PM Jonathan Gonzalez V.
<jonathan.abdiel@gmail.com> wrote:
>
> solai v <solai.cdac@gmail.com> writes:
>
> Hello,
>
> >
> > [...] 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.
>
> Yes this is by design, it's documented that they will return the
> OWNER[1] unless that publications doesn't intend to have an OWNER in the
> DDL.
>
> > 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
>
> Yes, this was already handled by Peter Smith[1] email
>
> Regards!
>
> [1] https://www.postgresql.org/docs/19/functions-info.html#FUNCTIONS-GET-OBJECT-DDL
> [2] https://www.postgresql.org/message-id/CAHut%2BPvyd3vN_LV8ppyX6Vu7pKdBhC5M3_zHN7gdJCvz1%3DkKHQ%40mail...
>
Thank you for the clarification and for sharing the references.
I understand that returning SETOF text with the OWNER statement is
intended and can see the formatting of publication options has been
addressed accordingly in Peter Smith's follow-up email.
Regards,
Solai
^ permalink raw reply [nested|flat] 22+ messages in thread
* Re: Add pg_get_publication_ddl function
@ 2026-06-24 07:26 Rui Zhao <zhaorui126@gmail.com>
parent: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2 siblings, 0 replies; 22+ messages in thread
From: Rui Zhao @ 2026-06-24 07:26 UTC (permalink / raw)
To: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>; +Cc: pgsql-hackers@lists.postgresql.org
Hi Jonathan,
I tested v3 fairly thoroughly and it holds up well. I checked that the
generated DDL round-trips (get the DDL, drop the publication, replay it,
and confirm the output is identical) across: empty publications,
publish='', column lists (including reordered and after a dropped column),
row filters, column+filter combinations, quoted schema/table/column names,
FOR TABLE + TABLES IN SCHEMA, FOR ALL TABLES EXCEPT, ALL SEQUENCES + ALL
TABLES EXCEPT, generated columns, publish_via_partition_root, and
partitioned tables. The variadic option parsing (unknown/duplicate/odd/NULL
options, owner=false) behaves correctly and consistently with
pg_get_database_ddl(). No functional issues found.
One thing from your original post I wanted to close out -- the worry about
this leaking table/column/row-filter information to users without rights on
those tables. I don't think there's a new exposure here: pg_publication and
pg_publication_rel have no REVOKE and are world-readable like the other
system catalogs (unlike pg_subscription, which REVOKEs because of
subconninfo). So the membership, column lists (prattrs) and row filters
(prqual) are already visible to any user via the catalogs;
pg_get_publication_ddl() just formats what is already readable. This also
matches the existing pg_get_*def family (pg_get_viewdef, pg_get_indexdef,
pg_get_functiondef, ...): none of them perform a privilege check. I
confirmed this -- given the object's OID, those functions return the full
definition even to a role that has no access to the object at all (the only
gate is name resolution, i.e. schema USAGE, which an OID argument bypasses).
So I don't think a separate privilege check is needed here either, and
adding one would be inconsistent both with those functions and with the
catalogs being readable.
A couple of smaller comments:
1. src/test/regress/sql/publication_ddl.sql: the 'owner' option isn't
exercised anywhere -- there's no pg_get_publication_ddl(..., 'owner',
'false') case verifying the OWNER statement is omitted. Given the
polarity is easy to get wrong, a test for it would help.
2. src/backend/utils/adt/ddlutils.c: minor, there's a double blank line
after appendStringInfoString(&buf, "publish='") before the pubinsert
block.
Thanks,
Rui
^ permalink raw reply [nested|flat] 22+ messages in thread
end of thread, other threads:[~2026-06-24 07:26 UTC | newest]
Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-01-11 11:20 Add pg_get_publication_ddl function Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-01-11 12:18 ` =?gb18030?B?emVuZ21hbg==?= <zengman@halodbtech.com>
2026-01-13 02:46 ` Peter Smith <smithpb2250@gmail.com>
2026-02-14 00:21 ` Cary Huang <cary.huang@highgo.ca>
2026-02-27 07:53 ` Hüseyin Demir <huseyin.d3r@gmail.com>
2026-05-17 21:27 ` Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-05-20 09:39 ` Peter Smith <smithpb2250@gmail.com>
2026-05-20 10:00 ` Japin Li <japinli@hotmail.com>
2026-06-09 11:43 ` Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-05-20 22:27 ` Peter Smith <smithpb2250@gmail.com>
2026-06-09 11:15 ` Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-06-11 00:04 ` Peter Smith <smithpb2250@gmail.com>
2026-06-11 02:01 ` Peter Smith <smithpb2250@gmail.com>
2026-06-11 02:11 ` Philip Alger <paalger0@gmail.com>
2026-06-11 07:37 ` Peter Smith <smithpb2250@gmail.com>
2026-06-02 11:10 ` solai v <solai.cdac@gmail.com>
2026-06-09 11:35 ` Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-06-11 09:01 ` solai v <solai.cdac@gmail.com>
2026-06-09 18:17 ` Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
2026-06-09 21:06 ` Álvaro Herrera <alvherre@kurilemu.de>
2026-06-09 22:06 ` Zsolt Parragi <zsolt.parragi@percona.com>
2026-06-24 07:26 ` Rui Zhao <zhaorui126@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox