*We use logical replication from a PG version 10.6 to a 11.2. Both are Ubuntu
16.04.We have a hundred schemas with more or less a hundred tables, so
number of tables is about 10.000. All replication is ok but when we try to
do a REFRESH SUBSCRIPTION because we added a new schema, it takes hours and
doesn´t finish. Then, if I go to our master server and do a select * from
pg_publication_tables it doesn´t respond too. Then, analysing the source of
view pg_publication_tables ...*
create view pg_publication_tables as SELECT p.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
pg_get_publication_tables.relid FROM pg_get_publication_tables((p.pubname)
:: text) pg_get_publication_tables (relid)));
If we run both statements of that view separately
SELECT string_agg(pg_get_publication_tables.relid::text,',') FROM
pg_get_publication_tables(('MyPublication')::text) pg_get_publication_tables
(relid);
*put all those oids retrieved on that IN of the view*
select * from pg_Class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE
c.oid IN (
*OIDs List*
);
*Then it responds immediatelly*
So, the question is .. can we change this view to select faster ? Just
rewriting that view to a better select will solve ?Is this view used by
REFRESH SUBSCRIPTION ? We think yes because if we run refresh subscription
or select from view it doesn´t respond, so ...
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html