The list of all the queries

Updataple foreign tables that refer to another PostgreSQL table

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 statements for generating SQL statements that help us to fix the problem

SQL queryDescription
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 where the query belongs to

Collection nameCollection description
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 where the query belongs to

Category nameCategory description
Distributed databaseQueries of this category provide information about the foreign table mechanism.

Reference materials for further reading

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

The list of all the queries