v20250610-0002-Introduce-ALL-SEQUENCES-support-for-Postgr.patch
application/x-patch
Filename: v20250610-0002-Introduce-ALL-SEQUENCES-support-for-Postgr.patch
Type: application/x-patch
Part: 1
Message:
Re: Logical Replication of sequences
From 6f08269b286ea8ec35a7f4314c47d9142454876c Mon Sep 17 00:00:00 2001
From: Vignesh <vignesh21@gmail.com>
Date: Thu, 22 May 2025 20:15:34 +0530
Subject: [PATCH v20250610 2/6] Introduce "ALL SEQUENCES" support for
PostgreSQL logical replication
This commit enhances logical replication by enabling the inclusion of all
sequences in publications.
Furthermore, enhancements to psql commands now display which
publications contain the specified sequence (\d command), and if a
specified publication includes all sequences (\dRp command).
Note: This patch currently supports only the "ALL SEQUENCES" clause.
Handling of clauses such as "FOR SEQUENCE" and "FOR SEQUENCES IN SCHEMA"
will be addressed in a subsequent patch.
---
src/backend/catalog/pg_publication.c | 4 +-
src/backend/commands/publicationcmds.c | 52 +-
src/backend/parser/gram.y | 84 +++-
src/bin/pg_dump/pg_dump.c | 14 +-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 22 +
src/bin/psql/describe.c | 202 +++++---
src/bin/psql/tab-complete.in.c | 8 +-
src/include/catalog/pg_publication.h | 7 +
src/include/nodes/parsenodes.h | 18 +
src/test/regress/expected/psql.out | 6 +-
src/test/regress/expected/publication.out | 560 ++++++++++++----------
src/test/regress/sql/publication.sql | 37 ++
src/tools/pgindent/typedefs.list | 2 +
14 files changed, 680 insertions(+), 337 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d6f94db5d99..dc3f9ed3fbf 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -134,7 +134,8 @@ static bool
is_publishable_class(Oid relid, Form_pg_class reltuple)
{
return (reltuple->relkind == RELKIND_RELATION ||
- reltuple->relkind == RELKIND_PARTITIONED_TABLE) &&
+ reltuple->relkind == RELKIND_PARTITIONED_TABLE ||
+ reltuple->relkind == RELKIND_SEQUENCE) &&
!IsCatalogRelationOid(relid) &&
reltuple->relpersistence == RELPERSISTENCE_PERMANENT &&
relid >= FirstNormalObjectId;
@@ -1083,6 +1084,7 @@ GetPublication(Oid pubid)
pub->oid = pubid;
pub->name = pstrdup(NameStr(pubform->pubname));
pub->alltables = pubform->puballtables;
+ pub->allsequences = pubform->puballsequences;
pub->pubactions.pubinsert = pubform->pubinsert;
pub->pubactions.pubupdate = pubform->pubupdate;
pub->pubactions.pubdelete = pubform->pubdelete;
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index 0b23d94c38e..ef13cf618d3 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -848,11 +848,17 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
aclcheck_error(aclresult, OBJECT_DATABASE,
get_database_name(MyDatabaseId));
- /* FOR ALL TABLES requires superuser */
- if (stmt->for_all_tables && !superuser())
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("must be superuser to create FOR ALL TABLES publication")));
+ if (!superuser())
+ {
+ if (stmt->for_all_tables)
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create a FOR ALL TABLES publication"));
+ if (stmt->for_all_sequences)
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("must be superuser to create a FOR ALL SEQUENCES publication"));
+ }
rel = table_open(PublicationRelationId, RowExclusiveLock);
@@ -886,6 +892,8 @@ CreatePublication(ParseState *pstate, CreatePublicationStmt *stmt)
values[Anum_pg_publication_oid - 1] = ObjectIdGetDatum(puboid);
values[Anum_pg_publication_puballtables - 1] =
BoolGetDatum(stmt->for_all_tables);
+ values[Anum_pg_publication_puballsequences - 1] =
+ BoolGetDatum(stmt->for_all_sequences);
values[Anum_pg_publication_pubinsert - 1] =
BoolGetDatum(pubactions.pubinsert);
values[Anum_pg_publication_pubupdate - 1] =
@@ -2019,19 +2027,27 @@ AlterPublicationOwner_internal(Relation rel, HeapTuple tup, Oid newOwnerId)
aclcheck_error(aclresult, OBJECT_DATABASE,
get_database_name(MyDatabaseId));
- if (form->puballtables && !superuser_arg(newOwnerId))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied to change owner of publication \"%s\"",
- NameStr(form->pubname)),
- errhint("The owner of a FOR ALL TABLES publication must be a superuser.")));
-
- if (!superuser_arg(newOwnerId) && is_schema_publication(form->oid))
- ereport(ERROR,
- (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- errmsg("permission denied to change owner of publication \"%s\"",
- NameStr(form->pubname)),
- errhint("The owner of a FOR TABLES IN SCHEMA publication must be a superuser.")));
+ if (!superuser_arg(newOwnerId))
+ {
+ if (form->puballtables)
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to change owner of publication \"%s\"",
+ NameStr(form->pubname)),
+ errhint("The owner of a FOR ALL TABLES publication must be a superuser."));
+ if (form->puballsequences)
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to change owner of publication \"%s\"",
+ NameStr(form->pubname)),
+ errhint("The owner of a FOR ALL SEQUENCES publication must be a superuser."));
+ if (is_schema_publication(form->oid))
+ ereport(ERROR,
+ errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied to change owner of publication \"%s\"",
+ NameStr(form->pubname)),
+ errhint("The owner of a FOR TABLES IN SCHEMA publication must be a superuser."));
+ }
}
form->pubowner = newOwnerId;
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0b5652071d1..958ff4c226a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -204,6 +204,10 @@ static PartitionStrategy parsePartitionStrategy(char *strategy, int location,
core_yyscan_t yyscanner);
static void preprocess_pubobj_list(List *pubobjspec_list,
core_yyscan_t yyscanner);
+static void preprocess_pub_all_objtype_list(List *all_objects_list,
+ bool *all_tables,
+ bool *all_sequences,
+ core_yyscan_t yyscanner);
static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%}
@@ -260,6 +264,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
PartitionBoundSpec *partboundspec;
RoleSpec *rolespec;
PublicationObjSpec *publicationobjectspec;
+ PublicationAllObjSpec *publicationallobjectspec;
struct SelectLimit *selectlimit;
SetQuantifier setquantifier;
struct GroupClause *groupclause;
@@ -446,7 +451,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
transform_element_list transform_type_list
TriggerTransitions TriggerReferencing
vacuum_relation_list opt_vacuum_relation_list
- drop_option_list pub_obj_list
+ drop_option_list pub_obj_list pub_obj_type_list
%type <retclause> returning_clause
%type <node> returning_option
@@ -585,6 +590,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> var_value zone_value
%type <rolespec> auth_ident RoleSpec opt_granted_by
%type <publicationobjectspec> PublicationObjSpec
+%type <publicationallobjectspec> PublicationAllObjSpec
%type <keyword> unreserved_keyword type_func_name_keyword
%type <keyword> col_name_keyword reserved_keyword
@@ -10614,7 +10620,12 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec
*
* CREATE PUBLICATION name [WITH options]
*
- * CREATE PUBLICATION FOR ALL TABLES [WITH options]
+ * CREATE PUBLICATION FOR ALL pub_obj_type [, ...] [WITH options]
+ *
+ * pub_obj_type is one of:
+ *
+ * TABLES
+ * SEQUENCES
*
* CREATE PUBLICATION FOR pub_obj [, ...] [WITH options]
*
@@ -10634,13 +10645,13 @@ CreatePublicationStmt:
n->options = $4;
$$ = (Node *) n;
}
- | CREATE PUBLICATION name FOR ALL TABLES opt_definition
+ | CREATE PUBLICATION name FOR ALL pub_obj_type_list opt_definition
{
CreatePublicationStmt *n = makeNode(CreatePublicationStmt);
n->pubname = $3;
+ preprocess_pub_all_objtype_list($6, &n->for_all_tables, &n->for_all_sequences, yyscanner);
n->options = $7;
- n->for_all_tables = true;
$$ = (Node *) n;
}
| CREATE PUBLICATION name FOR pub_obj_list opt_definition
@@ -10752,6 +10763,28 @@ pub_obj_list: PublicationObjSpec
{ $$ = lappend($1, $3); }
;
+PublicationAllObjSpec:
+ TABLES
+ {
+ $$ = makeNode(PublicationAllObjSpec);
+ $$->pubobjtype = PUBLICATION_ALL_TABLES;
+ $$->location = @1;
+ }
+ | SEQUENCES
+ {
+ $$ = makeNode(PublicationAllObjSpec);
+ $$->pubobjtype = PUBLICATION_ALL_SEQUENCES;
+ $$->location = @1;
+ }
+ ;
+
+pub_obj_type_list: PublicationAllObjSpec
+ { $$ = list_make1($1); }
+ | pub_obj_type_list ',' PublicationAllObjSpec
+ { $$ = lappend($1, $3); }
+ ;
+
+
/*****************************************************************************
*
* ALTER PUBLICATION name SET ( options )
@@ -19638,6 +19671,47 @@ parsePartitionStrategy(char *strategy, int location, core_yyscan_t yyscanner)
}
+/*
+ * Process all_objects_list to set all_tables/all_sequences.
+ * Also, checks if the pub_object_type has been specified more than once.
+ */
+static void
+preprocess_pub_all_objtype_list(List *all_objects_list, bool *all_tables,
+ bool *all_sequences, core_yyscan_t yyscanner)
+{
+ if (!all_objects_list)
+ return;
+
+ *all_tables = false;
+ *all_sequences = false;
+
+ foreach_ptr(PublicationAllObjSpec, obj, all_objects_list)
+ {
+ if (obj->pubobjtype == PUBLICATION_ALL_TABLES)
+ {
+ if (*all_tables)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid publication object list"),
+ errdetail("ALL TABLES can be specified only once."),
+ parser_errposition(obj->location));
+
+ *all_tables = true;
+ }
+ else if (obj->pubobjtype == PUBLICATION_ALL_SEQUENCES)
+ {
+ if (*all_sequences)
+ ereport(ERROR,
+ errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("invalid publication object list"),
+ errdetail("ALL SEQUENCES can be specified only once."),
+ parser_errposition(obj->location));
+
+ *all_sequences = true;
+ }
+ }
+}
+
/*
* Process pubobjspec_list to check for errors in any of the objects and
* convert PUBLICATIONOBJ_CONTINUATION into appropriate PublicationObjSpecType.
@@ -19657,7 +19731,7 @@ preprocess_pubobj_list(List *pubobjspec_list, core_yyscan_t yyscanner)
ereport(ERROR,
errcode(ERRCODE_SYNTAX_ERROR),
errmsg("invalid publication object list"),
- errdetail("One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name."),
+ errdetail("One of TABLE, TABLES IN SCHEMA or ALL SEQUENCES must be specified before a standalone table or schema name."),
parser_errposition(pubobj->location));
foreach(cell, pubobjspec_list)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 37432e66efd..8453a3670f3 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -4390,6 +4390,7 @@ getPublications(Archive *fout)
int i_pubname;
int i_pubowner;
int i_puballtables;
+ int i_puballsequences;
int i_pubinsert;
int i_pubupdate;
int i_pubdelete;
@@ -4420,9 +4421,9 @@ getPublications(Archive *fout)
appendPQExpBufferStr(query, "false AS pubviaroot, ");
if (fout->remoteVersion >= 180000)
- appendPQExpBufferStr(query, "p.pubgencols ");
+ appendPQExpBufferStr(query, "p.pubgencols, p.puballsequences ");
else
- appendPQExpBuffer(query, "'%c' AS pubgencols ", PUBLISH_GENCOLS_NONE);
+ appendPQExpBuffer(query, "'%c' AS pubgencols, false AS puballsequences ", PUBLISH_GENCOLS_NONE);
appendPQExpBufferStr(query, "FROM pg_publication p");
@@ -4438,6 +4439,7 @@ getPublications(Archive *fout)
i_pubname = PQfnumber(res, "pubname");
i_pubowner = PQfnumber(res, "pubowner");
i_puballtables = PQfnumber(res, "puballtables");
+ i_puballsequences = PQfnumber(res, "puballsequences");
i_pubinsert = PQfnumber(res, "pubinsert");
i_pubupdate = PQfnumber(res, "pubupdate");
i_pubdelete = PQfnumber(res, "pubdelete");
@@ -4458,6 +4460,8 @@ getPublications(Archive *fout)
pubinfo[i].rolname = getRoleName(PQgetvalue(res, i, i_pubowner));
pubinfo[i].puballtables =
(strcmp(PQgetvalue(res, i, i_puballtables), "t") == 0);
+ pubinfo[i].puballsequences =
+ (strcmp(PQgetvalue(res, i, i_puballsequences), "t") == 0);
pubinfo[i].pubinsert =
(strcmp(PQgetvalue(res, i, i_pubinsert), "t") == 0);
pubinfo[i].pubupdate =
@@ -4509,8 +4513,12 @@ dumpPublication(Archive *fout, const PublicationInfo *pubinfo)
appendPQExpBuffer(query, "CREATE PUBLICATION %s",
qpubname);
- if (pubinfo->puballtables)
+ if (pubinfo->puballtables && pubinfo->puballsequences)
+ appendPQExpBufferStr(query, " FOR ALL TABLES, SEQUENCES");
+ else if (pubinfo->puballtables)
appendPQExpBufferStr(query, " FOR ALL TABLES");
+ else if (pubinfo->puballsequences)
+ appendPQExpBufferStr(query, " FOR ALL SEQUENCES");
appendPQExpBufferStr(query, " WITH (publish = '");
if (pubinfo->pubinsert)
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 7417eab6aef..1f9bd58a4e2 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -661,6 +661,7 @@ typedef struct _PublicationInfo
DumpableObject dobj;
const char *rolname;
bool puballtables;
+ bool puballsequences;
bool pubinsert;
bool pubupdate;
bool pubdelete;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 386e21e0c59..09a54e310ce 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -3273,6 +3273,28 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub5' => {
+ create_order => 50,
+ create_sql => 'CREATE PUBLICATION pub5
+ FOR ALL SEQUENCES
+ WITH (publish = \'\');',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub5 FOR ALL SEQUENCES WITH (publish = '');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
+ 'CREATE PUBLICATION pub6' => {
+ create_order => 50,
+ create_sql => 'CREATE PUBLICATION pub6
+ FOR ALL SEQUENCES, TABLES
+ WITH (publish = \'\');',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub6 FOR ALL TABLES, SEQUENCES WITH (publish = '');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 24e0100c9f0..5859443d0aa 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1757,28 +1757,19 @@ describeOneTableDetails(const char *schemaname,
if (tableinfo.relkind == RELKIND_SEQUENCE)
{
PGresult *result = NULL;
- printQueryOpt myopt = pset.popt;
- char *footers[2] = {NULL, NULL};
if (pset.sversion >= 100000)
{
printfPQExpBuffer(&buf,
- "SELECT pg_catalog.format_type(seqtypid, NULL) AS \"%s\",\n"
- " seqstart AS \"%s\",\n"
- " seqmin AS \"%s\",\n"
- " seqmax AS \"%s\",\n"
- " seqincrement AS \"%s\",\n"
- " CASE WHEN seqcycle THEN '%s' ELSE '%s' END AS \"%s\",\n"
- " seqcache AS \"%s\"\n",
- gettext_noop("Type"),
- gettext_noop("Start"),
- gettext_noop("Minimum"),
- gettext_noop("Maximum"),
- gettext_noop("Increment"),
+ "SELECT pg_catalog.format_type(seqtypid, NULL),\n"
+ " seqstart,\n"
+ " seqmin,\n"
+ " seqmax,\n"
+ " seqincrement,\n"
+ " CASE WHEN seqcycle THEN '%s' ELSE '%s' END,\n"
+ " seqcache\n",
gettext_noop("yes"),
- gettext_noop("no"),
- gettext_noop("Cycles?"),
- gettext_noop("Cache"));
+ gettext_noop("no"));
appendPQExpBuffer(&buf,
"FROM pg_catalog.pg_sequence\n"
"WHERE seqrelid = '%s';",
@@ -1787,22 +1778,15 @@ describeOneTableDetails(const char *schemaname,
else
{
printfPQExpBuffer(&buf,
- "SELECT 'bigint' AS \"%s\",\n"
- " start_value AS \"%s\",\n"
- " min_value AS \"%s\",\n"
- " max_value AS \"%s\",\n"
- " increment_by AS \"%s\",\n"
- " CASE WHEN is_cycled THEN '%s' ELSE '%s' END AS \"%s\",\n"
- " cache_value AS \"%s\"\n",
- gettext_noop("Type"),
- gettext_noop("Start"),
- gettext_noop("Minimum"),
- gettext_noop("Maximum"),
- gettext_noop("Increment"),
+ "SELECT 'bigint',\n"
+ " start_value,\n"
+ " min_value,\n"
+ " max_value,\n"
+ " increment_by,\n"
+ " CASE WHEN is_cycled THEN '%s' ELSE '%s' END,\n"
+ " cache_value\n",
gettext_noop("yes"),
- gettext_noop("no"),
- gettext_noop("Cycles?"),
- gettext_noop("Cache"));
+ gettext_noop("no"));
appendPQExpBuffer(&buf, "FROM %s", fmtId(schemaname));
/* must be separate because fmtId isn't reentrant */
appendPQExpBuffer(&buf, ".%s;", fmtId(relationname));
@@ -1812,6 +1796,59 @@ describeOneTableDetails(const char *schemaname,
if (!res)
goto error_return;
+ numrows = PQntuples(res);
+
+ /*
+ * XXX reset to use expanded output for sequences (maybe we should
+ * keep this disabled, just like for tables?)
+ */
+ myopt.expanded = pset.popt.topt.expanded;
+
+ printTableInit(&cont, &myopt, title.data, 7, numrows);
+ printTableInitialized = true;
+
+ if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
+ printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
+ schemaname, relationname);
+ else
+ printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
+ schemaname, relationname);
+
+ printTableAddHeader(&cont, gettext_noop("Type"), true, 'l');
+ printTableAddHeader(&cont, gettext_noop("Start"), true, 'r');
+ printTableAddHeader(&cont, gettext_noop("Minimum"), true, 'r');
+ printTableAddHeader(&cont, gettext_noop("Maximum"), true, 'r');
+ printTableAddHeader(&cont, gettext_noop("Increment"), true, 'r');
+ printTableAddHeader(&cont, gettext_noop("Cycles?"), true, 'l');
+ printTableAddHeader(&cont, gettext_noop("Cache"), true, 'r');
+
+ /* Generate table cells to be printed */
+ for (i = 0; i < numrows; i++)
+ {
+ /* Type */
+ printTableAddCell(&cont, PQgetvalue(res, i, 0), false, false);
+
+ /* Start */
+ printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
+
+ /* Minimum */
+ printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
+
+ /* Maximum */
+ printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
+
+ /* Increment */
+ printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
+
+ /* Cycles? */
+ printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
+
+ /* Cache */
+ printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
+ }
+
+ /* Footer information about a sequence */
+
/* Get the column that owns this sequence */
printfPQExpBuffer(&buf, "SELECT pg_catalog.quote_ident(nspname) || '.' ||"
"\n pg_catalog.quote_ident(relname) || '.' ||"
@@ -1843,32 +1880,53 @@ describeOneTableDetails(const char *schemaname,
switch (PQgetvalue(result, 0, 1)[0])
{
case 'a':
- footers[0] = psprintf(_("Owned by: %s"),
- PQgetvalue(result, 0, 0));
+ printTableAddFooter(&cont,
+ psprintf(_("Owned by: %s"),
+ PQgetvalue(result, 0, 0)));
break;
case 'i':
- footers[0] = psprintf(_("Sequence for identity column: %s"),
- PQgetvalue(result, 0, 0));
+ printTableAddFooter(&cont,
+ psprintf(_("Sequence for identity column: %s"),
+ PQgetvalue(result, 0, 0)));
break;
}
}
PQclear(result);
- if (tableinfo.relpersistence == RELPERSISTENCE_UNLOGGED)
- printfPQExpBuffer(&title, _("Unlogged sequence \"%s.%s\""),
- schemaname, relationname);
- else
- printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
- schemaname, relationname);
+ /* Print any publications */
+ if (pset.sversion >= 180000)
+ {
+ int tuples;
- myopt.footers = footers;
- myopt.topt.default_footer = false;
- myopt.title = title.data;
- myopt.translate_header = true;
+ printfPQExpBuffer(&buf,
+ "SELECT pubname\n"
+ "FROM pg_catalog.pg_publication p\n"
+ "WHERE p.puballsequences AND pg_catalog.pg_relation_is_publishable('%s')\n"
+ "ORDER BY 1;",
+ oid);
- printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ result = PSQLexec(buf.data);
+ if (!result)
+ goto error_return;
- free(footers[0]);
+ /* Might be an empty set - that's ok */
+ tuples = PQntuples(result);
+ if (tuples > 0)
+ {
+ printTableAddFooter(&cont, _("Publications:"));
+
+ for (i = 0; i < tuples; i++)
+ {
+ printfPQExpBuffer(&buf, " \"%s\"",
+ PQgetvalue(result, i, 0));
+
+ printTableAddFooter(&cont, buf.data);
+ }
+ }
+ PQclear(result);
+ }
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
retval = true;
goto error_return; /* not an error, just return early */
@@ -6397,7 +6455,7 @@ listPublications(const char *pattern)
PQExpBufferData buf;
PGresult *res;
printQueryOpt myopt = pset.popt;
- static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false};
+ static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false};
if (pset.sversion < 100000)
{
@@ -6414,13 +6472,20 @@ listPublications(const char *pattern)
printfPQExpBuffer(&buf,
"SELECT pubname AS \"%s\",\n"
" pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
- " puballtables AS \"%s\",\n"
- " pubinsert AS \"%s\",\n"
- " pubupdate AS \"%s\",\n"
- " pubdelete AS \"%s\"",
+ " puballtables AS \"%s\"",
gettext_noop("Name"),
gettext_noop("Owner"),
- gettext_noop("All tables"),
+ gettext_noop("All tables"));
+
+ if (pset.sversion >= 180000)
+ appendPQExpBuffer(&buf,
+ ",\n puballsequences AS \"%s\"",
+ gettext_noop("All sequences"));
+
+ appendPQExpBuffer(&buf,
+ ",\n pubinsert AS \"%s\",\n"
+ " pubupdate AS \"%s\",\n"
+ " pubdelete AS \"%s\"",
gettext_noop("Inserts"),
gettext_noop("Updates"),
gettext_noop("Deletes"));
@@ -6531,6 +6596,7 @@ describePublications(const char *pattern)
bool has_pubtruncate;
bool has_pubgencols;
bool has_pubviaroot;
+ bool has_pubsequence;
PQExpBufferData title;
printTableContent cont;
@@ -6545,6 +6611,7 @@ describePublications(const char *pattern)
return true;
}
+ has_pubsequence = (pset.sversion >= 180000);
has_pubtruncate = (pset.sversion >= 110000);
has_pubgencols = (pset.sversion >= 180000);
has_pubviaroot = (pset.sversion >= 130000);
@@ -6554,7 +6621,18 @@ describePublications(const char *pattern)
printfPQExpBuffer(&buf,
"SELECT oid, pubname,\n"
" pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
- " puballtables, pubinsert, pubupdate, pubdelete");
+ " puballtables");
+
+ if (has_pubsequence)
+ appendPQExpBufferStr(&buf,
+ ", puballsequences");
+ else
+ appendPQExpBufferStr(&buf,
+ ", false AS puballsequences");
+
+ appendPQExpBufferStr(&buf,
+ ", pubinsert, pubupdate, pubdelete");
+
if (has_pubtruncate)
appendPQExpBufferStr(&buf,
", pubtruncate");
@@ -6629,6 +6707,8 @@ describePublications(const char *pattern)
bool puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0;
printTableOpt myopt = pset.popt.topt;
+ if (has_pubsequence)
+ ncols++;
if (has_pubtruncate)
ncols++;
if (has_pubgencols)
@@ -6642,6 +6722,8 @@ describePublications(const char *pattern)
printTableAddHeader(&cont, gettext_noop("Owner"), true, align);
printTableAddHeader(&cont, gettext_noop("All tables"), true, align);
+ if (has_pubsequence)
+ printTableAddHeader(&cont, gettext_noop("All sequences"), true, align);
printTableAddHeader(&cont, gettext_noop("Inserts"), true, align);
printTableAddHeader(&cont, gettext_noop("Updates"), true, align);
printTableAddHeader(&cont, gettext_noop("Deletes"), true, align);
@@ -6654,15 +6736,17 @@ describePublications(const char *pattern)
printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
- printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
+ if (has_pubsequence)
+ printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
if (has_pubtruncate)
- printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
- if (has_pubgencols)
printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
- if (has_pubviaroot)
+ if (has_pubgencols)
printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false);
+ if (has_pubviaroot)
+ printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false);
if (!puballtables)
{
diff --git a/src/bin/psql/tab-complete.in.c b/src/bin/psql/tab-complete.in.c
index ec65ab79fec..3dc84074e63 100644
--- a/src/bin/psql/tab-complete.in.c
+++ b/src/bin/psql/tab-complete.in.c
@@ -3530,12 +3530,12 @@ match_previous_words(int pattern_id,
/* CREATE PUBLICATION */
else if (Matches("CREATE", "PUBLICATION", MatchAny))
- COMPLETE_WITH("FOR TABLE", "FOR ALL TABLES", "FOR TABLES IN SCHEMA", "WITH (");
+ COMPLETE_WITH("FOR TABLE", "FOR TABLES IN SCHEMA", "FOR ALL TABLES", "FOR ALL SEQUENCES", "WITH (");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR"))
- COMPLETE_WITH("TABLE", "ALL TABLES", "TABLES IN SCHEMA");
+ COMPLETE_WITH("TABLE", "TABLES IN SCHEMA", "ALL TABLES", "ALL SEQUENCES");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL"))
- COMPLETE_WITH("TABLES");
- else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES"))
+ COMPLETE_WITH("TABLES", "SEQUENCES");
+ else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "ALL", "TABLES|SEQUENCES"))
COMPLETE_WITH("WITH (");
else if (Matches("CREATE", "PUBLICATION", MatchAny, "FOR", "TABLES"))
COMPLETE_WITH("IN SCHEMA");
diff --git a/src/include/catalog/pg_publication.h b/src/include/catalog/pg_publication.h
index 48c7d1a8615..843fe784d64 100644
--- a/src/include/catalog/pg_publication.h
+++ b/src/include/catalog/pg_publication.h
@@ -40,6 +40,12 @@ CATALOG(pg_publication,6104,PublicationRelationId)
*/
bool puballtables;
+ /*
+ * indicates that this is special publication which should encompass all
+ * sequences in the database (except for the unlogged and temp ones)
+ */
+ bool puballsequences;
+
/* true if inserts are published */
bool pubinsert;
@@ -129,6 +135,7 @@ typedef struct Publication
Oid oid;
char *name;
bool alltables;
+ bool allsequences;
bool pubviaroot;
PublishGencolsType pubgencols_type;
PublicationActions pubactions;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index dd00ab420b8..03ec92d2098 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -4258,6 +4258,22 @@ typedef struct PublicationObjSpec
ParseLoc location; /* token location, or -1 if unknown */
} PublicationObjSpec;
+/*
+ * Publication types supported by FOR ALL ...
+ */
+typedef enum PublicationAllObjType
+{
+ PUBLICATION_ALL_TABLES,
+ PUBLICATION_ALL_SEQUENCES,
+} PublicationAllObjType;
+
+typedef struct PublicationAllObjSpec
+{
+ NodeTag type;
+ PublicationAllObjType pubobjtype; /* type of this publication object */
+ ParseLoc location; /* token location, or -1 if unknown */
+} PublicationAllObjSpec;
+
typedef struct CreatePublicationStmt
{
NodeTag type;
@@ -4265,6 +4281,8 @@ typedef struct CreatePublicationStmt
List *options; /* List of DefElem nodes */
List *pubobjects; /* Optional list of publication objects */
bool for_all_tables; /* Special publication for all tables in db */
+ bool for_all_sequences; /* Special publication for all sequences
+ * in db */
} CreatePublicationStmt;
typedef enum AlterPublicationAction
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index cf48ae6d0c2..fb05755449d 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -6443,9 +6443,9 @@ List of schemas
(0 rows)
\dRp "no.such.publication"
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
-------+-------+------------+---------+---------+---------+-----------+-------------------+----------
+ List of publications
+ Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+------+-------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
(0 rows)
\dRs "no.such.subscription"
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 4de96c04f9d..ffae6b5ded2 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -36,20 +36,20 @@ LINE 1: ...pub_xxx WITH (publish_generated_columns = stored, publish_ge...
CREATE PUBLICATION testpub_xxx WITH (publish_generated_columns = foo);
ERROR: publish_generated_columns requires a "none" or "stored" value
\dRp
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- testpub_default | regress_publication_user | f | f | t | f | f | none | f
- testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f
+ List of publications
+ Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ testpub_default | regress_publication_user | f | f | f | t | f | f | none | f
+ testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f
(2 rows)
ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
\dRp
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- testpub_default | regress_publication_user | f | t | t | t | f | none | f
- testpub_ins_trunct | regress_publication_user | f | t | f | f | f | none | f
+ List of publications
+ Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ testpub_default | regress_publication_user | f | f | t | t | t | f | none | f
+ testpub_ins_trunct | regress_publication_user | f | f | t | f | f | f | none | f
(2 rows)
--- adding tables
@@ -93,10 +93,10 @@ RESET client_min_messages;
-- should be able to add schema to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable ADD TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_tbl1"
Tables from schemas:
@@ -105,20 +105,20 @@ Tables from schemas:
-- should be able to drop schema from 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable DROP TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_tbl1"
-- should be able to set schema to 'FOR TABLE' publication
ALTER PUBLICATION testpub_fortable SET TABLES IN SCHEMA pub_test;
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test"
@@ -129,10 +129,10 @@ CREATE PUBLICATION testpub_forschema FOR TABLES IN SCHEMA pub_test;
CREATE PUBLICATION testpub_for_tbl_schema FOR TABLES IN SCHEMA pub_test, TABLE pub_test.testpub_nopk;
RESET client_min_messages;
\dRp+ testpub_for_tbl_schema
- Publication testpub_for_tbl_schema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_for_tbl_schema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test.testpub_nopk"
Tables from schemas:
@@ -150,10 +150,10 @@ LINE 1: ...CATION testpub_parsertst FOR TABLES IN SCHEMA foo, test.foo;
-- should be able to add a table of the same schema to the schema publication
ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test.testpub_nopk"
Tables from schemas:
@@ -162,10 +162,10 @@ Tables from schemas:
-- should be able to drop the table
ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test"
@@ -176,10 +176,10 @@ ERROR: relation "testpub_nopk" is not part of the publication
-- should be able to set table to schema publication
ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk;
\dRp+ testpub_forschema
- Publication testpub_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test.testpub_nopk"
@@ -203,10 +203,10 @@ Not-null constraints:
"testpub_tbl2_id_not_null" NOT NULL "id"
\dRp+ testpub_foralltables
- Publication testpub_foralltables
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | t | t | t | f | f | none | f
+ Publication testpub_foralltables
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | f | f | none | f
(1 row)
DROP TABLE testpub_tbl2;
@@ -218,24 +218,96 @@ CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
RESET client_min_messages;
\dRp+ testpub3
- Publication testpub3
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub3
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_tbl3"
"public.testpub_tbl3a"
\dRp+ testpub4
- Publication testpub4
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub4
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_tbl3"
DROP TABLE testpub_tbl3, testpub_tbl3a;
DROP PUBLICATION testpub3, testpub4;
+--- Tests for publications with SEQUENCES
+CREATE SEQUENCE regress_pub_seq0;
+CREATE SEQUENCE pub_test.regress_pub_seq1;
+-- FOR ALL SEQUENCES
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES;
+RESET client_min_messages;
+SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1';
+ pubname | puballtables | puballsequences
+------------------------------+--------------+-----------------
+ regress_pub_forallsequences1 | f | t
+(1 row)
+
+\d+ regress_pub_seq0
+ Sequence "public.regress_pub_seq0"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+--------+-------+---------+---------------------+-----------+---------+-------
+ bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
+Publications:
+ "regress_pub_forallsequences1"
+
+\dRp+ regress_pub_forallsequences1
+ Publication regress_pub_forallsequences1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | t | t | t | t | t | none | f
+(1 row)
+
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES;
+RESET client_min_messages;
+-- check that describe sequence lists both publications the sequence belongs to
+\d+ pub_test.regress_pub_seq1
+ Sequence "pub_test.regress_pub_seq1"
+ Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
+--------+-------+---------+---------------------+-----------+---------+-------
+ bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
+Publications:
+ "regress_pub_forallsequences1"
+ "regress_pub_forallsequences2"
+
+--- Specifying both ALL TABLES and ALL SEQUENCES
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES;
+RESET client_min_messages;
+SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables';
+ pubname | puballtables | puballsequences
+----------------------------------------+--------------+-----------------
+ regress_pub_for_allsequences_alltables | t | t
+(1 row)
+
+\dRp+ regress_pub_for_allsequences_alltables
+ Publication regress_pub_for_allsequences_alltables
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | t | t | t | t | t | none | f
+(1 row)
+
+DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1;
+DROP PUBLICATION regress_pub_forallsequences1, regress_pub_forallsequences2, regress_pub_for_allsequences_alltables;
+-- fail - Specifying ALL TABLES more than once
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES, TABLES;
+ERROR: invalid publication object list
+LINE 1: ...or_allsequences_alltables FOR ALL SEQUENCES, TABLES, TABLES;
+ ^
+DETAIL: ALL TABLES can be specified only once.
+-- fail - Specifying ALL SEQUENCES more than once
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES, SEQUENCES;
+ERROR: invalid publication object list
+LINE 1: ...allsequences_alltables FOR ALL SEQUENCES, TABLES, SEQUENCES;
+ ^
+DETAIL: ALL SEQUENCES can be specified only once.
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
@@ -251,10 +323,10 @@ UPDATE testpub_parted1 SET a = 1;
-- only parent is listed as being in publication, not the partition
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
\dRp+ testpub_forparted
- Publication testpub_forparted
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_forparted
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_parted"
@@ -269,10 +341,10 @@ ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1;
UPDATE testpub_parted1 SET a = 1;
ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true);
\dRp+ testpub_forparted
- Publication testpub_forparted
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | t
+ Publication testpub_forparted
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | t
Tables:
"public.testpub_parted"
@@ -301,10 +373,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub5 FOR TABLE testpub_rf_tbl1, testpub_rf_tbl2 WHERE (c <> 'test' AND d < 5) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
@@ -317,10 +389,10 @@ Tables:
ALTER PUBLICATION testpub5 ADD TABLE testpub_rf_tbl3 WHERE (e > 1000 AND e < 2000);
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl2" WHERE ((c <> 'test'::text) AND (d < 5))
@@ -336,10 +408,10 @@ Publications:
ALTER PUBLICATION testpub5 DROP TABLE testpub_rf_tbl2;
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl3" WHERE ((e > 1000) AND (e < 2000))
@@ -347,10 +419,10 @@ Tables:
-- remove testpub_rf_tbl1 and add testpub_rf_tbl3 again (another WHERE expression)
ALTER PUBLICATION testpub5 SET TABLE testpub_rf_tbl3 WHERE (e > 300 AND e < 500);
\dRp+ testpub5
- Publication testpub5
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub5
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl3" WHERE ((e > 300) AND (e < 500))
@@ -383,10 +455,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_syntax1 FOR TABLE testpub_rf_tbl1, ONLY testpub_rf_tbl3 WHERE (e < 999) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub_syntax1
- Publication testpub_syntax1
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub_syntax1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl1"
"public.testpub_rf_tbl3" WHERE (e < 999)
@@ -396,10 +468,10 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_syntax2 FOR TABLE testpub_rf_tbl1, testpub_rf_schema1.testpub_rf_tbl5 WHERE (h < 999) WITH (publish = 'insert');
RESET client_min_messages;
\dRp+ testpub_syntax2
- Publication testpub_syntax2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | f | none | f
+ Publication testpub_syntax2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | f | none | f
Tables:
"public.testpub_rf_tbl1"
"testpub_rf_schema1.testpub_rf_tbl5" WHERE (h < 999)
@@ -514,10 +586,10 @@ CREATE PUBLICATION testpub6 FOR TABLES IN SCHEMA testpub_rf_schema2;
ALTER PUBLICATION testpub6 SET TABLES IN SCHEMA testpub_rf_schema2, TABLE testpub_rf_schema2.testpub_rf_tbl6 WHERE (i < 99);
RESET client_min_messages;
\dRp+ testpub6
- Publication testpub6
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub6
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"testpub_rf_schema2.testpub_rf_tbl6" WHERE (i < 99)
Tables from schemas:
@@ -803,10 +875,10 @@ CREATE PUBLICATION testpub_table_ins WITH (publish = 'insert, truncate');
RESET client_min_messages;
ALTER PUBLICATION testpub_table_ins ADD TABLE testpub_tbl5 (a); -- ok
\dRp+ testpub_table_ins
- Publication testpub_table_ins
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | f | f | t | none | f
+ Publication testpub_table_ins
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | f | f | t | none | f
Tables:
"public.testpub_tbl5" (a)
@@ -996,10 +1068,10 @@ CREATE TABLE testpub_tbl_both_filters (a int, b int, c int, PRIMARY KEY (a,c));
ALTER TABLE testpub_tbl_both_filters REPLICA IDENTITY USING INDEX testpub_tbl_both_filters_pkey;
ALTER PUBLICATION testpub_both_filters ADD TABLE testpub_tbl_both_filters (a,c) WHERE (c != 1);
\dRp+ testpub_both_filters
- Publication testpub_both_filters
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_both_filters
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.testpub_tbl_both_filters" (a, c) WHERE (c <> 1)
@@ -1207,10 +1279,10 @@ ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" already exists
\dRp+ testpub_fortbl
- Publication testpub_fortbl
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortbl
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
@@ -1250,10 +1322,10 @@ Not-null constraints:
"testpub_tbl1_id_not_null" NOT NULL "id"
\dRp+ testpub_default
- Publication testpub_default
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | f | none | f
+ Publication testpub_default
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | f | none | f
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
@@ -1333,10 +1405,10 @@ REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
DROP TABLE testpub_parted;
DROP TABLE testpub_tbl1;
\dRp+ testpub_default
- Publication testpub_default
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | f | none | f
+ Publication testpub_default
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | f | none | f
(1 row)
-- fail - must be owner of publication
@@ -1346,20 +1418,20 @@ ERROR: must be owner of publication testpub_default
RESET ROLE;
ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
\dRp testpub_foo
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
--------------+--------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- testpub_foo | regress_publication_user | f | t | t | t | f | none | f
+ List of publications
+ Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+-------------+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ testpub_foo | regress_publication_user | f | f | t | t | t | f | none | f
(1 row)
-- rename back to keep the rest simple
ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
- List of publications
- Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
------------------+---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- testpub_default | regress_publication_user2 | f | t | t | t | f | none | f
+ List of publications
+ Name | Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+-----------------+---------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ testpub_default | regress_publication_user2 | f | f | t | t | t | f | none | f
(1 row)
-- adding schemas and tables
@@ -1375,19 +1447,19 @@ CREATE TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA"(id int);
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub1_forschema FOR TABLES IN SCHEMA pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
CREATE PUBLICATION testpub2_forschema FOR TABLES IN SCHEMA pub_test1, pub_test2, pub_test3;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub2_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1401,44 +1473,44 @@ CREATE PUBLICATION testpub6_forschema FOR TABLES IN SCHEMA "CURRENT_SCHEMA", CUR
CREATE PUBLICATION testpub_fortable FOR TABLE "CURRENT_SCHEMA"."CURRENT_SCHEMA";
RESET client_min_messages;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub3_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"public"
\dRp+ testpub4_forschema
- Publication testpub4_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub4_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"CURRENT_SCHEMA"
\dRp+ testpub5_forschema
- Publication testpub5_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub5_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"CURRENT_SCHEMA"
"public"
\dRp+ testpub6_forschema
- Publication testpub6_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub6_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"CURRENT_SCHEMA"
"public"
\dRp+ testpub_fortable
- Publication testpub_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"CURRENT_SCHEMA.CURRENT_SCHEMA"
@@ -1453,7 +1525,7 @@ CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA;
ERROR: invalid publication object list
LINE 1: CREATE PUBLICATION testpub_forschema1 FOR CURRENT_SCHEMA;
^
-DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name.
+DETAIL: One of TABLE, TABLES IN SCHEMA or ALL SEQUENCES must be specified before a standalone table or schema name.
-- check create publication on CURRENT_SCHEMA along with FOR TABLE
CREATE PUBLICATION testpub_forschema1 FOR TABLE CURRENT_SCHEMA;
ERROR: syntax error at or near "CURRENT_SCHEMA"
@@ -1472,10 +1544,10 @@ ERROR: schema "testpub_view" does not exist
-- dropping the schema should reflect the change in publication
DROP SCHEMA pub_test3;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub2_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1483,20 +1555,20 @@ Tables from schemas:
-- renaming the schema should reflect the change in publication
ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub2_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1_renamed"
"pub_test2"
ALTER SCHEMA pub_test1_renamed RENAME to pub_test1;
\dRp+ testpub2_forschema
- Publication testpub2_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub2_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1504,10 +1576,10 @@ Tables from schemas:
-- alter publication add schema
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1516,10 +1588,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1528,10 +1600,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema ADD TABLES IN SCHEMA pub_test1;
ERROR: schema "pub_test1" is already member of publication "testpub1_forschema"
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1539,10 +1611,10 @@ Tables from schemas:
-- alter publication drop schema
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
@@ -1550,10 +1622,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test2;
ERROR: tables from schema "pub_test2" are not part of the publication
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
@@ -1561,29 +1633,29 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
-- drop all schemas
ALTER PUBLICATION testpub1_forschema DROP TABLES IN SCHEMA pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
-- alter publication set multiple schema
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test2;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1592,10 +1664,10 @@ Tables from schemas:
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA non_existent_schema;
ERROR: schema "non_existent_schema" does not exist
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
"pub_test2"
@@ -1604,10 +1676,10 @@ Tables from schemas:
-- removing the duplicate schemas
ALTER PUBLICATION testpub1_forschema SET TABLES IN SCHEMA pub_test1, pub_test1;
\dRp+ testpub1_forschema
- Publication testpub1_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub1_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
@@ -1686,18 +1758,18 @@ SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub3_forschema;
RESET client_min_messages;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub3_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
(1 row)
ALTER PUBLICATION testpub3_forschema SET TABLES IN SCHEMA pub_test1;
\dRp+ testpub3_forschema
- Publication testpub3_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub3_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables from schemas:
"pub_test1"
@@ -1707,20 +1779,20 @@ CREATE PUBLICATION testpub_forschema_fortable FOR TABLES IN SCHEMA pub_test1, TA
CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, TABLES IN SCHEMA pub_test1;
RESET client_min_messages;
\dRp+ testpub_forschema_fortable
- Publication testpub_forschema_fortable
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_forschema_fortable
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test2.tbl1"
Tables from schemas:
"pub_test1"
\dRp+ testpub_fortable_forschema
- Publication testpub_fortable_forschema
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication testpub_fortable_forschema
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"pub_test2.tbl1"
Tables from schemas:
@@ -1732,7 +1804,7 @@ CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
ERROR: invalid publication object list
LINE 1: CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
^
-DETAIL: One of TABLE or TABLES IN SCHEMA must be specified before a standalone table or schema name.
+DETAIL: One of TABLE, TABLES IN SCHEMA or ALL SEQUENCES must be specified before a standalone table or schema name.
DROP VIEW testpub_view;
DROP PUBLICATION testpub_default;
DROP PUBLICATION testpub_ins_trunct;
@@ -1842,26 +1914,26 @@ DROP SCHEMA sch2 cascade;
SET client_min_messages = 'ERROR';
CREATE PUBLICATION pub1 FOR ALL TABLES WITH (publish_generated_columns = stored);
\dRp+ pub1
- Publication pub1
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | t | t | t | t | t | stored | f
+ Publication pub1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | stored | f
(1 row)
CREATE PUBLICATION pub2 FOR ALL TABLES WITH (publish_generated_columns = none);
\dRp+ pub2
- Publication pub2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | t | t | t | t | t | none | f
+ Publication pub2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | none | f
(1 row)
CREATE PUBLICATION pub3 FOR ALL TABLES WITH (publish_generated_columns);
\dRp+ pub3
- Publication pub3
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | t | t | t | t | t | stored | f
+ Publication pub3
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | t | f | t | t | t | t | stored | f
(1 row)
DROP PUBLICATION pub1;
@@ -1873,50 +1945,50 @@ CREATE TABLE gencols (a int, gen1 int GENERATED ALWAYS AS (a * 2) STORED);
-- Generated columns in column list, when 'publish_generated_columns'='none'
CREATE PUBLICATION pub1 FOR table gencols(a, gen1) WITH (publish_generated_columns = none);
\dRp+ pub1
- Publication pub1
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication pub1
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.gencols" (a, gen1)
-- Generated columns in column list, when 'publish_generated_columns'='stored'
CREATE PUBLICATION pub2 FOR table gencols(a, gen1) WITH (publish_generated_columns = stored);
\dRp+ pub2
- Publication pub2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | stored | f
+ Publication pub2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | stored | f
Tables:
"public.gencols" (a, gen1)
-- Generated columns in column list, then set 'publish_generated_columns'='none'
ALTER PUBLICATION pub2 SET (publish_generated_columns = none);
\dRp+ pub2
- Publication pub2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication pub2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.gencols" (a, gen1)
-- Remove generated columns from column list, when 'publish_generated_columns'='none'
ALTER PUBLICATION pub2 SET TABLE gencols(a);
\dRp+ pub2
- Publication pub2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication pub2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.gencols" (a)
-- Add generated columns in column list, when 'publish_generated_columns'='none'
ALTER PUBLICATION pub2 SET TABLE gencols(a, gen1);
\dRp+ pub2
- Publication pub2
- Owner | All tables | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
---------------------------+------------+---------+---------+---------+-----------+-------------------+----------
- regress_publication_user | f | t | t | t | t | none | f
+ Publication pub2
+ Owner | All tables | All sequences | Inserts | Updates | Deletes | Truncates | Generated columns | Via root
+--------------------------+------------+---------------+---------+---------+---------+-----------+-------------------+----------
+ regress_publication_user | f | f | t | t | t | t | none | f
Tables:
"public.gencols" (a, gen1)
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index 68001de4000..1f7b7ebe4d5 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -119,6 +119,43 @@ RESET client_min_messages;
DROP TABLE testpub_tbl3, testpub_tbl3a;
DROP PUBLICATION testpub3, testpub4;
+--- Tests for publications with SEQUENCES
+CREATE SEQUENCE regress_pub_seq0;
+CREATE SEQUENCE pub_test.regress_pub_seq1;
+
+-- FOR ALL SEQUENCES
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences1 FOR ALL SEQUENCES;
+RESET client_min_messages;
+
+SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_forallsequences1';
+\d+ regress_pub_seq0
+\dRp+ regress_pub_forallsequences1
+
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_forallsequences2 FOR ALL SEQUENCES;
+RESET client_min_messages;
+
+-- check that describe sequence lists both publications the sequence belongs to
+\d+ pub_test.regress_pub_seq1
+
+--- Specifying both ALL TABLES and ALL SEQUENCES
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES;
+RESET client_min_messages;
+
+SELECT pubname, puballtables, puballsequences FROM pg_publication WHERE pubname = 'regress_pub_for_allsequences_alltables';
+\dRp+ regress_pub_for_allsequences_alltables
+
+DROP SEQUENCE regress_pub_seq0, pub_test.regress_pub_seq1;
+DROP PUBLICATION regress_pub_forallsequences1, regress_pub_forallsequences2, regress_pub_for_allsequences_alltables;
+
+-- fail - Specifying ALL TABLES more than once
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES, TABLES;
+
+-- fail - Specifying ALL SEQUENCES more than once
+CREATE PUBLICATION regress_pub_for_allsequences_alltables FOR ALL SEQUENCES, TABLES, SEQUENCES;
+
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index a8346cda633..2351d9644f7 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -2339,6 +2339,8 @@ PsqlScanStateData
PsqlSettings
Publication
PublicationActions
+PublicationAllObjSpec
+PublicationAllObjType
PublicationDesc
PublicationInfo
PublicationObjSpec
--
2.34.1