On 6/15/17 12:23, Tom Lane wrote:
> It strikes me that you could rewrite psql's query to just do its own
> catalog search and not bother with the function at all. It would have
> to know a bit more about the catalog structure than it does now, but not
> that much:
>
> select pub.pubname from pg_catalog.pg_publication pub
> where puballtables or
> exists(select 1 from pg_catalog.pg_publication_rel r
> where r.prpubid = pub.oid and r.prrelid = '%s');
We used to do something like that, but then people complained that that
was not absolutely accurate, because it did not exclude catalog tables
and related things properly. See commit
2d460179baa8744e9e2a183a5121306596c53fba. To do this properly, you need
to filter pg_class using is_publishable_class() (hitherto internal C
function).
The way this was originally written was for use by subscriptioncmds.c
fetch_table_list(), which generally only deals with a small number of
publications as the search key and wants to find all the relations. The
psql use case is exactly the opposite: We start with a relation and want
to find all the publications. The third use case is that we document
the view pg_publication_tables for general use, so depending on which
search key you start with, you might get terrible performance if you
have a lot of tables.
An academically nice way to write a general query for this would be:
CREATE VIEW pg_publication_tables AS
SELECT
p.pubname AS pubname,
n.nspname AS schemaname,
c.relname AS tablename,
c.oid AS relid
FROM pg_publication p
JOIN pg_publication_rel pr ON p.oid = pr.prpubid
JOIN pg_class c ON pr.prrelid = c.oid
JOIN pg_namespace n ON c.relnamespace = n.oid
UNION
SELECT
p.pubname AS pubname,
n.nspname AS schemaname,
c.relname AS tablename,
c.oid AS relid
FROM pg_publication p
JOIN pg_class c ON p.puballtables AND
pg_is_relation_publishable(c.oid)
JOIN pg_namespace n ON c.relnamespace = n.oid;
But looking at the plans this generates, it will do a sequential scan of
pg_class even if you look for a publication that is not puballtables,
which would suck for the subscriptioncmds.c use case.
We could use the above definition for the documented view and the psql
use case.
We could then create second view that uses the existing definition
CREATE VIEW pg_publication_tables AS
SELECT
P.pubname AS pubname,
N.nspname AS schemaname,
C.relname AS tablename
FROM pg_publication P, pg_class C
JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname));
for use in subscriptioncmds.c. Or don't use a view for that. But the
view is useful because we should preserve this interface across versions.
Or we throw away all the views and use custom code everywhere.
Patch for experimentation attached.
Any ideas?
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 855aff09b862fab7bf8ca49b3b653b296a124484 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Thu, 15 Jun 2017 23:18:39 -0400
Subject: [PATCH] WIP Tweak publication fetching in psql
---
src/backend/catalog/pg_publication.c | 16 ++++++++++++++++
src/backend/catalog/system_views.sql | 23 +++++++++++++++++------
src/bin/psql/describe.c | 5 ++---
src/include/catalog/pg_proc.h | 2 ++
4 files changed, 37 insertions(+), 9 deletions(-)
diff --git a/src/backend/catalog/pg_publication.c
b/src/backend/catalog/pg_publication.c
index 17105f4f2c..fccf642605 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -105,6 +105,22 @@ is_publishable_class(Oid relid, Form_pg_class reltuple)
relid >= FirstNormalObjectId;
}
+Datum
+pg_is_relation_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/backend/catalog/system_views.sql
b/src/backend/catalog/system_views.sql
index 0fdad0c119..895b4001b7 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -255,12 +255,23 @@ CREATE VIEW pg_stats WITH (security_barrier) AS
CREATE VIEW pg_publication_tables AS
SELECT
- P.pubname AS pubname,
- N.nspname AS schemaname,
- C.relname AS tablename
- FROM pg_publication P, pg_class C
- JOIN pg_namespace N ON (N.oid = C.relnamespace)
- WHERE C.oid IN (SELECT relid FROM pg_get_publication_tables(P.pubname));
+ p.pubname AS pubname,
+ n.nspname AS schemaname,
+ c.relname AS tablename,
+ c.oid AS relid
+ FROM pg_publication p
+ JOIN pg_publication_rel pr ON p.oid = pr.prpubid
+ JOIN pg_class c ON pr.prrelid = c.oid
+ JOIN pg_namespace n ON c.relnamespace = n.oid
+ UNION
+ SELECT
+ p.pubname AS pubname,
+ n.nspname AS schemaname,
+ c.relname AS tablename,
+ c.oid AS relid
+ FROM pg_publication p
+ JOIN pg_class c ON p.puballtables AND
pg_is_relation_publishable(c.oid)
+ JOIN pg_namespace n ON c.relnamespace = n.oid;
CREATE VIEW pg_locks AS
SELECT * FROM pg_lock_status() AS L;
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 1c268f0b08..f2c6f1dd75 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2536,9 +2536,8 @@ 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"
+ "SELECT pubname\n"
+ "FROM
pg_catalog.pg_publication_tables\n"
"WHERE relid = '%s'\n"
"ORDER BY 1;",
oid);
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 6c44def6e6..c3b461163e 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_is_relation_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_is_relation_publishable _null_ _null_ _null_ ));
+DESCR("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
