Some views not showing columns in Redshift

[sgpeter1]

Some views aren't showing columns in the explorer using Redshift after clicking the expansion arrow. The "native" postgres views (like pg_catalog, etc.) appear working, but anything else isn't appearing.

This only seems to be happening with views, and not tables.

Thanks!

[mikeburgh]

Okay.. Can you try this SQL.. it's looking in the public schema, so you might need to change that string if you have a different schema.

This is a modified version of the introspection, but it will identify if the issue is in getting the data (eg if you don't see the columns in this report) or if something else is going on. Don't worry that it says table_name, Postgres just re used the same underlying table that they use to track tables for views.

Does it show you the columns for each of the views ?

      SELECT c.table_schema
               ,c.table_name 
               ,c.column_name
               ,ordinal_position
               ,data_type
        from pg_catalog.pg_statio_all_tables as st
        inner join pg_catalog.pg_description pgd on (pgd.objoid=st.relid)
        right outer join information_schema.columns c on (pgd.objsubid=c.ordinal_position and  c.table_schema=st.schemaname and c.table_name=st.relname)
        left join pg_table_def ptd on (ptd.tablename = c.table_name and ptd.column = c.column_name)
        where table_schema  = 'public'
        and table_name in ( 
            select table_name
            FROM information_schema.views
            where table_schema = 'public'
        )

[mikeburgh]

Ohh and if you want to do the testing somewhere other than Github.. discord, chat, etc.. let me know.

[sgpeter1]

No problem.

The query returns 0 rows. The issue seems to be pg_catalog.pg_statio_all_tables doesn't contains the views in question.

[mikeburgh]

Thanks for checking which one it was, cannot seem to get any answers on why that's populated on my test redshift DB, and not yours... and it was mainly there to get the column comment.. so dropping it for now.

Can you try this query and confirm it now shows the cols for the views ?

SELECT c.table_schema
        ,c.table_name 
        ,c.column_name
        ,ordinal_position
        ,data_type
from information_schema.columns c 
LEFT join pg_table_def ptd on (ptd.tablename = c.table_name and ptd.column = c.column_name)
where table_schema  = 'public'
and table_name in ( 
    select table_name
    FROM information_schema.views
    where table_schema = 'public'
)

[mikeburgh]

I think this one will work, so I pushed it live in a release today, version 1.2.4 should be good to test for views with this fix in it.

[sgpeter1]

Actually, the view columns still aren't showing. It looks like the views in question are "late-binding views" and don't contain metadata in information_schema.columns. This function returns the necessary data, however.

[mikeburgh]

Ahh, thanks for digging into it and finding out the cause.. I replicated it with those views (no schema binding) and got a fix in place, check out version 1.2.6 and it should good.