If there are no new insights, I plan to proceed with the attached patch
tomorrow. This leaves the existing view and function alone, adds
pg_relation_is_publishable() and uses that in psql.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From e3cf8bc1d2ac44081c63bb5425ddde4f85ebeaf8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Mon, 19 Jun 2017 20:49:57 -0400
Subject: [PATCH v2] Tweak publication fetching in psql
Viewing a table with \d in psql also shows the publications at table is
in. If a publication is concurrently dropped, this shows an error,
because the view pg_publication_tables internally uses
pg_get_publication_tables, which uses a catalog snapshot. This can be
particularly annoying if a for-all-tables publication is concurrently
dropped.
To avoid that, write the query in psql differently. Expose the function
pg_relation_is_publishable() to SQL and write the query using that.
That still has a risk of being affected by concurrent catalog changes,
but in this case it would be a table drop that causes problems, and then
the psql \d command wouldn't be interesting anymore anyway.
Reported-by: Tom Lane <t...@sss.pgh.pa.us>
---
src/backend/catalog/pg_publication.c | 24 ++++++++++++++++++++++++
src/bin/psql/describe.c | 14 +++++++++-----
src/include/catalog/pg_proc.h | 2 ++
3 files changed, 35 insertions(+), 5 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c
b/src/backend/catalog/pg_publication.c
index 17105f4f2c..17b2e8d649 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -105,6 +105,30 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
relid >= FirstNormalObjectId;
}
+
+/*
+ * SQL-callable variant of the above
+ *
+ * This returns null when the relation does not exist. This is intended to be
+ * used for example in psql to avoid gratuitous errors when there are
+ * concurrent catalog changes.
+ */
+Datum
+pg_relation_is_publishable(PG_FUNCTION_ARGS)
+{
+ Oid relid = PG_GETARG_OID(0);
+ HeapTuple tuple;
+ bool result;
+
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+ if (!tuple)
+ PG_RETURN_NULL();
+ result = is_publishable_class(relid, (Form_pg_class) GETSTRUCT(tuple));
+ ReleaseSysCache(tuple);
+ PG_RETURN_BOOL(result);
+}
+
+
/*
* Insert new publication / relation mapping.
*/
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ea0e8af2ec..0e19e94841 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2536,12 +2536,16 @@ describeOneTableDetails(const char *schemaname,
if (pset.sversion >= 100000)
{
printfPQExpBuffer(&buf,
- "SELECT pub.pubname\n"
- " FROM
pg_catalog.pg_publication pub,\n"
- "
pg_catalog.pg_get_publication_tables(pub.pubname)\n"
- "WHERE relid = '%s'\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 ALL\n"
+ "SELECT pubname\n"
+ "FROM
pg_catalog.pg_publication p\n"
+ "WHERE p.puballtables
AND pg_relation_is_publishable('%s')\n"
"ORDER BY 1;",
- oid);
+ oid, oid);
result = PSQLexec(buf.data);
if (!result)
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6c44def6e6..81bed23426 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -5436,6 +5436,8 @@ DESCR("get progress for all replication origins");
/* publications */
DATA(insert OID = 6119 ( pg_get_publication_tables PGNSP PGUID 12 1 1000 0
0 f f t f t t s s 1 0 26 "25" "{25,26}" "{i,o}" "{pubname,relid}" _null_ _null_
pg_get_publication_tables _null_ _null_ _null_ ));
DESCR("get OIDs of tables in a publication");
+DATA(insert OID = 6121 ( pg_relation_is_publishable PGNSP PGUID 12 1 0 0
0 f f f f t f s s 1 0 16 "2205" _null_ _null_ _null_ _null_ _null_
pg_relation_is_publishable _null_ _null_ _null_ ));
+DESCR("returns whether a relation can be part of a publication");
/* rls */
DATA(insert OID = 3298 ( row_security_active PGNSP PGUID 12 1 0 0 0 f f f
f t f s s 1 0 16 "26" _null_ _null_ _null_ _null_ _null_
row_security_active _null_ _null_ _null_ ));
--
2.13.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
