Materialized Views in PostgreSQL are not visible in Information Services

book

Article ID: KB0080777

calendar_today

Updated On:

Products	Versions
Spotfire Server	All Versions

Description

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.

Issue/Introduction

Materialized Views in PostgreSQL are not visible in Information Services

Resolution

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.