v34-0005-Address-review-comments-for-conflict-log-table-p.patch
application/octet-stream
Filename: v34-0005-Address-review-comments-for-conflict-log-table-p.patch
Type: application/octet-stream
Part: 4
From 51f7457973a2fcc62747bd6b0ca0d336d5329f3f Mon Sep 17 00:00:00 2001
From: Vignesh C <vignesh21@gmail.com>
Date: Mon, 11 May 2026 11:30:27 +0530
Subject: [PATCH v34 5/5] Address review comments for conflict log table patch
This patch addresses the review comments for the conflict log table at:
https://www.postgresql.org/message-id/CAFiTN-sdcjf9xJ2M-%3Dab5e4y662tTmFFiP4gHL44tC9PcQozcw%40mail.gmail.com
https://www.postgresql.org/message-id/CAJpy0uANkzTyUjO2W0%3DRtaJCGg%3DVYcwLGGCpqax%3DzKJgNbB0Hw%40mail.gmail.com
https://www.postgresql.org/message-id/CAJpy0uCdxiAVsVr95S%3D6vngxPGGFaSCoSyzb0B9DdF9oJucsfQ%40mail.gmail.com
https://www.postgresql.org/message-id/CANhcyEWRT-8fUzfKGFvGcZCQmjM4mMbpbVgCOSsGC3R5guHHCw%40mail.gmail.com
---
src/backend/catalog/aclchk.c | 11 +-
src/backend/commands/subscriptioncmds.c | 39 +-
src/bin/initdb/initdb.c | 5 +
src/bin/psql/command.c | 5 +-
src/bin/psql/describe.c | 405 ++++++++++++++++-----
src/bin/psql/describe.h | 5 +-
src/include/catalog/pg_subscription.h | 16 +-
src/test/regress/expected/subscription.out | 220 ++++++-----
src/test/regress/sql/subscription.sql | 41 ++-
9 files changed, 533 insertions(+), 214 deletions(-)
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 84ef5304e22..ddaec84cc1f 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3345,14 +3345,17 @@ pg_class_aclmask_ext(Oid table_oid, Oid roleid, AclMode mask,
* protected in this way. Assume the view rules can take care of
* themselves. ACL_USAGE is if we ever have system sequences.
*
- * For conflict log tables, we allow non-superusers to perform DELETE
- * and TRUNCATE for maintenance, while still restricting INSERT,
- * UPDATE, and USAGE.
+ * For conflict log tables, allow non-superusers to perform DELETE and
+ * TRUNCATE for cleanup and maintenance. Also allow INSERT and UPDATE
+ * to pass ACL checks so that later checks can raise the dedicated
+ * "cannot modify or insert data into conflict log table" error instead
+ * of a generic permission denied error. Still restrict USAGE for
+ * non-superusers.
*/
if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE | ACL_USAGE)) &&
IsConflictClass(classForm) &&
!superuser_arg(roleid))
- mask &= ~(ACL_INSERT | ACL_UPDATE | ACL_USAGE);
+ mask &= ~(ACL_USAGE);
else if ((mask & (ACL_INSERT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE | ACL_USAGE)) &&
IsSystemClass(table_oid, classForm) &&
classForm->relkind != RELKIND_VIEW &&
diff --git a/src/backend/commands/subscriptioncmds.c b/src/backend/commands/subscriptioncmds.c
index c22af9be3fd..2d6f975e3bb 100644
--- a/src/backend/commands/subscriptioncmds.c
+++ b/src/backend/commands/subscriptioncmds.c
@@ -2474,18 +2474,27 @@ DropSubscription(DropSubscriptionStmt *stmt, bool isTopLevel)
deleteDependencyRecordsFor(SubscriptionRelationId, subid, false);
deleteSharedDependencyRecordsFor(SubscriptionRelationId, subid, 0);
- /*
- * Conflict log tables are recorded as internal dependencies of the
- * subscription. We must drop the dependent objects before the
- * subscription itself is removed. By using
- * PERFORM_DELETION_SKIP_ORIGINAL, we ensure that only the conflict log
- * table is reaped while the subscription remains for the final deletion
- * step.
- */
- ObjectAddressSet(object, SubscriptionRelationId, subid);
- performDeletion(&object, DROP_CASCADE,
- PERFORM_DELETION_INTERNAL |
- PERFORM_DELETION_SKIP_ORIGINAL);
+ if (OidIsValid(form->subconflictlogrelid))
+ {
+ char *conflictrelname = get_rel_name(form->subconflictlogrelid);
+ /*
+ * Conflict log tables are recorded as internal dependencies of the
+ * subscription. We must drop the dependent objects before the
+ * subscription itself is removed. By using
+ * PERFORM_DELETION_SKIP_ORIGINAL, we ensure that only the conflict log
+ * table is reaped while the subscription remains for the final
+ * deletion step.
+ */
+ ObjectAddressSet(object, SubscriptionRelationId, subid);
+ performDeletion(&object, DROP_CASCADE,
+ PERFORM_DELETION_INTERNAL |
+ PERFORM_DELETION_SKIP_ORIGINAL);
+
+ ereport(NOTICE,
+ errmsg("dropped conflict log table \"%s\" for subscription \"%s\"",
+ get_qualified_objname(PG_CONFLICT_NAMESPACE, conflictrelname),
+ subname));
+ }
/* Remove any associated relation synchronization states. */
RemoveSubscriptionRel(subid, InvalidOid);
@@ -3587,9 +3596,10 @@ create_conflict_log_table(Oid subid, char *subname, Oid subowner)
snprintf(relname, NAMEDATALEN, "pg_conflict_log_%u", subid);
/*
- * Check for an existing table with the sname name in the pg_conflict namespace.
+ * Check for an existing table with the same name in the pg_conflict namespace.
* A collision should not occur under normal operation, but we must handle cases
- * where a table has been created manually.
+ * where a table has been created manually when allow_system_tables_mods is
+ * ON.
*/
if (OidIsValid(get_relname_relid(relname, PG_CONFLICT_NAMESPACE)))
ereport(ERROR,
@@ -3623,6 +3633,7 @@ create_conflict_log_table(Oid subid, char *subname, Oid subowner)
true, /* is_internal */
InvalidOid, /* relrewrite */
NULL); /* typaddress */
+ Assert(relid != InvalidOid);
/*
* Establish an internal dependency between the conflict log table and
diff --git a/src/bin/initdb/initdb.c b/src/bin/initdb/initdb.c
index cda05676a79..803ca4112d4 100644
--- a/src/bin/initdb/initdb.c
+++ b/src/bin/initdb/initdb.c
@@ -1839,6 +1839,11 @@ setup_privileges(FILE *cmdfd)
" AND relacl IS NULL;\n\n",
escape_quotes(username));
PG_CMD_PUTS("GRANT USAGE ON SCHEMA pg_catalog, public TO PUBLIC;\n\n");
+
+ /*
+ * Allow non-superuser subscription owners to access their associated
+ * conflict log tables in the pg_conflict schema.
+ */
PG_CMD_PUTS("GRANT USAGE ON SCHEMA pg_conflict TO PUBLIC;\n\n");
PG_CMD_PUTS("REVOKE ALL ON pg_largeobject FROM PUBLIC;\n\n");
PG_CMD_PUTS("INSERT INTO pg_init_privs "
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
index 01b8f11aadd..777d0553246 100644
--- a/src/bin/psql/command.c
+++ b/src/bin/psql/command.c
@@ -1220,7 +1220,10 @@ exec_command_d(PsqlScanState scan_state, bool active_branch, const char *cmd)
success = listPublications(pattern);
break;
case 's':
- success = describeSubscriptions(pattern, show_verbose);
+ if (show_verbose)
+ success = describeSubscriptions(pattern);
+ else
+ success = listSubscriptions(pattern);
break;
default:
status = PSQL_CMD_UNKNOWN;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 95f78071969..e495956897e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -7081,25 +7081,23 @@ error_return:
/*
* \dRs
- * Describes subscriptions.
+ * Lists subscriptions.
*
* Takes an optional regexp to select particular subscriptions
*/
bool
-describeSubscriptions(const char *pattern, bool verbose)
+listSubscriptions(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, false, false, false, false, false,
- false, false, false, false, false, false, false, false, false};
+ static const bool translate_columns[] = {false, false, false, false};
if (pset.sversion < 100000)
{
char sverbuf[32];
- pg_log_error("The server (version %s) does not support subscriptions.",
+ pg_log_error("The server (version %s) does not support publications.",
formatPGVersionNumber(pset.sversion, false,
sverbuf, sizeof(sverbuf)));
return true;
@@ -7118,103 +7116,195 @@ describeSubscriptions(const char *pattern, bool verbose)
gettext_noop("Enabled"),
gettext_noop("Publication"));
- if (verbose)
+ /* Only display subscriptions in current database. */
+ appendPQExpBufferStr(&buf,
+ "FROM pg_catalog.pg_subscription\n"
+ "WHERE subdbid = (SELECT oid\n"
+ " FROM pg_catalog.pg_database\n"
+ " WHERE datname = pg_catalog.current_database())");
+
+ if (!validateSQLNamePattern(&buf, pattern, true, false,
+ NULL, "subname", NULL,
+ NULL,
+ NULL, 1))
{
- /* Binary mode and streaming are only supported in v14 and higher */
- if (pset.sversion >= 140000)
- {
- appendPQExpBuffer(&buf,
- ", subbinary AS \"%s\"\n",
- gettext_noop("Binary"));
+ termPQExpBuffer(&buf);
+ return false;
+ }
- if (pset.sversion >= 160000)
- appendPQExpBuffer(&buf,
- ", (CASE substream\n"
- " WHEN " CppAsString2(LOGICALREP_STREAM_OFF) " THEN 'off'\n"
- " WHEN " CppAsString2(LOGICALREP_STREAM_ON) " THEN 'on'\n"
- " WHEN " CppAsString2(LOGICALREP_STREAM_PARALLEL) " THEN 'parallel'\n"
- " END) AS \"%s\"\n",
- gettext_noop("Streaming"));
- else
- appendPQExpBuffer(&buf,
- ", substream AS \"%s\"\n",
- gettext_noop("Streaming"));
- }
+ appendPQExpBufferStr(&buf, "ORDER BY 1;");
- /* Two_phase and disable_on_error are only supported in v15 and higher */
- if (pset.sversion >= 150000)
- appendPQExpBuffer(&buf,
- ", subtwophasestate AS \"%s\"\n"
- ", subdisableonerr AS \"%s\"\n",
- gettext_noop("Two-phase commit"),
- gettext_noop("Disable on error"));
+ res = PSQLexec(buf.data);
+ termPQExpBuffer(&buf);
+ if (!res)
+ return false;
- if (pset.sversion >= 160000)
- appendPQExpBuffer(&buf,
- ", suborigin AS \"%s\"\n"
- ", subpasswordrequired AS \"%s\"\n"
- ", subrunasowner AS \"%s\"\n",
- gettext_noop("Origin"),
- gettext_noop("Password required"),
- gettext_noop("Run as owner?"));
+ myopt.title = _("List of subscriptions");
+ myopt.translate_header = true;
+ myopt.translate_columns = translate_columns;
+ myopt.n_translate_columns = lengthof(translate_columns);
- if (pset.sversion >= 170000)
+ printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+
+ PQclear(res);
+
+ return true;
+}
+
+/*
+ * \dRs+
+ * Describes subscriptions.
+ *
+ * Takes an optional regexp to select particular subscriptions
+ */
+bool
+describeSubscriptions(const char *pattern)
+{
+ PQExpBufferData buf;
+ int i;
+ PGresult *res;
+ int ncols;
+ int nrows = 1;
+
+ PQExpBufferData title;
+ printTableContent cont;
+
+ if (pset.sversion < 100000)
+ {
+ char sverbuf[32];
+
+ pg_log_error("The server (version %s) does not support subscriptions.",
+ formatPGVersionNumber(pset.sversion, false,
+ sverbuf, sizeof(sverbuf)));
+ return true;
+ }
+
+ initPQExpBuffer(&buf);
+
+ printfPQExpBuffer(&buf, "/* %s */\n", _("Get matching subscriptions"));
+ appendPQExpBuffer(&buf,
+ "SELECT oid, subname AS \"%s\"\n"
+ ", pg_catalog.pg_get_userbyid(subowner) AS \"%s\"\n"
+ ", subenabled AS \"%s\"\n"
+ ", subpublications AS \"%s\"\n",
+ gettext_noop("Name"),
+ gettext_noop("Owner"),
+ gettext_noop("Enabled"),
+ gettext_noop("Publication"));
+ ncols = 3;
+
+ /* Binary mode and streaming are only supported in v14 and higher */
+ if (pset.sversion >= 140000)
+ {
+ appendPQExpBuffer(&buf,
+ ", subbinary AS \"%s\"\n",
+ gettext_noop("Binary"));
+ ncols++;
+
+ if (pset.sversion >= 160000)
appendPQExpBuffer(&buf,
- ", subfailover AS \"%s\"\n",
- gettext_noop("Failover"));
- if (pset.sversion >= 190000)
- {
+ ", (CASE substream\n"
+ " WHEN " CppAsString2(LOGICALREP_STREAM_OFF) " THEN 'off'\n"
+ " WHEN " CppAsString2(LOGICALREP_STREAM_ON) " THEN 'on'\n"
+ " WHEN " CppAsString2(LOGICALREP_STREAM_PARALLEL) " THEN 'parallel'\n"
+ " END) AS \"%s\"\n",
+ gettext_noop("Streaming"));
+ else
appendPQExpBuffer(&buf,
- ", (select srvname from pg_foreign_server where oid=subserver) AS \"%s\"\n",
- gettext_noop("Server"));
+ ", substream AS \"%s\"\n",
+ gettext_noop("Streaming"));
- appendPQExpBuffer(&buf,
- ", subretaindeadtuples AS \"%s\"\n",
- gettext_noop("Retain dead tuples"));
+ ncols++;
+ }
- appendPQExpBuffer(&buf,
- ", submaxretention AS \"%s\"\n",
- gettext_noop("Max retention duration"));
+ /* Two_phase and disable_on_error are only supported in v15 and higher */
+ if (pset.sversion >= 150000)
+ {
+ appendPQExpBuffer(&buf,
+ ", subtwophasestate AS \"%s\"\n"
+ ", subdisableonerr AS \"%s\"\n",
+ gettext_noop("Two-phase commit"),
+ gettext_noop("Disable on error"));
+ ncols += 2;
+ }
- appendPQExpBuffer(&buf,
- ", subretentionactive AS \"%s\"\n",
- gettext_noop("Retention active"));
- }
+ if (pset.sversion >= 160000)
+ {
+ appendPQExpBuffer(&buf,
+ ", suborigin AS \"%s\"\n"
+ ", subpasswordrequired AS \"%s\"\n"
+ ", subrunasowner AS \"%s\"\n",
+ gettext_noop("Origin"),
+ gettext_noop("Password required"),
+ gettext_noop("Run as owner?"));
+ ncols += 3;
+ }
+ if (pset.sversion >= 170000)
+ {
appendPQExpBuffer(&buf,
- ", subsynccommit AS \"%s\"\n"
- ", subconninfo AS \"%s\"\n",
- gettext_noop("Synchronous commit"),
- gettext_noop("Conninfo"));
+ ", subfailover AS \"%s\"\n",
+ gettext_noop("Failover"));
+ ncols++;
+ }
- if (pset.sversion >= 190000)
- appendPQExpBuffer(&buf,
- ", subwalrcvtimeout AS \"%s\"\n",
- gettext_noop("Receiver timeout"));
+ if (pset.sversion >= 190000)
+ {
+ appendPQExpBuffer(&buf,
+ ", (select srvname from pg_foreign_server where oid=subserver) AS \"%s\"\n",
+ gettext_noop("Server"));
- /* Skip LSN is only supported in v15 and higher */
- if (pset.sversion >= 150000)
- appendPQExpBuffer(&buf,
- ", subskiplsn AS \"%s\"\n",
- gettext_noop("Skip LSN"));
+ appendPQExpBuffer(&buf,
+ ", subretaindeadtuples AS \"%s\"\n",
+ gettext_noop("Retain dead tuples"));
appendPQExpBuffer(&buf,
- ", pg_catalog.obj_description(oid, 'pg_subscription') AS \"%s\"\n",
- gettext_noop("Description"));
+ ", submaxretention AS \"%s\"\n",
+ gettext_noop("Max retention duration"));
- /* Conflict log destination is supported in v19 and higher */
- if (pset.sversion >= 190000)
- {
- appendPQExpBuffer(&buf,
- ", subconflictlogdest AS \"%s\"\n",
- gettext_noop("Conflict log destination"));
+ appendPQExpBuffer(&buf,
+ ", subretentionactive AS \"%s\"\n",
+ gettext_noop("Retention active"));
- appendPQExpBuffer(&buf,
- ", (CASE WHEN subconflictlogdest IN ('table', 'all') "
- " THEN 'pg_conflict.pg_conflict_log_' || oid "
- " ELSE '-' END) AS \"%s\"\n",
- gettext_noop("Conflict log table"));
- }
+ ncols += 4;
+ }
+
+ appendPQExpBuffer(&buf,
+ ", subsynccommit AS \"%s\"\n"
+ ", subconninfo AS \"%s\"\n",
+ gettext_noop("Synchronous commit"),
+ gettext_noop("Conninfo"));
+ ncols += 2;
+
+ if (pset.sversion >= 190000)
+ {
+ appendPQExpBuffer(&buf,
+ ", subwalrcvtimeout AS \"%s\"\n",
+ gettext_noop("Receiver timeout"));
+ ncols++;
+ }
+
+ /* Skip LSN is only supported in v15 and higher */
+ if (pset.sversion >= 150000)
+ {
+ appendPQExpBuffer(&buf,
+ ", subskiplsn AS \"%s\"\n",
+ gettext_noop("Skip LSN"));
+ ncols++;
+ }
+
+ appendPQExpBuffer(&buf,
+ ", pg_catalog.obj_description(oid, 'pg_subscription') AS \"%s\"\n",
+ gettext_noop("Description"));
+ ncols++;
+
+ /* Conflict log destination is supported in v19 and higher */
+ if (pset.sversion >= 190000)
+ {
+ appendPQExpBuffer(&buf,
+ ", subconflictlogdest AS \"%s\"\n",
+ gettext_noop("Conflict log destination"));
+ ncols++;
}
/* Only display subscriptions in current database. */
@@ -7240,13 +7330,148 @@ describeSubscriptions(const char *pattern, bool verbose)
if (!res)
return false;
- myopt.title = _("List of subscriptions");
- myopt.translate_header = true;
- myopt.translate_columns = translate_columns;
- myopt.n_translate_columns = lengthof(translate_columns);
+ if (PQntuples(res) == 0)
+ {
+ if (!pset.quiet)
+ {
+ if (pattern)
+ pg_log_error("Did not find any subscription named \"%s\".",
+ pattern);
+ else
+ pg_log_error("Did not find any subscriptions.");
+ }
- printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+ termPQExpBuffer(&buf);
+ PQclear(res);
+ return false;
+ }
+
+ for (i = 0; i < PQntuples(res); i++)
+ {
+ const char align = 'l';
+ char *subid = PQgetvalue(res, i, 0);
+ char *subname = PQgetvalue(res, i, 1);
+ int current_col = 2;
+ printTableOpt myopt = pset.popt.topt;
+
+ initPQExpBuffer(&title);
+ printfPQExpBuffer(&title, _("Subscription %s"), subname);
+ printTableInit(&cont, &myopt, title.data, ncols, nrows);
+
+ printTableAddHeader(&cont, gettext_noop("Owner"), true, align);
+ printTableAddHeader(&cont, gettext_noop("Enabled"), true, align);
+ printTableAddHeader(&cont, gettext_noop("Publication"), true, align);
+
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ if (pset.sversion >= 140000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Binary"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Streaming"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ if (pset.sversion >= 150000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Two-phase commit"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Disable on error"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ if (pset.sversion >= 160000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Origin"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Password required"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Run as owner?"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ if (pset.sversion >= 170000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Failover"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ if (pset.sversion >= 190000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Server"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Retain dead tuples"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Max retention duration"),
+ true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Retention active"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ printTableAddHeader(&cont, gettext_noop("Synchronous commit"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ printTableAddHeader(&cont, gettext_noop("Conninfo"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ if (pset.sversion >= 190000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Receiver timeout"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ if (pset.sversion >= 150000)
+ {
+ printTableAddHeader(&cont, gettext_noop("Skip LSN"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+ }
+
+ printTableAddHeader(&cont, gettext_noop("Description"), true, align);
+ printTableAddCell(&cont, PQgetvalue(res, i, current_col++), false, false);
+
+ if (pset.sversion >= 190000)
+ {
+ char *logdest;
+
+ printTableAddHeader(&cont, gettext_noop("Conflict log destination"),
+ true, align);
+
+ logdest = PQgetvalue(res, i, current_col++);
+
+ printTableAddCell(&cont, logdest, false, false);
+
+ if (strcmp(logdest, "table") == 0 ||
+ strcmp(logdest, "all") == 0)
+ {
+ char conflictlogtable[NAMEDATALEN + 32];
+
+ snprintf(conflictlogtable,
+ sizeof(conflictlogtable),
+ "pg_conflict.pg_conflict_log_%s",
+ subid);
+
+ printTableAddFooter(&cont, _("Conflict log table:"));
+ printTableAddFooter(&cont, psprintf(" %s", conflictlogtable));
+ }
+ }
+
+ printTable(&cont, pset.queryFout, false, pset.logfile);
+ printTableCleanup(&cont);
+
+ termPQExpBuffer(&title);
+ }
+
+ termPQExpBuffer(&buf);
PQclear(res);
return true;
}
diff --git a/src/bin/psql/describe.h b/src/bin/psql/describe.h
index 47fae5ceafb..15c6c685323 100644
--- a/src/bin/psql/describe.h
+++ b/src/bin/psql/describe.h
@@ -126,7 +126,10 @@ bool listPublications(const char *pattern);
bool describePublications(const char *pattern);
/* \dRs */
-bool describeSubscriptions(const char *pattern, bool verbose);
+bool listSubscriptions(const char *pattern);
+
+/* \dRs+ */
+bool describeSubscriptions(const char *pattern);
/* \dAc */
extern bool listOperatorClasses(const char *access_method_pattern,
diff --git a/src/include/catalog/pg_subscription.h b/src/include/catalog/pg_subscription.h
index 5f214d3586b..cc31b4d00bc 100644
--- a/src/include/catalog/pg_subscription.h
+++ b/src/include/catalog/pg_subscription.h
@@ -97,6 +97,14 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW
Oid subconflictlogrelid; /* Relid of the conflict log table. */
#ifdef CATALOG_VARLEN /* variable-length fields start here */
+ /*
+ * Strategy for logging replication conflicts:
+ * 'log' - server log only,
+ * 'table' - conflict log table only,
+ * 'all' - both log and table.
+ */
+ text subconflictlogdest BKI_FORCE_NOT_NULL;
+
/* Connection string to the publisher */
text subconninfo; /* Set if connecting with connection string */
@@ -112,14 +120,6 @@ CATALOG(pg_subscription,6100,SubscriptionRelationId) BKI_SHARED_RELATION BKI_ROW
/* List of publications subscribed to */
text subpublications[1] BKI_FORCE_NOT_NULL;
- /*
- * Strategy for logging replication conflicts:
- * 'log' - server log only,
- * 'table' - conflict log table only,
- * 'all' - both log and table.
- */
- text subconflictlogdest BKI_FORCE_NOT_NULL;
-
/* Only publish data originating from the specified origin */
text suborigin BKI_DEFAULT(LOGICALREP_ORIGIN_ANY);
#endif
diff --git a/src/test/regress/expected/subscription.out b/src/test/regress/expected/subscription.out
index 85f9c60f449..be040f41da9 100644
--- a/src/test/regress/expected/subscription.out
+++ b/src/test/regress/expected/subscription.out
@@ -124,18 +124,18 @@ CREATE SUBSCRIPTION regress_testsub4 CONNECTION 'dbname=regress_doesnotexist' PU
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+ regress_testsub4
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
-------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub4
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | none | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub4 SET (origin = any);
\dRs+ regress_testsub4
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
-------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub4 | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub4
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
DROP SUBSCRIPTION regress_testsub3;
@@ -200,10 +200,10 @@ ALTER SUBSCRIPTION regress_testsub CONNECTION 'foobar';
ERROR: invalid connection string syntax: missing "=" after "foobar" in connection info string
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | test subscription | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | test subscription | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET PUBLICATION testpub2, testpub3 WITH (refresh = false);
@@ -212,10 +212,10 @@ ALTER SUBSCRIPTION regress_testsub SET (slot_name = 'newname');
ALTER SUBSCRIPTION regress_testsub SET (password_required = false);
ALTER SUBSCRIPTION regress_testsub SET (run_as_owner = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------
+ regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | f | t | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (password_required = true);
@@ -231,10 +231,10 @@ ERROR: unrecognized subscription parameter: "create_slot"
-- ok
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/12345');
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00012345 | test subscription | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------
+ regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00012345 | test subscription | log
(1 row)
-- ok - with lsn = NONE
@@ -243,10 +243,10 @@ ALTER SUBSCRIPTION regress_testsub SKIP (lsn = NONE);
ALTER SUBSCRIPTION regress_testsub SKIP (lsn = '0/0');
ERROR: invalid WAL location (LSN): 0/0
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------
+ regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist2 | -1 | 0/00000000 | test subscription | log
(1 row)
BEGIN;
@@ -282,10 +282,10 @@ ALTER SUBSCRIPTION regress_testsub_foo SET (wal_receiver_timeout = '80s');
ALTER SUBSCRIPTION regress_testsub_foo SET (wal_receiver_timeout = 'foobar');
ERROR: invalid value for parameter "wal_receiver_timeout": "foobar"
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
----------------------+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------+--------------------
- regress_testsub_foo | regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | local | dbname=regress_doesnotexist2 | 80s | 0/00000000 | test subscription | log | -
+ Subscription regress_testsub_foo
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+---------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+------------------------------+------------------+------------+-------------------+--------------------------
+ regress_subscription_user | f | {testpub2,testpub3} | f | parallel | d | f | any | t | f | f | | f | 0 | f | local | dbname=regress_doesnotexist2 | 80s | 0/00000000 | test subscription | log
(1 row)
-- rename back to keep the rest simple
@@ -314,19 +314,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | t | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (binary = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
DROP SUBSCRIPTION regress_testsub;
@@ -338,27 +338,27 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | on | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (streaming = parallel);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (streaming = false);
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
-- fail - publication already exists
@@ -373,10 +373,10 @@ ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refr
ALTER SUBSCRIPTION regress_testsub ADD PUBLICATION testpub1, testpub2 WITH (refresh = false);
ERROR: publication "testpub1" is already in subscription "regress_testsub"
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-----------------------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub,testpub1,testpub2} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
-- fail - publication used more than once
@@ -391,10 +391,10 @@ ERROR: publication "testpub3" is not in subscription "regress_testsub"
-- ok - delete publications
ALTER SUBSCRIPTION regress_testsub DROP PUBLICATION testpub1, testpub2 WITH (refresh = false);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | off | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
DROP SUBSCRIPTION regress_testsub;
@@ -430,19 +430,19 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
-- we can alter streaming when two_phase enabled
ALTER SUBSCRIPTION regress_testsub SET (streaming = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -452,10 +452,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | on | p | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -468,18 +468,18 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (disable_on_error = true);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | t | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -492,10 +492,10 @@ CREATE SUBSCRIPTION regress_testsub CONNECTION 'dbname=regress_doesnotexist' PUB
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -509,19 +509,19 @@ NOTICE: max_retention_duration is ineffective when retain_dead_tuples is disabl
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 1000 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 1000 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
-- ok
ALTER SUBSCRIPTION regress_testsub SET (max_retention_duration = 0);
\dRs+
- List of subscriptions
- Name | Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination | Conflict log table
------------------+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------+--------------------
- regress_testsub | regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log | -
+ Subscription regress_testsub
+ Owner | Enabled | Publication | Binary | Streaming | Two-phase commit | Disable on error | Origin | Password required | Run as owner? | Failover | Server | Retain dead tuples | Max retention duration | Retention active | Synchronous commit | Conninfo | Receiver timeout | Skip LSN | Description | Conflict log destination
+---------------------------+---------+-------------+--------+-----------+------------------+------------------+--------+-------------------+---------------+----------+--------+--------------------+------------------------+------------------+--------------------+-----------------------------+------------------+------------+-------------+--------------------------
+ regress_subscription_user | f | {testpub} | f | parallel | d | f | any | t | f | f | | f | 0 | f | off | dbname=regress_doesnotexist | -1 | 0/00000000 | | log
(1 row)
ALTER SUBSCRIPTION regress_testsub SET (slot_name = NONE);
@@ -585,7 +585,7 @@ SET client_min_messages = WARNING;
CREATE SUBSCRIPTION regress_conflict_fail CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'invalid');
ERROR: unrecognized conflict_log_destination value: "invalid"
HINT: Valid values are "log", "table", and "all".
--- verify subconflictlogdest is 'log' and relid is 0 (InvalidOid) for default case
+-- verify subconflictlogdest is 'log' and subconflictlogrelid is 0 (InvalidOid) for default case
CREATE SUBSCRIPTION regress_conflict_log_default CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
@@ -611,7 +611,7 @@ FROM pg_subscription WHERE subname = 'regress_conflict_empty_str';
CREATE SUBSCRIPTION regress_conflict_test1 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
WARNING: subscription was created, but is not connected
HINT: To initiate replication, you must manually create the replication slot, enable the subscription, and alter the subscription to refresh publications.
--- check metadata in pg_subscription: destination should be 'table' and relid valid
+-- check metadata in pg_subscription: destination should be 'table' and subconflictlogrelid valid
SELECT subname, subconflictlogdest, subconflictlogrelid > 0 AS has_relid
FROM pg_subscription WHERE subname = 'regress_conflict_test1';
subname | subconflictlogdest | has_relid
@@ -653,6 +653,41 @@ WHERE s.subname = 'regress_conflict_test1' AND a.attnum > 0
11 | local_conflicts
(11 rows)
+-- Changing the subscription owner should also update the owner
+-- of the associated conflict log table.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user2;
+SELECT pg_catalog.pg_get_userbyid(c.relowner) AS owner
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_subscription s
+ ON c.relname = 'pg_conflict_log_' || s.oid
+WHERE s.subname = 'regress_conflict_test1';
+ owner
+----------------------------
+ regress_subscription_user2
+(1 row)
+
+-- Verify that a non-superuser subscription owner can truncate,
+-- delete from, and select from the associated conflict log table.
+SET ROLE 'regress_subscription_user2';
+SELECT format('%I.%I', n.nspname, c.relname) AS conflict_log_table
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n
+ ON n.oid = c.relnamespace
+JOIN pg_catalog.pg_subscription s
+ ON c.relname = 'pg_conflict_log_' || s.oid
+WHERE s.subname = 'regress_conflict_test1'
+\gset
+TRUNCATE TABLE :conflict_log_table;
+DELETE FROM :conflict_log_table;
+SELECT COUNT(*) FROM :conflict_log_table;
+ count
+-------
+ 0
+(1 row)
+
+RESET ROLE;
+-- Restore the original subscription owner.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user;
--
-- ALTER SUBSCRIPTION - conflict_log_destination state transitions
--
@@ -686,7 +721,7 @@ FROM pg_subscription WHERE subname = 'regress_conflict_test2';
(1 row)
-- transition from 'table' to 'log'
--- should drop the table and clear relid
+-- should drop the table and clear subconflictlogrelid
ALTER SUBSCRIPTION regress_conflict_test2 SET (conflict_log_destination = 'log');
SELECT subconflictlogdest, subconflictlogrelid
FROM pg_subscription WHERE subname = 'regress_conflict_test2';
@@ -748,6 +783,7 @@ ALTER SUBSCRIPTION regress_conflict_test1 DISABLE;
ALTER SUBSCRIPTION regress_conflict_test1 SET (slot_name = NONE);
-- Verify the table OID for reap check
SELECT 'pg_conflict_log_' || oid AS internal_tablename FROM pg_subscription WHERE subname = 'regress_conflict_test1' \gset
+SET client_min_messages = WARNING;
DROP SUBSCRIPTION regress_conflict_test1;
-- should return NULL, meaning the conflict log table was reaped via dependency
SELECT to_regclass(:'internal_tablename');
@@ -759,7 +795,6 @@ SELECT to_regclass(:'internal_tablename');
--
-- Additional Namespace and Table Protection Tests
--
-SET client_min_messages = WARNING;
-- Setup: Ensure we have a subscription with a conflict log table
CREATE SUBSCRIPTION regress_conflict_protection_test CONNECTION 'dbname=regress_doesnotexist'
PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
@@ -835,6 +870,7 @@ CREATE TABLE public.test_move (id int);
ALTER TABLE public.test_move SET SCHEMA pg_conflict;
ERROR: cannot move objects into or out of CONFLICT schema
DROP TABLE public.test_move;
+SET client_min_messages = WARNING;
-- Clean up remaining test subscription
ALTER SUBSCRIPTION regress_conflict_log_default DISABLE;
ALTER SUBSCRIPTION regress_conflict_log_default SET (slot_name = NONE);
diff --git a/src/test/regress/sql/subscription.sql b/src/test/regress/sql/subscription.sql
index d155f24fdbb..1445feac32a 100644
--- a/src/test/regress/sql/subscription.sql
+++ b/src/test/regress/sql/subscription.sql
@@ -442,7 +442,7 @@ SET client_min_messages = WARNING;
-- fail - unrecognized parameter value
CREATE SUBSCRIPTION regress_conflict_fail CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'invalid');
--- verify subconflictlogdest is 'log' and relid is 0 (InvalidOid) for default case
+-- verify subconflictlogdest is 'log' and subconflictlogrelid is 0 (InvalidOid) for default case
CREATE SUBSCRIPTION regress_conflict_log_default CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false);
SELECT subname, subconflictlogdest, subconflictlogrelid
FROM pg_subscription WHERE subname = 'regress_conflict_log_default';
@@ -455,7 +455,7 @@ FROM pg_subscription WHERE subname = 'regress_conflict_empty_str';
-- this should generate an internal conflict log table named pg_conflict_log_$subid$
CREATE SUBSCRIPTION regress_conflict_test1 CONNECTION 'dbname=regress_doesnotexist' PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
--- check metadata in pg_subscription: destination should be 'table' and relid valid
+-- check metadata in pg_subscription: destination should be 'table' and subconflictlogrelid valid
SELECT subname, subconflictlogdest, subconflictlogrelid > 0 AS has_relid
FROM pg_subscription WHERE subname = 'regress_conflict_test1';
@@ -475,6 +475,37 @@ JOIN pg_subscription s ON c.relname = 'pg_conflict_log_' || s.oid
WHERE s.subname = 'regress_conflict_test1' AND a.attnum > 0
ORDER BY a.attnum;
+-- Changing the subscription owner should also update the owner
+-- of the associated conflict log table.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user2;
+SELECT pg_catalog.pg_get_userbyid(c.relowner) AS owner
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_subscription s
+ ON c.relname = 'pg_conflict_log_' || s.oid
+WHERE s.subname = 'regress_conflict_test1';
+
+-- Verify that a non-superuser subscription owner can truncate,
+-- delete from, and select from the associated conflict log table.
+SET ROLE 'regress_subscription_user2';
+
+SELECT format('%I.%I', n.nspname, c.relname) AS conflict_log_table
+FROM pg_catalog.pg_class c
+JOIN pg_catalog.pg_namespace n
+ ON n.oid = c.relnamespace
+JOIN pg_catalog.pg_subscription s
+ ON c.relname = 'pg_conflict_log_' || s.oid
+WHERE s.subname = 'regress_conflict_test1'
+\gset
+
+TRUNCATE TABLE :conflict_log_table;
+DELETE FROM :conflict_log_table;
+SELECT COUNT(*) FROM :conflict_log_table;
+
+RESET ROLE;
+
+-- Restore the original subscription owner.
+ALTER SUBSCRIPTION regress_conflict_test1 owner to regress_subscription_user;
+
--
-- ALTER SUBSCRIPTION - conflict_log_destination state transitions
--
@@ -499,7 +530,7 @@ SELECT subconflictlogdest, subconflictlogrelid = :old_relid AS relid_unchanged
FROM pg_subscription WHERE subname = 'regress_conflict_test2';
-- transition from 'table' to 'log'
--- should drop the table and clear relid
+-- should drop the table and clear subconflictlogrelid
ALTER SUBSCRIPTION regress_conflict_test2 SET (conflict_log_destination = 'log');
SELECT subconflictlogdest, subconflictlogrelid
FROM pg_subscription WHERE subname = 'regress_conflict_test2';
@@ -553,6 +584,7 @@ ALTER SUBSCRIPTION regress_conflict_test1 SET (slot_name = NONE);
-- Verify the table OID for reap check
SELECT 'pg_conflict_log_' || oid AS internal_tablename FROM pg_subscription WHERE subname = 'regress_conflict_test1' \gset
+SET client_min_messages = WARNING;
DROP SUBSCRIPTION regress_conflict_test1;
-- should return NULL, meaning the conflict log table was reaped via dependency
@@ -562,7 +594,6 @@ SELECT to_regclass(:'internal_tablename');
-- Additional Namespace and Table Protection Tests
--
-SET client_min_messages = WARNING;
-- Setup: Ensure we have a subscription with a conflict log table
CREATE SUBSCRIPTION regress_conflict_protection_test CONNECTION 'dbname=regress_doesnotexist'
PUBLICATION testpub WITH (connect = false, conflict_log_destination = 'table');
@@ -637,6 +668,8 @@ CREATE TABLE public.test_move (id int);
ALTER TABLE public.test_move SET SCHEMA pg_conflict;
DROP TABLE public.test_move;
+SET client_min_messages = WARNING;
+
-- Clean up remaining test subscription
ALTER SUBSCRIPTION regress_conflict_log_default DISABLE;
ALTER SUBSCRIPTION regress_conflict_log_default SET (slot_name = NONE);
--
2.53.0