Thread: Refresh Publication takes hours and doesn´t finish

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)));

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 the PostgreSQL - general mailing list archive at Nabble.com.

I tried sometime ago ... but with no responses, I ask you again.
pg_publication_tables is a view that is used to refresh publication, but as
we have 15.000 tables, it takes hours and doesn´t complete. If I change that
view I can have an immediate result. The question is: Can I change that view
? There is some trouble changing those system views ?

Original View is ...
create view pg_catalog.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)));
This way it takes 45 minutes to respond.

I changed it to ...
create or replace pg_catalog.view pg_publication_tables as SELECT p.pubname,
n.nspname AS schemaname, c.relname AS tablename from pg_publication p inner
join pg_get_publication_tables(p.pubname) pt on true inner join pg_class c
on pt.relid = c.oid inner join pg_namespace n ON (n.oid = c.relnamespace);
This one takes just one or two seconds.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

PegoraroF10 <marcos@f10.com.br> writes:
> I tried sometime ago ... but with no responses, I ask you again.
> pg_publication_tables is a view that is used to refresh publication, but as
> we have 15.000 tables, it takes hours and doesn´t complete. If I change that
> view I can have an immediate result. The question is: Can I change that view
> ? There is some trouble changing those system views ?

> Original View is ...
> create view pg_catalog.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)));
> This way it takes 45 minutes to respond.

> I changed it to ...
> create or replace pg_catalog.view pg_publication_tables as SELECT p.pubname,
> n.nspname AS schemaname, c.relname AS tablename from pg_publication p inner
> join pg_get_publication_tables(p.pubname) pt on true inner join pg_class c
> on pt.relid = c.oid inner join pg_namespace n ON (n.oid = c.relnamespace);
> This one takes just one or two seconds.

Hmm ... given that pg_get_publication_tables() shouldn't return any
duplicate OIDs, it does seem unnecessarily inefficient to put it in
an IN-subselect condition.  Peter, is there a reason why this isn't
a straight lateral join?  I get a much saner-looking plan from

    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));
+        JOIN pg_namespace N ON (N.oid = C.relnamespace),
+        LATERAL pg_get_publication_tables(P.pubname)
+   WHERE C.oid = pg_get_publication_tables.relid;

            regards, tom lane

I cannot because we created a replication for ALL TABLES



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Restart Postgres means exactly what ? We tried just restart the service but
we tried to refresh publication the old view was used because it took 2hours
and gave us a timeout.

I found some people talking that I need to initdb, but initdb means recreate
entirely my database or just reinstall my postgres server ?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabrizio@timbira.com.br> writes:
> As I said before to change system catalog you should set
> "allow_system_table_mods=on" and restart PostgreSQL service.
> After that you'll able to recreate the "pg_catalog.pg_publication_tables"
> system view. (You can use the Tom's suggestion using LATERAL)

It's a view, not a table, so I don't think you need
allow_system_table_mods.  A quick test here says that being
superuser is enough to do a CREATE OR REPLACE VIEW on it.

            regards, tom lane

=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabrizio@timbira.com.br> writes:
> Em ter, 21 de mai de 2019 às 14:41, Tom Lane <tgl@sss.pgh.pa.us> escreveu:
>> It's a view, not a table, so I don't think you need
>> allow_system_table_mods.  A quick test here says that being
>> superuser is enough to do a CREATE OR REPLACE VIEW on it.

> Interesting, I tried the following commands and got error:

Oh, huh, this is something that changed recently in HEAD ---
since commit 2d7d946cd, stuff created by system_views.sql
is not protected as though it were a system catalog.

So in released versions, yes you need allow_system_table_mods=on.
Sorry for the misinformation.

            regards, tom lane

On 2019-05-20 23:30, Tom Lane wrote:
> Hmm ... given that pg_get_publication_tables() shouldn't return any
> duplicate OIDs, it does seem unnecessarily inefficient to put it in
> an IN-subselect condition.  Peter, is there a reason why this isn't
> a straight lateral join?  I get a much saner-looking plan from
> 
>     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));
> +        JOIN pg_namespace N ON (N.oid = C.relnamespace),
> +        LATERAL pg_get_publication_tables(P.pubname)
> +   WHERE C.oid = pg_get_publication_tables.relid;

No reason I think, just didn't quite manage to recognize the possibility
of using LATERAL at the time.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services