From: Jonathan Gonzalez V. <jonathan.abdiel@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Subject: Re: Add pg_get_publication_ddl function
Date: Tue, 09 Jun 2026 20:17:37 +0200
Message-ID: <87o6hjy3we.fsf@gmail.com> (raw)
In-Reply-To: <defc1876a11710f61228835471c0cdf402167314.camel@gmail.com>
References: <70d20c14d1c14f93e4200c1a9327b97d7c18a782.camel@gmail.com>
<defc1876a11710f61228835471c0cdf402167314.camel@gmail.com>
"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.comAttachments:
[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.sgmlindex 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.cindex 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.datindex 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.outnew file mode 100644index 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_scheduleindex 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.sqlnew file mode 100644index 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: jonathan.abdiel@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: Add pg_get_publication_ddl function
In-Reply-To: <87o6hjy3we.fsf@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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