Spotfire® Server Information Designer - Fix issues with PostgreSQL Functions Not Showing in List of Database Objects

Overview

With more recent versions of PostgreSQL and Spotfire, functions in the PostgreSQL database will not show up in the database metadata in Information Designer.  In previous versions of Spotfire, functions in PostgreSQL would be listed in Information Designer.  This article provides a custom metadata provider to use in Information Designer to fix this issue.

The issue is that the PostgreSQL JDBC driver was modified around version 42.2.11.  The PostgreSQL driver changed behavior and added a DatabaseMetaData.getFunctions() around version 11-12 of the driver. (See https://github.com/pgjdbc/pgjdbc/pull/1723)  Spotfire Information Services uses the default BasicJDBCMetadataProvider for the PostgreSQL data source which does not know to call the getFunctions() call so functions are not returned during the query for metadata about database objects.

The PostgreSQL functions do show up in Spotfire 10.3.5 since the PostgreSQL JDBC driver is version 42.2.5.  In Spotfire 10.10.0, the functions do not show up because the PostgreSQL JDBC driver is version 42.2.12.

Solution

We can override the metadata provider by modifying the data source template for PostgreSQL in the Spotfire Server configuration tool.  I created a specific JDBC Metadata Provider for PostgreSQL, PostgreSQLJDBCMetadataProvider, which appears to fix this issue.  (Note: There may be other issues that have not been found yet.)  The compiled custom JDBC metadata provider is attached to this page.  

In order to install and configure the environment to use this custom metadata provider, the following steps are required:

(1) Unzip the file attached to this article and copy the PostgreSQLJDBCMetadataProvider.jar file to the tibco/<version>/tomcat/custom-ext directory:

(2) Open the Spotfire Server Configuration Tool and modify the PostgreSQL data source template adding in a custom metadata provider:

 <metadata-provider>com.tibco.spotfireps.server.is.PostgreSQLJDBCMetadataProvider</metadata-provider>

(3) Save the configuration change to the database.

(4) Stop and restart Spotfire Server(s).

(5) Open Spotfire Analyst, open Information Designer, and browse the metadata for a PostgreSQL data source.  The functions should now show up and be able to be created and used in Spotfire.

The custom code had to override a couple of metadata calls:

- getProcedures - to also get Functions from the Postgres DatabaseMetaData class.

- getProcedureMetadata - to call the metadata call getFunctions if the procedure name was not found when calling the metadata call getProcedures.

postgresqljdbcmetadataprovider.zip