DbTree and DB Explorer doesn't return any objects ( tables, indexes... ) from RedShift

[Jon Jon]

I turned on the DEBUG in General and looked at the log and found that this statement returns no rows as the default. The getschemas() appears to work but when I put the sql statement listed below in another editor ( SQL developer ) I get the same result-> no rows.  Is this the wrong query or does this connection need catalog privs to read the information schemas and such



select current_database() as rule_catalog,
        n.nspname as rule_schema,
       r.rulename as rule_name,
       c.relname as rule_table,
       case r.ev_type  
         when '1' then 'SELECT'
         when '2' then 'UPDATE'
         when '3' then 'INSERT'
         when '4' then 'DELETE'
         else 'UNKNOWN'
       end as rule_event,
       pg_get_ruledef(r.oid, true) as definition,
       d.description as remarks
from pg_rewrite r  
  join pg_class c on r.ev_class = c.oid
  left join pg_namespace n on n.oid = c.relnamespace
  left join pg_description d on r.oid = d.objoid
 WHERE n.nspname = 'ext_pmpx_tables'
 AND not (c.relkind in ('v','m') and r.ev_type = '1')

 ORDER BY 1, 2

(39ms)
2021-09-07 15:39:18 DEBUG Panel 'Statement' was updated in PT0S (0 bookmarks)
2021-09-07 15:39:25 DEBUG Trying to physically close the connection with id=WbWin-1 TAB-2
2021-09-07 15:39:25 INFO  Disconnecting: [RedShiftDW], ID=WbWin-1 TAB-2
2021-09-07 15:39:25 DEBUG Connection WbWin-1 TAB-2 closed.
2021-09-07 15:39:25 DEBUG Connection with key= was closed. Reference count for this cache is: 1
2021-09-07 15:39:25 DEBUG Removed all entries from the cache
2021-09-07 15:39:25 DEBUG Removed cache for _dev
2021-09-07 15:39:33 DEBUG Selected object types: [DOMAIN, EXTERNAL TABLE, RULE, SEQUENCE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]

2021-09-07 15:39:33 DEBUG Using object types: [DOMAIN, EXTERNAL TABLE, RULE, SEQUENCE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]
2021-09-07 15:39:33 DEBUG Using the following object types for the type filter: [DOMAIN, EXTERNAL TABLE, RULE, SEQUENCE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]
2021-09-07 15:39:33 DEBUG WbWin-1 TAB-1: Retrieving 1 catalogs using getCatalogs() took: 1ms
2021-09-07 15:39:33 DEBUG Loading schemas for catalog: dw_dev
2021-09-07 15:39:33 WARN  WbWin-1 TAB-1: getSchemas() called with catalog parameter, but current connection is not configured to support that Backtrace
java.lang.Exception: Backtrace
    at workbench.db.DbMetadata.getSchemas(DbMetadata.java:2789)
    at workbench.gui.dbobjects.objecttree.TreeLoader.loadSchemas(TreeLoader.java:412)
    at workbench.gui.dbobjects.objecttree.TreeLoader.loadChildren(TreeLoader.java:1127)
    at workbench.gui.dbobjects.objecttree.DbObjectsTree.doLoad(DbObjectsTree.java:600)
    at workbench.gui.dbobjects.objecttree.DbObjectsTree.access$000(DbObjectsTree.java:63)
    at workbench.gui.dbobjects.objecttree.DbObjectsTree$7.run(DbObjectsTree.java:586)

2021-09-07 15:39:33 DEBUG WbWin-1 TAB-1: Retrieving 18 schemas using getSchemas() took 30ms
2021-09-07 15:39:33 DEBUG Loaded 18 schemas. Currently selected types: [DOMAIN, EXTERNAL TABLE, RULE, SEQUENCE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]
2021-09-07 15:39:37 DEBUG Loading schemas for catalog: dw_dev
2021-09-07 15:39:37 WARN  WbWin-1 TAB-1: getSchemas() called with catalog parameter, but current connection is not configured to support that Backtrace
java.lang.Exception: Backtrace
    at workbench.db.DbMetadata.getSchemas(DbMetadata.java:2789)
    at workbench.gui.dbobjects.objecttree.TreeLoader.loadSchemas(TreeLoader.java:412)
    at workbench.gui.dbobjects.objecttree.TreeLoader.loadChildren(TreeLoader.java:1127)
    at workbench.gui.dbobjects.objecttree.DbObjectsTree.reloadNode(DbObjectsTree.java:692)
    at workbench.gui.dbobjects.objecttree.DbTreePanel.reloadSelectedNodes(DbTreePanel.java:536)
    at workbench.gui.dbobjects.objecttree.ReloadNodeAction$1.run(ReloadNodeAction.java:67)

2021-09-07 15:39:37 DEBUG WbWin-1 TAB-1: Retrieving 18 schemas using getSchemas() took 31ms
2021-09-07 15:39:37 DEBUG Loaded 18 schemas. Currently selected types: [DOMAIN, EXTERNAL TABLE, RULE, SEQUENCE, SYSTEM TABLE, SYSTEM TOAST TABLE, SYSTEM VIEW, TABLE, TEMPORARY SEQUENCE, TEMPORARY TABLE, TEMPORARY VIEW, TYPE, VIEW]
2021-09-07 15:39:39 DEBUG Restoring window position for 'workbench.gui.dbobjects.ObjectScripterUI', current screen size: [x: 0, y: 0, w:1920,h:1080], requested position: [x: 710, y: 341, w:500,h:400]
2021-09-07 15:39:52 DEBUG WbWin-1 TAB-1: Calling getTables() using: catalog=dw\_dev, schema=dw, name=null, types=[TABLE]
2021-09-07 15:39:52 DEBUG WbWin-1 TAB-1: Retrieving table list took: 81ms
2021-09-07 15:39:52 DEBUG DatabaseMetaData.getTables() returned: 1: [table_cat], 2: [table_schem], 3: [table_name], 4: [table_type], 5: [remarks], 6: [type_cat], 7: [type_schem], 8: [type_name], 9: [self_referencing_col_name], 10: [ref_generation]
2021-09-07 15:39:52 DEBUG WbWin-1 TAB-1: Processing 0 tables took: 0ms

[Jon Jon]

I do realize that usually only the tables section of a Redshift schema is returned even though RedShift now has various forms of views.

The tables part of the db Tree did work with Oracles SQL developer.  I can try to dig what query it used, and SQL developer used the Postgres driver instead of the amazon one

[Thomas Kellerer]

SQL Workbench uses a standard JDBC API call to get the tables and columns.

Before calling getTables() the driver is asked for the "types" of objects the database supports, and those values are then e.g. used in the DB Explorer to populate the different nodes. The type returned by the driver is then used to get a list of those types.

If it doesn't work with Redshift, then apparently the Redshift JDBC driver either has a bug, or doesn't work as other JDBC drivers.

[Jon Jon]

I realize that Redshift doesn't presrnta full set of DB objects like views, doesn't support indexes... however the table list came through with dbBeaver and SQL Developer.  Therefore it is not the driver rather SQL workbench interface with driver

--
You received this message because you are subscribed to the Google Groups "SQL Workbench/J - DBMS independent SQL tool" group.
To unsubscribe from this group and stop receiving emails from it, send an email to sql-workbenc...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/sql-workbench/c4dc8271-cc73-53d6-d454-3eea89f7ca19%40sql-workbench.net.