From: | Fabrízio de Royes Mello <fabrizio(at)timbira(dot)com(dot)br> |
---|---|
To: | PegoraroF10 <marcos(at)f10(dot)com(dot)br> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Refresh Publication takes hours and doesn´t finish |
Date: | 2019-05-20 21:25:49 |
Message-ID: | CAPfkCSCMDNB4gGhmULe4XWLcnRZcV6q3aPVpSMWkJoDEYbOBpA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
Em seg, 20 de mai de 2019 às 17:18, PegoraroF10 <marcos(at)f10(dot)com(dot)br>
escreveu:
>
> 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 ?
>
You really need a publication with a lot of relations??? If you can split
it in several publications your life should be easy.
>
> 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 really don't know why we did it... because pg_get_publication_tables
doesn't have any special behavior different than get relations assigned to
publications.
>
> 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.
>
Even better, you can go direct by system catalogs:
SELECT p.pubname,
n.nspname AS schemaname,
c.relname AS tablename
FROM pg_publication p
JOIN pg_publication_rel pr ON pr.prpubid = p.oid
JOIN pg_class c ON c.oid = pr.prrelid
JOIN pg_namespace n ON n.oid = c.relnamespace;
To change it, before you'll need to set "allow_system_table_mods=on" and
restart PostgreSQL.
Regards,
--
Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-20 21:30:04 | Re: Re: Refresh Publication takes hours and doesn´t finish |
Previous Message | Will Hartung | 2019-05-20 21:14:16 | Re: Loading table with indexed jsonb field is stalling |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-05-20 21:30:04 | Re: Re: Refresh Publication takes hours and doesn´t finish |
Previous Message | Mark Wong | 2019-05-20 21:21:53 | Re: Why is infinite_recurse test suddenly failing? |