Re: Re: Re: Refresh Publication takes hours and doesn´t finish
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= writes: > Em ter, 21 de mai de 2019 às 14:41, Tom Lane 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
Re: Re: Refresh Publication takes hours and doesn´t finish
Em ter, 21 de mai de 2019 às 14:41, Tom Lane escreveu: > > =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= 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. > Interesting, I tried the following commands and got error: postgres=# SELECT version(); version -- PostgreSQL 11.3 (Debian 11.3-1.pgdg90+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit (1 row) postgres=# SELECT session_user; session_user -- postgres (1 row) postgres=# SHOW allow_system_table_mods ; allow_system_table_mods - off (1 row) postgres=# CREATE OR REPLACE VIEW pg_catalog.pg_publication_tables AS postgres-# SELECT postgres-# P.pubname AS pubname, postgres-# N.nspname AS schemaname, postgres-# C.relname AS tablename postgres-# FROM pg_publication P, pg_class C postgres-# JOIN pg_namespace N ON (N.oid = C.relnamespace), postgres-# LATERAL pg_get_publication_tables(P.pubname) postgres-# WHERE C.oid = pg_get_publication_tables.relid; ERROR: permission denied: "pg_publication_tables" is a system catalog But changing "allow_system_table_mods=on" works as expected: postgres=# SHOW allow_system_table_mods ; allow_system_table_mods - on (1 row) postgres=# CREATE OR REPLACE VIEW pg_catalog.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), LATERAL pg_get_publication_tables(P.pubname) WHERE C.oid = pg_get_publication_tables.relid; CREATE VIEW Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: Re: Refresh Publication takes hours and doesn´t finish
=?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= 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
Re: Re: Refresh Publication takes hours and doesn´t finish
I cannot because we created a replication for ALL TABLES -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
Re: Re: Refresh Publication takes hours and doesn´t finish
Em seg, 20 de mai de 2019 às 18:30, Tom Lane escreveu: > > 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; > And why not just JOIN direct with pg_publication_rel ? Regards, -- Fabrízio de Royes Mello Timbira - http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Re: Re: Refresh Publication takes hours and doesn´t finish
PegoraroF10 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
