Materialized Views in PostgreSQL are not visible in Information Services

Materialized Views in PostgreSQL are not visible in Information Services

When using the Information Designer, the PostgreSQL Materialized Views are not seen, only the PostgreSQL tables. Postgres JDBC and data source templates are in place and are working in general. Materialized Views were introduced in PostgresSQL in 9.3. Version 9.3 or newer of the postgreSQL JDBC driver (postgresql-9.4-1201.jdbc41.jar tested) will be needed. In the data source template, add "<table-types>TABLE,VIEW,MATERIALIZED VIEW</table-types>".

Here is the PostgreSQL Data Source Template XML:

<jdbc-type-settings>
    <type-name>postgreSQL</type-name>
    <driver>org.postgresql.Driver</driver>
    <connection-url-pattern>jdbc:postgresql://host:port/database</connection-url-pattern>
    <supports-catalogs>true</supports-catalogs>
    <supports-schemas>true</supports-schemas>
    <table-types>TABLE,VIEW,MATERIALIZED VIEW</table-types>
    <use-ansii-style-outer-join>true</use-ansii-style-outer-join>
    <metadata-provider>com.spotfire.ws.im.ds.sql.BasicJDBCMetadataProvider</metadata-provider>
    <supports-procedures>true</supports-procedures>
    <sql-runtime>com.spotfire.ws.im.ds.sql.postgresql.PostgresSQLRuntime</sql-runtime>
</jdbc-type-settings>

To modify the data source template, open the TIBCO Spotfire Server Configuration Tool, and go to Configuration > Data Source Templates. Once the template is modified, save the configuration to the database and restart your TIBCO Spotfire Server. Any new data sources created in the Information Designer will now use this template. For any already existing data sources in the library, those must be edited and re-saved for the template changes to be reflected.

  External: Materialized Views

• https://wiki.postgresql.org/wiki/Materialized_Views

Doc: Opening the configuration tool

• https://docs.tibco.com/pub/spotfire_server/7.11.1/doc/html/TIB_sfire_server_tsas_admin_help/GUID-BCC4FF7E-B527-49F1-BB1C-555CF2E213A3.html

KB: 000028924 Modifications to a data source template not reflected in data source

• https://support.tibco.com/s/article/Modifications-to-a-data-source-template-not-reflected-in-data-source