Query goal: | Find foreign tables that have been defined based on postgresql_fwd foreign data wrapper and that are updatable. Make sure that updatability is in this case needed. |
Query type: | General (Overview of some aspect of the database.) |
Query license: | MIT License |
Fixing suggestion: | If updatability is not needed then set updatable=false |
Data source: | INFORMATION_SCHEMA only |
SQL query: | Click on query to copy it
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, foreign_table_name, foreign_server_name, foreign_data_wrapper_name, foreign_server_type AS fs_type, foreign_server_version AS fs_version, authorization_identifier AS foreign_server_owner FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name) WHERE foreign_data_wrapper_name='postgres_fdw') SELECT foreign_table_schema, foreign_table_name, foreign_server_name, foreign_data_wrapper_name, fs_type, fs_version, foreign_server_owner FROM postgres_foreign_tables AS pfs WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.foreign_table_options AS fto WHERE pfs.foreign_table_schema=fto.foreign_table_schema AND pfs.foreign_table_name=fto.foreign_table_name AND fto.option_name='updatable' AND fto.option_value='false') ORDER BY foreign_server_name, foreign_table_schema, foreign_table_name; |
SQL query | Description |
---|---|
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, foreign_table_name FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name) WHERE foreign_data_wrapper_name='postgres_fdw') SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (ADD updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements FROM postgres_foreign_tables AS pfs WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.foreign_table_options AS fto WHERE pfs.foreign_table_schema=fto.foreign_table_schema AND pfs.foreign_table_name=fto.foreign_table_name AND fto.option_name='updatable' AND fto.option_value='false') AND NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.foreign_table_options AS fto WHERE pfs.foreign_table_schema=fto.foreign_table_schema AND pfs.foreign_table_name=fto.foreign_table_name AND fto.option_name='updatable') ORDER BY foreign_table_schema, foreign_table_name; | Add the property-value pair in case the external table does not have the "updatable" property. |
WITH postgres_foreign_tables AS (SELECT foreign_table_schema, foreign_table_name FROM INFORMATION_SCHEMA.foreign_tables INNER JOIN INFORMATION_SCHEMA.foreign_servers USING (foreign_server_name) WHERE foreign_data_wrapper_name='postgres_fdw') SELECT format('ALTER FOREIGN TABLE %1$I.%2$I OPTIONS (SET updatable ''false'');', foreign_table_schema, foreign_table_name) AS statements FROM postgres_foreign_tables AS pfs WHERE NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.foreign_table_options AS fto WHERE pfs.foreign_table_schema=fto.foreign_table_schema AND pfs.foreign_table_name=fto.foreign_table_name AND fto.option_name='updatable' AND fto.option_value='false') AND EXISTS (SELECT * FROM INFORMATION_SCHEMA.foreign_table_options AS fto WHERE pfs.foreign_table_schema=fto.foreign_table_schema AND pfs.foreign_table_name=fto.foreign_table_name AND fto.option_name='updatable') ORDER BY foreign_table_schema, foreign_table_name; | Change the property-value pair in case the external table does have the "updatable" property but its value is currently true. |
Collection name | Collection description |
---|---|
Find problems automatically | Queries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not . |
Category name | Category description |
---|---|
Distributed database | Queries of this category provide information about the foreign table mechanism. |
Reference |
---|
https://www.postgresql.org/docs/current/postgres-fdw.html |