Find base table columns in case of which the system can use TOAST technique (due to the data type of the column) and where the toasting strategy has been changed to plain. It means that potentially, if a value in the column is large enough, it is not possible to save the row.
Type
Problem detection (Each row in the result could represent a flaw in the design)
Change the storage strategy of the column based on the default storage strategy of the column type.
Data Source
system catalog only
SQL Query
SELECT n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name,
tp.typname AS column_data_type_name,
tbt.typname AS column_base_data_type_name,
CASE WHEN tp.typstorage='e' THEN 'external'
WHEN tp.typstorage='m' THEN 'main'
WHEN tp.typstorage='x' THEN 'extended'
END AS type_toasting_strategy,
'plain' AS column_toasting_strategy
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS tp ON at.atttypid=tp.oid
LEFT JOIN pg_type AS tbt ON tp.typbasetype=tbt.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r')
AND at.attisdropped='f'
AND at.attnum>0
AND tp.typlen = -1
AND at.attstorage='p'
AND tp.typstorage<>'p'
ORDER BY table_schema, table_name, at.attnum;
SQL statements that help generate fixes for the identified problem.
SQL Query to Generate Fix
Description
WITH storage AS (SELECT n.nspname AS table_schema,
c.relname AS table_name,
at.attname AS column_name,
CASE WHEN tp.typstorage='e' THEN 'external'
WHEN tp.typstorage='m' THEN 'main'
WHEN tp.typstorage='x' THEN 'extended'
END AS type_toasting_strategy,
'plain' AS column_toasting_strategy,
at.attnum
FROM pg_attribute at INNER JOIN pg_class c ON at.attrelid=c.oid
INNER JOIN pg_namespace AS n ON n.oid=c.relnamespace
INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_type AS tp ON at.atttypid=tp.oid
LEFT JOIN pg_type AS tbt ON tp.typbasetype=tbt.oid
WHERE (nspname='public' OR rolname<>'postgres')
AND c.relkind IN ('r')
AND at.attisdropped='f'
AND at.attnum>0
AND tp.typlen = -1
AND at.attstorage='p'
AND tp.typstorage<>'p')
SELECT format('ALTER TABLE %1$I.%2$I ALTER COLUMN %3$I SET STORAGE %4$s;', table_schema, table_name, column_name, type_toasting_strategy) AS statements
FROM storage
ORDER BY table_schema, table_name, attnum;
Change the column.
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
Data at the database physical level
Queries of this category provide information about the disk usage.
Data types
Queries of this category provide information about the data types and their usage.
TOAST
Queries of this category provide information about The Oversized-Attribute Storage Technique.