Re: Re: Refresh Publication takes hours and doesn´t finish

Em ter, 21 de mai de 2019 às 14:41, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> escreveu:
>
> =?UTF-8?Q?Fabr=C3=ADzio_de_Royes_Mello?= <fabrizio(at)timbira(dot)com(dot)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.
>

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