The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

v_internal.vs_view_tables incorrect data on schemes

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

Answers

  • SruthiASruthiA Employee

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

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

  • view_tables gives many tables where the scheme is not put down

  • Jim_KnicelyJim_Knicely Administrator
    edited November 27

    @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.
    
  • 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

  • @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| :'(

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.