Em ter, 21 de mai de 2019 às 14:41, Tom Lane <
tgl@sss.pgh.pa.us> escreveu:
>
> Fabrízio 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.
>
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
