Add decoding of sequences to built-in replication

This commit adds support for decoding of sequences to the built-in
replication (the infrastructure was added by commit 0da92dc530).

The syntax and behavior mostly mimics handling of tables, i.e. a
publication may be defined as FOR ALL SEQUENCES (replicating all
sequences in a database), FOR ALL SEQUENCES IN SCHEMA (replicating
all sequences in a particular schema) or individual sequences.

To publish sequence modifications, the publication has to include
'sequence' action. The protocol is extended with a new message,
describing sequence increments.

A new system view pg_publication_sequences lists all the sequences
added to a publication, both directly and indirectly. Various psql
commands (\d and \dRp) are improved to also display publications
including a given sequence, or sequences included in a publication.

Author: Tomas Vondra, Cary Huang
Reviewed-by: Peter Eisentraut, Amit Kapila, Hannu Krosing, Andres
             Freund, Petr Jelinek
Discussion: https://postgr.es/m/[email protected]
Discussion: https://postgr.es/m/[email protected]

1 files changed, 216 insertions, 71 deletions

diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 714097cad1b..b8a532a45f7 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1633,28 +1633,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';",
@@ -1663,22 +1654,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));
@@ -1688,6 +1672,51 @@ 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;
+
+		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 */
@@ -1721,29 +1750,63 @@ 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);
 
-		printfPQExpBuffer(&title, _("Sequence \"%s.%s\""),
-						  schemaname, relationname);
+		/* print any publications */
+		if (pset.sversion >= 150000)
+		{
+			int			tuples = 0;
 
-		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"
+							  "		JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
+							  "		JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
+							  "WHERE pc.oid ='%s' and pn.pntype = 's' and pg_catalog.pg_relation_is_publishable('%s')\n"
+							  "UNION\n"
+							  "SELECT pubname\n"
+							  "FROM pg_catalog.pg_publication p\n"
+							  "		JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid\n"
+							  "WHERE pr.prrelid = '%s'\n"
+							  "UNION\n"
+							  "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, oid, oid, oid);
 
-		printQuery(res, &myopt, pset.queryFout, false, pset.logfile);
+			result = PSQLexec(buf.data);
+			if (!result)
+				goto error_return;
+			else
+				tuples = PQntuples(result);
+
+			if (tuples > 0)
+				printTableAddFooter(&cont, _("Publications:"));
+
+			/* Might be an empty set - that's ok */
+			for (i = 0; i < tuples; i++)
+			{
+				printfPQExpBuffer(&buf, "    \"%s\"",
+								  PQgetvalue(result, i, 0));
+
+				printTableAddFooter(&cont, buf.data);
+			}
+			PQclear(result);
+		}
 
-		if (footers[0])
-			free(footers[0]);
+		printTable(&cont, pset.queryFout, false, pset.logfile);
 
 		retval = true;
 		goto error_return;		/* not an error, just return early */
@@ -1970,6 +2033,11 @@ describeOneTableDetails(const char *schemaname,
 	for (i = 0; i < cols; i++)
 		printTableAddHeader(&cont, headers[i], true, 'l');
 
+	res = PSQLexec(buf.data);
+	if (!res)
+		goto error_return;
+	numrows = PQntuples(res);
+
 	/* Generate table cells to be printed */
 	for (i = 0; i < numrows; i++)
 	{
@@ -2895,7 +2963,7 @@ describeOneTableDetails(const char *schemaname,
 								  "FROM pg_catalog.pg_publication p\n"
 								  "		JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
 								  "		JOIN pg_catalog.pg_class pc ON pc.relnamespace = pn.pnnspid\n"
-								  "WHERE pc.oid ='%s' and pg_catalog.pg_relation_is_publishable('%s')\n"
+								  "WHERE pc.oid ='%s' and pn.pntype = 't' and pg_catalog.pg_relation_is_publishable('%s')\n"
 								  "UNION\n"
 								  "SELECT pubname\n"
 								  "		, pg_get_expr(pr.prqual, c.oid)\n"
@@ -4785,7 +4853,7 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 		int			i;
 
 		printfPQExpBuffer(&buf,
-						  "SELECT pubname \n"
+						  "SELECT pubname, (CASE WHEN pntype = 't' THEN 'tables' ELSE 'sequences' END) AS pubtype\n"
 						  "FROM pg_catalog.pg_publication p\n"
 						  "		JOIN pg_catalog.pg_publication_namespace pn ON p.oid = pn.pnpubid\n"
 						  "		JOIN pg_catalog.pg_namespace n ON n.oid = pn.pnnspid \n"
@@ -4814,8 +4882,9 @@ listSchemas(const char *pattern, bool verbose, bool showSystem)
 			/* Might be an empty set - that's ok */
 			for (i = 0; i < pub_schema_tuples; i++)
 			{
-				printfPQExpBuffer(&buf, "    \"%s\"",
-								  PQgetvalue(result, i, 0));
+				printfPQExpBuffer(&buf, "    \"%s\" (%s)",
+								  PQgetvalue(result, i, 0),
+								  PQgetvalue(result, i, 1));
 
 				footers[i + 1] = pg_strdup(buf.data);
 			}
@@ -5820,7 +5889,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};
+	static const bool translate_columns[] = {false, false, false, false, false, false, false, false, false, false};
 
 	if (pset.sversion < 100000)
 	{
@@ -5834,23 +5903,45 @@ listPublications(const char *pattern)
 
 	initPQExpBuffer(&buf);
 
-	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\"",
-					  gettext_noop("Name"),
-					  gettext_noop("Owner"),
-					  gettext_noop("All tables"),
-					  gettext_noop("Inserts"),
-					  gettext_noop("Updates"),
-					  gettext_noop("Deletes"));
+	if (pset.sversion >= 150000)
+		printfPQExpBuffer(&buf,
+						  "SELECT pubname AS \"%s\",\n"
+						  "  pg_catalog.pg_get_userbyid(pubowner) AS \"%s\",\n"
+						  "  puballtables AS \"%s\",\n"
+						  "  puballsequences AS \"%s\",\n"
+						  "  pubinsert AS \"%s\",\n"
+						  "  pubupdate AS \"%s\",\n"
+						  "  pubdelete AS \"%s\"",
+						  gettext_noop("Name"),
+						  gettext_noop("Owner"),
+						  gettext_noop("All tables"),
+						  gettext_noop("All sequences"),
+						  gettext_noop("Inserts"),
+						  gettext_noop("Updates"),
+						  gettext_noop("Deletes"));
+	else
+		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\"",
+						  gettext_noop("Name"),
+						  gettext_noop("Owner"),
+						  gettext_noop("All tables"),
+						  gettext_noop("Inserts"),
+						  gettext_noop("Updates"),
+						  gettext_noop("Deletes"));
+
 	if (pset.sversion >= 110000)
 		appendPQExpBuffer(&buf,
 						  ",\n  pubtruncate AS \"%s\"",
 						  gettext_noop("Truncates"));
+	if (pset.sversion >= 150000)
+		appendPQExpBuffer(&buf,
+						  ",\n  pubsequence AS \"%s\"",
+						  gettext_noop("Sequences"));
 	if (pset.sversion >= 130000)
 		appendPQExpBuffer(&buf,
 						  ",\n  pubviaroot AS \"%s\"",
@@ -5936,6 +6027,7 @@ describePublications(const char *pattern)
 	PGresult   *res;
 	bool		has_pubtruncate;
 	bool		has_pubviaroot;
+	bool		has_pubsequence;
 
 	PQExpBufferData title;
 	printTableContent cont;
@@ -5952,6 +6044,7 @@ describePublications(const char *pattern)
 
 	has_pubtruncate = (pset.sversion >= 110000);
 	has_pubviaroot = (pset.sversion >= 130000);
+	has_pubsequence = (pset.sversion >= 150000);
 
 	initPQExpBuffer(&buf);
 
@@ -5959,12 +6052,17 @@ describePublications(const char *pattern)
 					  "SELECT oid, pubname,\n"
 					  "  pg_catalog.pg_get_userbyid(pubowner) AS owner,\n"
 					  "  puballtables, pubinsert, pubupdate, pubdelete");
+
 	if (has_pubtruncate)
 		appendPQExpBufferStr(&buf,
 							 ", pubtruncate");
 	if (has_pubviaroot)
 		appendPQExpBufferStr(&buf,
 							 ", pubviaroot");
+	if (has_pubsequence)
+		appendPQExpBufferStr(&buf,
+							 ", puballsequences, pubsequence");
+
 	appendPQExpBufferStr(&buf,
 						 "\nFROM pg_catalog.pg_publication\n");
 
@@ -6005,6 +6103,7 @@ describePublications(const char *pattern)
 		char	   *pubid = PQgetvalue(res, i, 0);
 		char	   *pubname = PQgetvalue(res, i, 1);
 		bool		puballtables = strcmp(PQgetvalue(res, i, 3), "t") == 0;
+		bool		puballsequences = strcmp(PQgetvalue(res, i, 9), "t") == 0;
 		printTableOpt myopt = pset.popt.topt;
 
 		if (has_pubtruncate)
@@ -6012,29 +6111,43 @@ describePublications(const char *pattern)
 		if (has_pubviaroot)
 			ncols++;
 
+		/* sequences have two extra columns (puballsequences, pubsequences) */
+		if (has_pubsequence)
+			ncols += 2;
+
 		initPQExpBuffer(&title);
 		printfPQExpBuffer(&title, _("Publication %s"), pubname);
 		printTableInit(&cont, &myopt, title.data, ncols, nrows);
 
 		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);
 		if (has_pubtruncate)
 			printTableAddHeader(&cont, gettext_noop("Truncates"), true, align);
+		if (has_pubsequence)
+			printTableAddHeader(&cont, gettext_noop("Sequences"), true, align);
 		if (has_pubviaroot)
 			printTableAddHeader(&cont, gettext_noop("Via root"), true, align);
 
-		printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
-		printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false);
-		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, 2), false, false); /* owner */
+		printTableAddCell(&cont, PQgetvalue(res, i, 3), false, false); /* all tables */
+
+		if (has_pubsequence)
+			printTableAddCell(&cont, PQgetvalue(res, i, 9), false, false); /* all sequences */
+
+		printTableAddCell(&cont, PQgetvalue(res, i, 4), false, false); /* insert */
+		printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false); /* update */
+		printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); /* delete */
 		if (has_pubtruncate)
-			printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
+			printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); /* truncate */
+		if (has_pubsequence)
+			printTableAddCell(&cont, PQgetvalue(res, i, 10), false, false); /* sequence */
 		if (has_pubviaroot)
-			printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
+			printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); /* via root */
 
 		if (!puballtables)
 		{
@@ -6054,6 +6167,7 @@ describePublications(const char *pattern)
 							  "WHERE c.relnamespace = n.oid\n"
 							  "  AND c.oid = pr.prrelid\n"
 							  "  AND pr.prpubid = '%s'\n"
+							  "  AND c.relkind != 'S'\n" /* exclude sequences */
 							  "ORDER BY 1,2", pubid);
 			if (!addFooterToPublicationDesc(&buf, "Tables:", false, &cont))
 				goto error_return;
@@ -6065,7 +6179,7 @@ describePublications(const char *pattern)
 								  "SELECT n.nspname\n"
 								  "FROM pg_catalog.pg_namespace n\n"
 								  "     JOIN pg_catalog.pg_publication_namespace pn ON n.oid = pn.pnnspid\n"
-								  "WHERE pn.pnpubid = '%s'\n"
+								  "WHERE pn.pnpubid = '%s' AND pn.pntype = 't'\n"
 								  "ORDER BY 1", pubid);
 				if (!addFooterToPublicationDesc(&buf, "Tables from schemas:",
 												true, &cont))
@@ -6073,6 +6187,37 @@ describePublications(const char *pattern)
 			}
 		}
 
+		if (!puballsequences)
+		{
+			/* Get the tables for the specified publication */
+			printfPQExpBuffer(&buf,
+							  "SELECT n.nspname, c.relname, NULL\n"
+							  "FROM pg_catalog.pg_class c,\n"
+							  "     pg_catalog.pg_namespace n,\n"
+							  "     pg_catalog.pg_publication_rel pr\n"
+							  "WHERE c.relnamespace = n.oid\n"
+							  "  AND c.oid = pr.prrelid\n"
+							  "  AND pr.prpubid = '%s'\n"
+							  "  AND c.relkind = 'S'\n" /* only sequences */
+							  "ORDER BY 1,2", pubid);
+			if (!addFooterToPublicationDesc(&buf, "Sequences:", false, &cont))
+				goto error_return;
+
+			if (pset.sversion >= 150000)
+			{
+				/* Get the schemas for the specified publication */
+				printfPQExpBuffer(&buf,
+								  "SELECT n.nspname\n"
+								  "FROM pg_catalog.pg_namespace n\n"
+								  "     JOIN pg_catalog.pg_publication_namespace pn ON n.oid = pn.pnnspid\n"
+								  "WHERE pn.pnpubid = '%s' AND pn.pntype = 's'\n"
+								  "ORDER BY 1", pubid);
+				if (!addFooterToPublicationDesc(&buf, "Sequences from schemas:",
+												true, &cont))
+					goto error_return;
+			}
+		}
+
 		printTable(&cont, pset.queryFout, false, pset.logfile);
 		printTableCleanup(&cont);