Missing Schema/Table (gp_toolkit 'pg_catalog, information_schema') when accessing data source through a PostgreSQL Connector.
Article ID: KB0075781
Updated On:
| Products | Versions |
|---|---|
| Spotfire Connector for PostgreSQL | All Versions |
Description
The data tables from the 'pg_catalog','information_schema' schema is missing while accessing through PostgreSQL Connector.
Issue/Introduction
Missing Schema/Table (gp_toolkit 'pg_catalog, information_schema') when accessing data source through a PostgreSQL Connector.
Environment
All Supported Operating Systems
Resolution
When you access a data table through a PostgreSQL Connector, in the back-end the following query is executed (Spotfire Analyst Debug logs)
Example: SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('gp_toolkit','pg_catalog','information_schema')
ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So the output to select data tables/columns depends upon the output of the above query. If you want to pull all the schema data, without the above restriction, then create a system DSN on your local machine where you have installed Spotfire Analyst. To create a System DSN, follow the steps below.
Example: SELECT TABLE_CATALOG,TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ('gp_toolkit','pg_catalog','information_schema')
ORDER BY TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME;
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So the output to select data tables/columns depends upon the output of the above query. If you want to pull all the schema data, without the above restriction, then create a system DSN on your local machine where you have installed Spotfire Analyst. To create a System DSN, follow the steps below.
- Start Button >> Control Panel >> Administrative Tools >> Data Sources(ODBC).
- Open ODBC and select the tab - System DSN or User DSN. Click on Add button.
- Select PostgreSQL ANSI(x64) and click on the Finish button.
- Enter the desired details of the data source and test the connection. Once the connection is successful, save it.
- You can access the created DSN from TIBCO Spotfire Analyst by selecting Spotfire Analyst >> File Menu >> Add data tables >> Database >> ODBC data provider.
- Click on the Configure button. Select the radio button "System or User data source". From the drop-down list, select the newly created PostgreSQL DSN.
- Enter the username and password for the PostgreSQL server. Check the checkbox "Allow Saving Credentials". Click on OK.
- Note that the schema which is not visible through the PostgreSQL connector is a system schema which is why they are being excluded while loading the metadata. Attached are the System DSN setting screenshots. (Filename: PostgreSql_System_DSN_Setting.JPG and PostgreSql_Table_View_from_DSN.JPG).
Additional Information
Attachments
Feedback
Yes
No
Powered by
