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.)
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 Fix
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.
Collections
This query belongs to the following collections:
Name
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 .
Categories
This query is classified under the following categories:
Name
Description
Distributed database
Queries of this category provide information about the foreign table mechanism.