v_internal.vs_view_tables incorrect data on schemes

ilya2

select t2.oid is not null is_not_null, count(*) cnt
from v_internal.vs_view_tables t1
left join vs_schemata t2 ON reference_schema_id = oid
group by 1
order by 2 desc;

is_not_null|cnt |
-----------|----|
false      |6337|
true       | 923|

for this reason, incorrect - view_tables

SruthiA

Could you please provide more description? Is the query showing wrong count of schemas?

ilya2

This request is made on a replica cluster. Part of the schemes in vs_view_tables have id, as on master cluster

ilya2

view_tables gives many tables where the scheme is not put down

Jim_Knicely

@ilya2 -
It is possible to have a view reference a schema that does not exist. In that case the view is invalid but the meta-data is still stored in the VS_VIEW_TABLES system table...

Example:
dbadmin=> CREATE SCHEMA nada;
CREATE SCHEMA

dbadmin=> CREATE TABLE nada.test AS SELECT * FROM public.test LIMIT 1;
CREATE TABLE

dbadmin=> CREATE SCHEMA adan;
CREATE SCHEMA

dbadmin=> CREATE TABLE adan.test AS SELECT * FROM public.test LIMIT 1;
CREATE TABLE

dbadmin=> CREATE VIEW adan.test_vw AS SELECT a.mgr FROM adan.test a JOIN nada.test b ON b.mgr = a.mgr;
CREATE VIEW

dbadmin=> SELECT table_schema, table_name, reference_schema_id, reference_table_name FROM vs_view_tables WHERE table_schema = 'adan';
 table_schema | table_name | reference_schema_id | reference_table_name
--------------+------------+---------------------+----------------------
 adan         | test_vw    |   45035996273849516 | test
 adan         | test_vw    |   45035996273849546 | test
(2 rows)

dbadmin=> SELECT name FROM vs_schemata WHERE oid IN (45035996273849516, 45035996273849546);
 name
------
 nada
 adan
(2 rows)

dbadmin=> select t2.oid is not null is_not_null, count(*) cnt
dbadmin-> from v_internal.vs_view_tables t1
dbadmin-> left join vs_schemata t2 ON reference_schema_id = oid
dbadmin-> group by 1
dbadmin-> order by 2 desc;
 is_not_null | cnt
-------------+-----
 t           | 491
(1 rows)

dbadmin=> DROP SCHEMA nada CASCADE;
DROP SCHEMA

dbadmin=> SELECT table_schema, table_name, reference_schema_id, reference_table_name FROM vs_view_tables WHERE table_schema = 'adan';
 table_schema | table_name | reference_schema_id | reference_table_name
--------------+------------+---------------------+----------------------
 adan         | test_vw    |   45035996273849516 | test
 adan         | test_vw    |   45035996273849546 | test
(2 rows)

dbadmin=> SELECT name FROM vs_schemata WHERE oid IN (45035996273849516, 45035996273849546);
 name
------
 adan
(1 row)

dbadmin=> select t2.oid is not null is_not_null, count(*) cnt
dbadmin-> from v_internal.vs_view_tables t1
dbadmin-> left join vs_schemata t2 ON reference_schema_id = oid
dbadmin-> group by 1
dbadmin-> order by 2 desc;
 is_not_null | cnt
-------------+-----
 t           | 490
 f           |   1
(2 rows)

dbadmin=> \dv adan.test_vw;
WARNING 3791:  Invalid view test_vw: Schema "nada" does not exist
No matching relations found.

ilya2

That's just the point that schemes exist and I can execute view.
The problem is observed only with the view, which were created on the master and were replicated to the replica. There are view immediately created on the replica, then there are no problems

ilya2

@Jim_Knicely

select 
    COALESCE(t3.not_invalid_view, False) as not_invalid_view, t2.oid is not null is_not_null, count(*) cnt
from v_internal.vs_view_tables t1
left join vs_schemata t2 ON reference_schema_id = oid
left join (select distinct table_schema, table_name, True as not_invalid_view from view_columns) t3 ON t1.table_schema=t3.table_schema and t1.table_name=t3.table_name
where not is_system_view
    and not is_virtual_view
group by 1,2
order by 3 desc;

not_invalid_view|is_not_null|cnt |
----------------|-----------|----|
true            |false      |5954|
true            |true       | 438|
false           |false      | 392|

true |false |5954|