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.
Type General (Overview of some aspect of the database.)
License MIT License
Fixing Suggestion If updatability is not needed then set updatable=false
Data Source INFORMATION_SCHEMA only
SQL Query
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 statements that help generate fixes for the identified problem.

SQL Query to Generate FixDescription
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.
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, 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 .
Categories

This query is classified under the following categories:

NameDescription
Distributed databaseQueries of this category provide information about the foreign table mechanism.

Further reading and related materials:

Reference
https://www.postgresql.org/docs/current/postgres-fdw.html