Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wX11I-0030QX-1d for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jun 2026 18:17:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.96) (envelope-from ) id 1wX11H-007ybn-1M for pgsql-hackers@arkaria.postgresql.org; Tue, 09 Jun 2026 18:17:47 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1wX11G-007ybf-1z for pgsql-hackers@lists.postgresql.org; Tue, 09 Jun 2026 18:17:47 +0000 Received: from mail-ed1-x52d.google.com ([2a00:1450:4864:20::52d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.98.2) (envelope-from ) id 1wX11C-00000001t9f-350K for pgsql-hackers@lists.postgresql.org; Tue, 09 Jun 2026 18:17:45 +0000 Received: by mail-ed1-x52d.google.com with SMTP id 4fb4d7f45d1cf-6924a68e3f3so1594894a12.1 for ; Tue, 09 Jun 2026 11:17:42 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20251104; t=1781029061; x=1781633861; darn=lists.postgresql.org; h=mime-version:user-agent:message-id:date:references:in-reply-to :subject:to:from:from:to:cc:subject:date:message-id:reply-to; bh=PL9hDgX4qWOrfR0ZbgGt48cAbSpYOkvPrXBd2UbSoxE=; b=TBZRpWab4msW9nslQjJPBRxZG96zXaByukRS/DIRjVa/rT+ouJ5Blj8ZpPxGj8XCMt 3mWkByH+HuZtc8HRGLdy4rlrtukZQQ3h+eWspmyhfz4N+MKvXBEiontCjweNnOdzUcyD bB4OAcgl62M91m/euwSiDn2FiXTL4H+PVzfAgtGkKZrTVeS9DH8RhzEzu654hhhPXCWw L59/YFMYvfnk65NbXOaIlhRYqqCPg3m6VhvZRcA57MIyOinBIdpu8jnyZOEeWNlT5mNV b06sgLB0Yq1cf+xdANmF3KkysiuAOZgk2e3Z9XGDaLgFK4yDIv278PbukZMSaOR59+0/ FOBQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20251104; t=1781029061; x=1781633861; h=mime-version:user-agent:message-id:date:references:in-reply-to :subject:to:from:x-gm-gg:x-gm-message-state:from:to:cc:subject:date :message-id:reply-to; bh=PL9hDgX4qWOrfR0ZbgGt48cAbSpYOkvPrXBd2UbSoxE=; b=Esepq+L7odniKByxkbjblaHHtM+eWueIcvHG1jUKqjz9MkrnPhFQU3s3EcSrMPwkFt duAFyoxOotH1fRmk1r56ixuH2izaKwvnziz3mWmv+3yXFrsqMwTHXsUjGWdupFNG6c7F qSva+JexAu6iyuELum2DxudBMKTi1WrVY5ZwMZADz0r4xbLV+RcKQuF+dCKUy7uiZAcu 1NeQMVR4fQehdLs0YIu93E3UsgrEVa5likWndVn3WPtY5dDcE9as0fYvySMAvSIv9Mlj ER30MVhvAdV0jB9o5AVDw22wqFWzXvAIjHE1OniBgGCYur3R/055ypVqhLQJBDtJTo/a LlDQ== X-Gm-Message-State: AOJu0YzFwXh4j82DCMiLvLwMf7A2ZAvmpSWJwDMgnAe9Lph2wuFNPbDk DZ9yFyKWeNovTnyBSijzHeG//xJ4959BHuoG9DPaIsUefKeEjJ1lWYCgYDcN5TI= X-Gm-Gg: Acq92OGhFpSKZGqjeDcUfRWulV5RdYRUj2EKzlRi2aB5qGTwvj580teAFXohzgtC0h7 ewElb4M/fOzwyEHHz9Vjb1HGaaFF2xLIqJcuQTGCq7Eu1Ya92xmRwN4psdzlojB9Vs5XHYI52eX lXOjS3ZyQ3XOO/edcazkgMNwYPbR6Z5TelahTM6a+kEswXU5zzkutM6id/Artz9Il3YazmZgKpE +sRboQhifLtQ1ebHMuExH7q4Z/CJUteOk9CPrlHI9QgGsTEZe2vDhrYx4j2Pu6oxW1MA90aRq5v N/FFb+oOAeRBDcjQ6h7YbP3tib7iviTiJRLLZ4hNAGeQsY2DKjn/gsMIPLpqTFIVxF/ciVgVif6 Tf0l48kIuEwp402UQ5sOGSzDJFTaISqalSWT0tzL7WKvWR4ZxoOZAUTNxFlotQUGlyGI6tDX4qm RUP9NUs95rA8HRlFCHa1e1FRGPfcykWUTB5qdg1K22Qabs4NM2GA== X-Received: by 2002:a05:6402:50ce:b0:683:1cc8:84a0 with SMTP id 4fb4d7f45d1cf-68fa525aba5mr11143370a12.21.1781029061007; Tue, 09 Jun 2026 11:17:41 -0700 (PDT) Received: from localhost ([2a00:6d43:908:5803::3]) by smtp.gmail.com with ESMTPSA id 4fb4d7f45d1cf-68e65b4d988sm9020867a12.25.2026.06.09.11.17.38 for (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 09 Jun 2026 11:17:38 -0700 (PDT) From: "Jonathan Gonzalez V." To: pgsql-hackers@lists.postgresql.org Subject: Re: Add pg_get_publication_ddl function In-Reply-To: (Jonathan Gonzalez V.'s message of "Sun, 17 May 2026 23:27:15 +0200") References: <70d20c14d1c14f93e4200c1a9327b97d7c18a782.camel@gmail.com> Date: Tue, 09 Jun 2026 20:17:37 +0200 Message-ID: <87o6hjy3we.fsf@gmail.com> User-Agent: Gnus/5.13 (Gnus v5.13) MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=-=-=" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --=-=-= Content-Type: text/plain "Jonathan Gonzalez V." writes: Hi! I'm attaching the v3 of this patch. I've added a new set of regress test that execute the query generated by pg_get_publication_ddl(), this helped to find a couple of issues with EXCEPT and WHERE. I've also applied the reviews previously done and discussed early today. Regards -- Jonathan Gonzalez V. EDB https://www.enterprisedb.com --=-=-= Content-Type: text/x-diff; charset=utf-8 Content-Disposition: attachment; filename=v3-0001-Introduce-a-new-function-pg_get_publication_ddl-t.patch Content-Transfer-Encoding: quoted-printable From bc57378ca127a43d5cd20260cdcb11f790a148ca Mon Sep 17 00:00:00 2001 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 --=-=-=--