Base table columns where TOAST-ing strategy has been changed to plain

Query goal:

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.

Query type:

Problem detection (Each row in the result could represent a flaw in the design)

Query reliability:

Medium (Medium number of false-positive results)

Query license:

MIT License

Fixing suggestion:

Change the storage strategy of the column based on the default storage strategy of the column type.

Data source:

system catalog only

SQL query:

Click on query to copy it

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

SQL query	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.

SQL query

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

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 .

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 .

Categories where the query belongs to

Category name	Category 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.

Category name

Category 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.