From: "Jonathan Gonzalez V." Date: Sun, 5 Oct 2025 18:11:56 +0200 Subject: [PATCH v3 1/1] Introduce a new function pg_get_publication_ddl() t= hat returns the CREATE ddl statement for a given PUBLICATION. MIME-Version: 1.0 Content-Type: text/plain; charset=3DUTF-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=C3=BCseyin Demir Reviewed-by: Cary Huang Reviewed-by: Peter Smith Reviewed-by: Man Zeng Reviewed-by: Japin Li Reviewed-by: Solai v Signed-off-by: Jonathan Gonzalez V. --- 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=3DarwdDxtm/postgres,foo=3Dr/pos= tgres} TABLESPACE. + + + + pg_get_publication_ddl + + pg_get_publication_ddl + ( publication oid + , VARIADIC options + text ) + setof text + + + pg_get_publication_ddl + ( publication text + , VARIADIC options + text ) + setof text + + + Reconstructs the = CREATE PUBLICATION statement for + the specified publication (by OID or name), followed by an + ALTER PUBLICATION ... OWNER TO statement (the + CREATE PUBLICATION grammar has no + OWNER clause). Each statement is returned as a + separate row. An error is raised if no publication with the suppl= ied + OID or name exists. + The following options are supported: + pretty (boolean) for formatted output and + owner (boolean) to include + OWNER. + + diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlut= ils.c index f32fcd453ef..2994f7a21f2 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -20,12 +20,14 @@ =20 #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, bo= ol 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); =20 /* * 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 isnul= l) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + DdlOption opts[] =3D { + {"pretty", DDL_OPT_BOOL}, + {"owner", DDL_OPT_BOOL}, + }; + + funcctx =3D SRF_FIRSTCALL_INIT(); + oldcontext =3D MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (isnull) + { + MemoryContextSwitchTo(oldcontext); + SRF_RETURN_DONE(funcctx); + } + + parse_ddl_options(fcinfo, 1, opts, lengthof(opts)); + + statements =3D pg_get_publication_ddl_internal(puboid, + opts[0].isset && opts[0].boolval, + opts[1].isset && !opts[1].boolval); + + funcctx->user_fctx =3D statements; + funcctx->max_calls =3D list_length(statements); + + MemoryContextSwitchTo(oldcontext); + } + + funcctx =3D SRF_PERCALL_SETUP(); + statements =3D (List *) funcctx->user_fctx; + + if (funcctx->call_cntr < funcctx->max_calls) + { + char *stmt; + + stmt =3D (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 =3D InvalidOid; + bool isnull; + + isnull =3D PG_ARGISNULL(0); + if (!isnull) + puboid =3D 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 =3D InvalidOid; + bool isnull; + + isnull =3D PG_ARGISNULL(0); + if (!isnull) + { + char *pubname =3D text_to_cstring(PG_GETARG_TEXT_PP(0)); + + puboid =3D 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 =3D NIL; + List *pub_incl_relids =3D NIL; + List *pub_excl_relids =3D NIL; + List *pub_schemas =3D NIL; + bool first_perm =3D true; + + if (!SearchSysCacheExists1(PUBLICATIONOID, ObjectIdGetDatum(puboid))) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("publication with OID %u does not exist", puboid))); + + pub =3D GetPublication(puboid); + + initStringInfo(&buf); + + appendStringInfo(&buf, "CREATE PUBLICATION %s", quote_identifier(pub->nam= e)); + + /* + * 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 =3D GetExcludedPublicationTables( + pub->oid, PUBLICATION_PART_ROOT); + + if (pub_excl_relids !=3D NIL) + { + ListCell *excl_cell; + char *schemaname =3D NULL; + + appendStringInfoString(&buf, " EXCEPT (TABLE "); + + foreach(excl_cell, pub_excl_relids) + { + HeapTuple tp =3D 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_va= lue); + + reltup =3D (Form_pg_class) GETSTRUCT(tp); + schemaname =3D 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 =3D GetIncludedPublicationRelations(pub->oid, PUBLICATIO= N_PART_ROOT); + pub_schemas =3D GetPublicationSchemas(pub->oid); + } + + /* + * Publication can have table relations + */ + if (pub_incl_relids !=3D NIL) + { + ListCell *pub_cell; + char *schemaname =3D NULL; + char *tablename; + + append_ddl_option(&buf, pretty, 4, "FOR TABLE "); + + foreach(pub_cell, pub_incl_relids) + { + HeapTuple pubtuple =3D NULL; + HeapTuple reltup; + Form_pg_class relform; + Datum columns, + rowfilter; + Oid relid =3D pub_cell->oid_value; + bool cols_nulls, + condition_nulls; + + reltup =3D SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(reltup)) + elog(ERROR, + "cache lookup failed for relation %u", + relid); + + relform =3D (Form_pg_class) GETSTRUCT(reltup); + tablename =3D NameStr(relform->relname); + schemaname =3D get_namespace_name(relform->relnamespace); + + appendStringInfo(&buf, "%s%s", + foreach_current_index(pub_cell) > 0 ? ", " : "", + quote_qualified_identifier(schemaname, tablename)); + + pfree(schemaname); + + pubtuple =3D 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 =3D SysCacheGetAttr(PUBLICATIONRELMAP, pubtuple, + Anum_pg_publication_rel_prattrs, + &cols_nulls); + + rowfilter =3D 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 =3D -1; + + attmap =3D pub_collist_to_bitmapset(NULL, columns, NULL); + + appendStringInfoChar(&buf, '('); + while ((attnum =3D bms_next_member(attmap, attnum)) >=3D 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 =3D stringToNode(TextDatumGetCString(rowfilter)); + context =3D deparse_context_for(tablename, relid); + str =3D 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 !=3D 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 =3D=3D NIL) + append_ddl_option(&buf, pretty, 4, "FOR TABLES IN SCHEMA"); + else + appendStringInfoString(&buf, ", TABLES IN SCHEMA"); + + foreach(schema_cell, pub_schemas) + { + char *nspname =3D 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=3D'"); + + + 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 =3D false; + } + if (pub->pubactions.pubupdate) + { + appendStringInfo(&buf, "%supdate", first_perm ? "" : ", "); + first_perm =3D false; + } + if (pub->pubactions.pubdelete) + { + appendStringInfo(&buf, "%sdelete", first_perm ? "" : ", "); + first_perm =3D false; + } + if (pub->pubactions.pubtruncate) + { + appendStringInfo(&buf, "%struncate", first_perm ? "" : ", "); + } + + appendStringInfoString(&buf, "', "); + + /* publish_generated_columns string */ + appendStringInfo(&buf, "publish_generated_columns=3D%s, ", + pub->pubgencols_type =3D=3D PUBLISH_GENCOLS_NONE ? "none" : "stored"= ); + + /* publish_via_partition_root value */ + appendStringInfo(&buf, "publish_via_partition_root=3D%s)", + pub->pubviaroot ? "true" : "false"); + + appendStringInfoChar(&buf, ';'); + statements =3D lappend(statements, pstrdup(buf.data)); + pfree(buf.data); + + /* OWNER */ + if (!no_owner) + { + HeapTuple tup; + Form_pg_publication pubform; + char *owner; + + tup =3D SearchSysCache1(PUBLICATIONOID, ObjectIdGetDatum(puboid)); + if (!HeapTupleIsValid(tup)) + elog(ERROR, "cache lookup failed for publication %u", puboid); + pubform =3D (Form_pg_publication) GETSTRUCT(tup); + owner =3D GetUserNameFromId(pubform->pubowner, false); + ReleaseSysCache(tup); + + initStringInfo(&buf); + appendStringInfo(&buf, "ALTER PUBLICATION %s OWNER TO %s;", + quote_identifier(pub->name), quote_identifier(owner)); + statements =3D 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 =3D> 'pg_relation_is_publishable', provolatile =3D> 's', prorettype =3D> 'bool', proargtypes =3D> 'regclass', prosrc =3D> 'pg_relation_is_publishable' }, +{ oid =3D> '6122', descr =3D> 'get DDL to recreate a publication', + proname =3D> 'pg_get_publication_ddl', provariadic =3D> 'text', proisstr= ict =3D> 'f', + provolatile =3D> 's', proretset =3D> 't', prorows =3D> '2', prorettype = =3D> 'text', + proargtypes =3D> 'oid text', + proargmodes =3D> '{i,v}', + proallargtypes =3D> '{oid,text}', + pronargdefaults =3D> '1', proargdefaults =3D> '{NULL}', + prosrc =3D> 'pg_get_publication_ddl_oid' }, +{ oid =3D> '6123', descr =3D> 'get DDL to recreate a publication', + proname =3D> 'pg_get_publication_ddl', provariadic =3D> 'text', proisstr= ict =3D> 'f', + provolatile =3D> 's', proretset =3D> 't', prorows =3D> '2', prorettype = =3D> 'text', + proargtypes =3D> 'text text', + proargmodes =3D> '{i,v}', + proallargtypes =3D> '{text,text}', + pronargdefaults =3D> '1', proargdefaults =3D> '{NULL}', + prosrc =3D> 'pg_get_publication_ddl_name' }, =20 # rls { oid =3D> '3298', diff --git a/src/test/regress/expected/publication_ddl.out b/src/test/regre= ss/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 =3D '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_pub= lication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -- + CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete,= truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3D= 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 pubnam= e=3D'testpub_ddl_1')); + pg_get_pub= lication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -- + CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete,= truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3D= 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_1 = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_1 OWNER TO regress_publication_ddl_user; +(2 rows) + +-- NULL input should produce an empty result set +SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::oid); + is_null=20 +--------- + t +(1 row) + +SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::text); + is_null=20 +--------- + 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_t= bl2, testpub_ddl_tbl3 WITH (publish=3D'delete', publish_generated_columns= =3D'stored', publish_via_partition_root=3D'true'); +SELECT pg_get_publication_ddl('testpub_ddl_2'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi= c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'delete', publi= sh_generated_columns=3Dstored, publish_via_partition_root=3Dtrue); + ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_2')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi= c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'delete', publi= sh_generated_columns=3Dstored, publish_via_partition_root=3Dtrue); + 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=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ----------------------- + CREATE PUBLICATION testpub_ddl_2 = + + FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.te= stpub_ddl_tbl3 + + WITH (publish=3D'delete', publish_generated_columns=3Dstored, publish= _via_partition_root=3Dtrue); + ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user; +(2 rows) + +ALTER PUBLICATION testpub_ddl_2 SET (publish =3D 'delete, update'); +SELECT pg_get_publication_ddl('testpub_ddl_2'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi= c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'update, delete= ', publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue); + ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_2')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_2 FOR TABLE public.testpub_ddl_tbl1, publi= c.testpub_ddl_tbl2, public.testpub_ddl_tbl3 WITH (publish=3D'update, delete= ', publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue); + 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=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------- + CREATE PUBLICATION testpub_ddl_2 = + + FOR TABLE public.testpub_ddl_tbl1, public.testpub_ddl_tbl2, public.te= stpub_ddl_tbl3 + + WITH (publish=3D'update, delete', publish_generated_columns=3Dstored,= publish_via_partition_root=3Dtrue); + 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=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------ + CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (= publish=3D'insert, update, delete, truncate', publish_generated_columns=3Dn= one, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_3')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------ + CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (= publish=3D'insert, update, delete, truncate', publish_generated_columns=3Dn= one, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_3 = + + FOR TABLE public.testpub_ddl_tbl1 = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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 =3D baz); +SELECT pg_get_publication_ddl('testpub_ddl_4'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------ + CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, b= az) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate= ', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_4')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------ + CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, b= az) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate= ', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_4 = + + FOR TABLE public.testpub_ddl_tbl3(bar, baz) WHERE ((bar =3D baz)) = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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,bequ= e) WHERE (beque IS TRUE); +SELECT pg_get_publication_ddl('testpub_ddl_5'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------ + CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, b= eque) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trun= cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse= ); + ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_5')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------ + CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, b= eque) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trun= cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse= ); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_5 = + + FOR TABLE public.testpub_ddl_tbl4(bar, beque) WHERE ((beque IS TRUE))= + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_6')); + pg= _get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_6 = + + FOR ALL TABLES = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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'); + p= g_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- + CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'inser= t, update, delete, truncate', publish_generated_columns=3Dnone, publish_via= _partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_7')); + p= g_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------- + CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'inser= t, update, delete, truncate', publish_generated_columns=3Dnone, publish_via= _partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_7 = + + FOR ALL SEQUENCES = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------- + CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publ= ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,= publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_8')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------- + CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publ= ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,= publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_8 = + + FOR ALL TABLES, ALL SEQUENCES = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user; +(2 rows) + +-- explicit publish_generated_columns=3D'none' +CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_co= lumns=3D'none'); +SELECT pg_get_publication_ddl('testpub_ddl_9'); + pg= _get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_9 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_9')); + pg= _get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_9 = + + FOR ALL TABLES = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------- + CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE= (beque) WITH (publish=3D'insert, update, delete, truncate', publish_genera= ted_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_10 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_10')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------------------- + CREATE PUBLICATION testpub_ddl_10 FOR TABLE public.testpub_ddl_tbl4 WHERE= (beque) WITH (publish=3D'insert, update, delete, truncate', publish_genera= ted_columns=3Dnone, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_10 = + + FOR TABLE public.testpub_ddl_tbl4 WHERE (beque) = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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.sche= ma_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=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test= _ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_= columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_schema_1')); + = pg_get_publication_ddl=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test= _ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_= columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_schema_1', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + 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=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +-- create publication in schema only for table +CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLES IN SCHEMA pub_schema_te= st_ddl, TABLE pub_schema_test_ddl.schema_tbl1; +SELECT pg_get_publication_ddl('testpub_ddl_schema_2'); + = pg_get_publication_ddl=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------- + CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upd= ate, delete, truncate', publish_generated_columns=3Dnone, publish_via_parti= tion_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_schema_2')); + = pg_get_publication_ddl=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------- + CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upd= ate, delete, truncate', publish_generated_columns=3Dnone, publish_via_parti= tion_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_schema_2', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_schema_2 = + + FOR TABLE pub_schema_test_ddl.schema_tbl1, TABLES IN SCHEMA pub_schem= a_test_ddl + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +-- create publication for all tables in schema +CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_te= st_ddl; +SELECT pg_get_publication_ddl('testpub_ddl_schema_3'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_t= est_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generat= ed_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_schema_3')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= -------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_t= est_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generat= ed_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_schema_3', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_schema_3 = + + FOR TABLES IN SCHEMA pub_schema_test_ddl = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_u= ser; +(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_te= st_ddl, pub_schema_test_ddl_2; +SELECT pg_get_publication_ddl('testpub_ddl_schema_4'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_t= est_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, tru= ncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfals= e); + ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_schema_4')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_t= est_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, tru= ncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfals= e); + ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_schema_4', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_schema_4 = + + FOR TABLES IN SCHEMA pub_schema_test_ddl, pub_schema_test_ddl_2 = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_u= ser; +(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.sch= ema_tbl1, schema_tbl1; +SELECT pg_get_publication_ddl('testpub_ddl_schema_5'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunc= ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_schema_5')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sch= ema_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunc= ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_schema_5', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_schema_5 = + + FOR TABLE pub_schema_test_ddl.schema_tbl1, public.schema_tbl1 = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_u= ser; +(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=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- + CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_part1')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- + CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_part1 = + + FOR TABLE public.testpub_ddl_part = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user; +(2 rows) + +CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publ= ish_via_partition_root=3D'false'); +SELECT pg_get_publication_ddl('testpub_ddl_part2'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- + CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_part2')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------- + CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_part2 = + + FOR TABLE public.testpub_ddl_part = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user; +(2 rows) + +CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publ= ish_via_partition_root=3D'true'); +SELECT pg_get_publication_ddl('testpub_ddl_part3'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------- + CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dtrue); + ALTER PUBLICATION testpub_ddl_part3 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_part3')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------------- + CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WI= TH (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dtrue); + 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=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ----------------------------------------------- + CREATE PUBLICATION testpub_ddl_part3 = + + FOR TABLE public.testpub_ddl_part = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dtrue); + 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=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------- + CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1= WITH (publish=3D'insert, update, delete, truncate', publish_generated_colu= mns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_part4')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ------------------------------------------- + CREATE PUBLICATION testpub_ddl_part4 FOR TABLE public.testpub_ddl_part_p1= WITH (publish=3D'insert, update, delete, truncate', publish_generated_colu= mns=3Dnone, publish_via_partition_root=3Dfalse); + 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= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_part4 = + + FOR TABLE public.testpub_ddl_part_p1 = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + 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 Tab= le" ("Col One", "select") WHERE ("Col One" > 0); +SELECT pg_get_publication_ddl('testpub Quoted Pub'); + = pg_get_publication_ddl=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Ta= ble"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub Quoted Pub')); + = pg_get_publication_ddl=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------- + CREATE PUBLICATION "testpub Quoted Pub" FOR TABLE "Pub Schema"."Quoted Ta= ble"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, = update, delete, truncate', publish_generated_columns=3Dnone, publish_via_pa= rtition_root=3Dfalse); + ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u= ser; +(2 rows) + +SELECT pg_get_publication_ddl('testpub Quoted Pub', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION "testpub Quoted Pub" = + + FOR TABLE "Pub Schema"."Quoted Table"("Col One", "select") WHERE (("C= ol One" > 0)) + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_u= ser; +(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 testpu= b_ddl_except1); +SELECT pg_get_publication_ddl('testpub_ddl_except1'); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE publi= c.testpub_ddl_except1) WITH (publish=3D'insert, update, delete, truncate', = publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us= er; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_except1')); + = pg_get_publication_ddl=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------- + CREATE PUBLICATION testpub_ddl_except1 FOR ALL TABLES EXCEPT (TABLE publi= c.testpub_ddl_except1) WITH (publish=3D'insert, update, delete, truncate', = publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us= er; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_except1', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_except1 = + + FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1) = + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_us= er; +(2 rows) + +-- create publication for all sequences and all tables except two tables +CREATE PUBLICATION testpub_ddl_except2 FOR ALL SEQUENCES, ALL TABLES EXCEP= T (TABLE testpub_ddl_except1, testpub_ddl_except2); +SELECT pg_get_publication_ddl('testpub_ddl_except2'); + = pg_get_publication_ddl=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------- + CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE publi= c.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (pub= lish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone= , publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us= er; +(2 rows) + +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_except2')); + = pg_get_publication_ddl=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= --------------------------------- + CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE publi= c.testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (pub= lish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone= , publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us= er; +(2 rows) + +SELECT pg_get_publication_ddl('testpub_ddl_except2', 'pretty', 'true'); + pg_get_publication_ddl= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20=20 +--------------------------------------------------------------------------= ------------------------------------------------ + CREATE PUBLICATION testpub_ddl_except2 = + + FOR ALL TABLES EXCEPT (TABLE public.testpub_ddl_except1, public.testp= ub_ddl_except2), ALL SEQUENCES + + WITH (publish=3D'insert, update, delete, truncate', publish_generated= _columns=3Dnone, publish_via_partition_root=3Dfalse); + ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_us= er; +(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 Tab= le"("Col One", "select") WHERE (("Col One" > 0)) WITH (publish=3D'insert, u= pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par= tition_root=3Dfalse); +ALTER PUBLICATION "testpub Quoted Pub" OWNER TO regress_publication_ddl_us= er; +CREATE PUBLICATION testpub_ddl_1 WITH (publish=3D'insert, update, delete, = truncate', publish_generated_columns=3Dnone, publish_via_partition_root=3Df= alse); +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=3D'insert, update, delete, truncate', publish_generat= ed_columns=3Dnone, publish_via_partition_root=3Dfalse); +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=3D'update, delete'= , publish_generated_columns=3Dstored, publish_via_partition_root=3Dtrue); +ALTER PUBLICATION testpub_ddl_2 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_3 FOR TABLE public.testpub_ddl_tbl1 WITH (p= ublish=3D'insert, update, delete, truncate', publish_generated_columns=3Dno= ne, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_3 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_4 FOR TABLE public.testpub_ddl_tbl3(bar, ba= z) WHERE ((bar =3D baz)) WITH (publish=3D'insert, update, delete, truncate'= , publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_4 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_5 FOR TABLE public.testpub_ddl_tbl4(bar, be= que) WHERE ((beque IS TRUE)) WITH (publish=3D'insert, update, delete, trunc= ate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_5 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_6 FOR ALL TABLES WITH (publish=3D'insert, u= pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par= tition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_6 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_7 FOR ALL SEQUENCES WITH (publish=3D'insert= , update, delete, truncate', publish_generated_columns=3Dnone, publish_via_= partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_7 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_8 FOR ALL TABLES, ALL SEQUENCES WITH (publi= sh=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone, = publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_8 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish=3D'insert, u= pdate, delete, truncate', publish_generated_columns=3Dnone, publish_via_par= tition_root=3Dfalse); +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=3D'insert, update, delete, truncate', p= ublish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_except1 OWNER TO regress_publication_ddl_use= r; +CREATE PUBLICATION testpub_ddl_except2 FOR ALL TABLES EXCEPT (TABLE public= .testpub_ddl_except1, public.testpub_ddl_except2), ALL SEQUENCES WITH (publ= ish=3D'insert, update, delete, truncate', publish_generated_columns=3Dnone,= publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_except2 OWNER TO regress_publication_ddl_use= r; +CREATE PUBLICATION testpub_ddl_part1 FOR TABLE public.testpub_ddl_part WIT= H (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_part1 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_part2 FOR TABLE public.testpub_ddl_part WIT= H (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_part2 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_part3 FOR TABLE public.testpub_ddl_part WIT= H (publish=3D'insert, update, delete, truncate', publish_generated_columns= =3Dnone, publish_via_partition_root=3Dtrue); +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=3D'insert, update, delete, truncate', publish_generated_colum= ns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_part4 OWNER TO regress_publication_ddl_user; +CREATE PUBLICATION testpub_ddl_schema_1 FOR TABLE pub_schema_test_ddl.sche= ma_tbl1, pub_schema_test_ddl.schema_tbl2, TABLES IN SCHEMA pub_schema_test_= ddl WITH (publish=3D'insert, update, delete, truncate', publish_generated_c= olumns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_schema_1 OWNER TO regress_publication_ddl_us= er; +CREATE PUBLICATION testpub_ddl_schema_2 FOR TABLE pub_schema_test_ddl.sche= ma_tbl1, TABLES IN SCHEMA pub_schema_test_ddl WITH (publish=3D'insert, upda= te, delete, truncate', publish_generated_columns=3Dnone, publish_via_partit= ion_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_schema_2 OWNER TO regress_publication_ddl_us= er; +CREATE PUBLICATION testpub_ddl_schema_3 FOR TABLES IN SCHEMA pub_schema_te= st_ddl WITH (publish=3D'insert, update, delete, truncate', publish_generate= d_columns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_schema_3 OWNER TO regress_publication_ddl_us= er; +CREATE PUBLICATION testpub_ddl_schema_4 FOR TABLES IN SCHEMA pub_schema_te= st_ddl, pub_schema_test_ddl_2 WITH (publish=3D'insert, update, delete, trun= cate', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse= ); +ALTER PUBLICATION testpub_ddl_schema_4 OWNER TO regress_publication_ddl_us= er; +CREATE PUBLICATION testpub_ddl_schema_5 FOR TABLE pub_schema_test_ddl.sche= ma_tbl1, public.schema_tbl1 WITH (publish=3D'insert, update, delete, trunca= te', publish_generated_columns=3Dnone, publish_via_partition_root=3Dfalse); +ALTER PUBLICATION testpub_ddl_schema_5 OWNER TO regress_publication_ddl_us= er; +-- 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 clus= ter # oidjoins is read-only, though, and should run late for best coverage test: oidjoins event_trigger =20 - # 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 =20 +# 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 d= uring # setup that other tests may use. test: tablespace diff --git a/src/test/regress/sql/publication_ddl.sql b/src/test/regress/sq= l/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 =3D '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 pubnam= e=3D'testpub_ddl_1')); +SELECT pg_get_publication_ddl('testpub_ddl_1', 'pretty', 'true'); + +-- NULL input should produce an empty result set +SELECT count(*) =3D 0 AS is_null FROM pg_get_publication_ddl(NULL::oid); +SELECT count(*) =3D 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_t= bl2, testpub_ddl_tbl3 WITH (publish=3D'delete', publish_generated_columns= =3D'stored', publish_via_partition_root=3D'true'); + +SELECT pg_get_publication_ddl('testpub_ddl_2'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_2')); +SELECT pg_get_publication_ddl('testpub_ddl_2', 'pretty', 'true'); + +ALTER PUBLICATION testpub_ddl_2 SET (publish =3D 'delete, update'); + +SELECT pg_get_publication_ddl('testpub_ddl_2'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 pubnam= e=3D'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 =3D baz); + +SELECT pg_get_publication_ddl('testpub_ddl_4'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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,bequ= e) WHERE (beque IS TRUE); + +SELECT pg_get_publication_ddl('testpub_ddl_5'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 pubnam= e=3D'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 pubnam= e=3D'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 pubnam= e=3D'testpub_ddl_8')); +SELECT pg_get_publication_ddl('testpub_ddl_8', 'pretty', 'true'); + +-- explicit publish_generated_columns=3D'none' +CREATE PUBLICATION testpub_ddl_9 FOR ALL TABLES WITH (publish_generated_co= lumns=3D'none'); +SELECT pg_get_publication_ddl('testpub_ddl_9'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 pubnam= e=3D'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.sche= ma_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 pubnam= e=3D'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_te= st_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 pubnam= e=3D'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_te= st_ddl; +SELECT pg_get_publication_ddl('testpub_ddl_schema_3'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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_te= st_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 pubnam= e=3D'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.sch= ema_tbl1, schema_tbl1; +SELECT pg_get_publication_ddl('testpub_ddl_schema_5'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 pubnam= e=3D'testpub_ddl_part1')); +SELECT pg_get_publication_ddl('testpub_ddl_part1', 'pretty', 'true'); + +CREATE PUBLICATION testpub_ddl_part2 FOR TABLE testpub_ddl_part WITH (publ= ish_via_partition_root=3D'false'); +SELECT pg_get_publication_ddl('testpub_ddl_part2'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'testpub_ddl_part2')); +SELECT pg_get_publication_ddl('testpub_ddl_part2', 'pretty', 'true'); + +CREATE PUBLICATION testpub_ddl_part3 FOR TABLE testpub_ddl_part WITH (publ= ish_via_partition_root=3D'true'); +SELECT pg_get_publication_ddl('testpub_ddl_part3'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 pubnam= e=3D'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 Tab= le" ("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 pubnam= e=3D'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 testpu= b_ddl_except1); +SELECT pg_get_publication_ddl('testpub_ddl_except1'); +SELECT pg_get_publication_ddl((SELECT oid FROM pg_publication WHERE pubnam= e=3D'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 EXCEP= T (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 pubnam= e=3D'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; --=20 2.53.0 --=-=-=--