Redshift Stored procedures not visible in non public schema

[Maneesh Sharma]

Hi SQL Workbench team,

I have installed SQL Workbench/J build 129 and NOT able to see stored procedure which are in non public schema. Public schema stored procedures are visible to us. 

On further reviewing the attached SQL workbench logs - we found :

2023-03-01 19:34:47 DEBUG JdbcProcedureReader.getProcedures() Calling getProcedures() using: catalog=null, schema=null, name=null
2023-03-01 19:34:49 DEBUG ObjectCache.addProcedureList() Added 0 procedures 

Due to this, we are not able to see the procedures in the non public schema(sales). Can you please review the attached logs and make sure that atleast schema name should be pulled correctly.  Then, the redshift jdbc driver will be able to list the procedures.

Later on, we manually ran below commands and it helped us to view the procedures:

WbSetSchema sales
WbListProcs %

Also, I looked at the SQL in the SQLworkbench logs and found that some queries are using postgres function which is not available in Redshift. Example: string_agg function is not there in Redshift. 

Let me know if you can help us on this issue. I can get you the information from Redshift side. 

Thanks & Regards,

Maneesh Sharma 

[Thomas Kellerer]

Note that I don't officially support Amazon Redshift.

If the driver does not return the procedures when schema and catalog are passed as null, then this is a bug in the driver.

The JDBC specification explicitely allows a null value to indicate "all schemas"

https://docs.oracle.com/en/java/javase/11/docs/api/java.sql/java/sql/DatabaseMetaData.html#getProcedures(java.lang.String%2Cjava.lang.String%2Cjava.lang.String)

> null means that the schema name should not be used to narrow the search

The same is true for "catalog"

Does this happen in the DbExplorer?
Did you select a schema in the DbExplorer or did you select "*" in the the schema dropdown?
Selecting the "*" will result in passing NULL to the JDBC driver.

> Also, I looked at the SQL in the SQLworkbench logs and found that
> some queries are using postgres function which is not available in
> Redshift. Example: string_agg function is not there in Redshift.

Well, Redhift claims to be Postgres, so in many places SQL Workbench uses Postgres compatible SQL.

If Redshift isn't compatible with Postgres, it shouldn't pretend to be Postgres.

Thomas

Maneesh Sharma schrieb am 02.03.2023 um 03:27:
> Hi SQL Workbench team,
>
> I have installed SQL Workbench/J build 129 and NOT able to see stored procedure which are in non public schema. Public schema stored procedures are visible to us. 
>
> On further reviewing the attached SQL workbench logs - we found :
>

> *2023-03-01 19:34:47 DEBUG JdbcProcedureReader.getProcedures() Calling getProcedures() using: catalog=null, schema=null, name=null

> 2023-03-01 19:34:49 DEBUG ObjectCache.addProcedureList() Added 0 procedures 

> *