Warn about unindexed foreign key constraints.

[jelder]

I'd like to have is a warning that a foreign key constraint isn't indexed. Static analysis of the migration file can't provide enough information to eliminate false positives for such a rule, so querying a running database would be required.

Such a query might look like this:

WITH indexes AS (
SELECT
    n.nspname as schema_name,
    t.relname as table_name,
    a.attname as column_name
FROM
    pg_class t
JOIN
    pg_index i ON t.oid = i.indrelid
JOIN
    pg_attribute a ON a.attrelid = t.oid AND a.attnum = ANY(i.indkey)
JOIN
    pg_namespace n ON t.relnamespace = n.oid
WHERE
    t.relkind = 'r' -- real tables
    AND array_position(i.indkey, a.attnum) = 0 -- only first column of possibly compound index
),
foreign_keys AS (
SELECT
    n.nspname AS schema_name,
    cl.relname AS table_name,
    a.attname AS column_name,
    ct.conname AS constraint_name
FROM
    pg_constraint ct
    JOIN pg_class cl ON ct.conrelid = cl.oid
    JOIN pg_namespace n ON cl.relnamespace = n.oid
    JOIN pg_attribute a ON a.attnum = ANY(ct.conkey) AND a.attrelid = cl.oid
WHERE
    ct.contype = 'f'
)
SELECT
    schema_name as "schema!",
    table_name as "table!",
    column_name as "name!"
FROM foreign_keys
LEFT JOIN indexes USING (schema_name, table_name, column_name)
WHERE indexes.column_name IS NULL
AND schema_name = ANY($1)

(query is not perfect but you get the idea)