PostgreSQL memo

Find table size.sql

SELECT
    table_name,
    pg_size_pretty(table_size) AS table_size,
    pg_size_pretty(indexes_size) AS indexes_size,
    pg_size_pretty(total_size) AS total_size,
    round(total_size / sum(total_size) OVER () * 100, 2) AS percent
FROM (
    SELECT
        table_name,
        pg_table_size(table_name) AS table_size,
        pg_indexes_size(table_name) AS indexes_size,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
        WHERE table_schema != 'pg_catalog'
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Kill connections to db.sql

SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE pg_stat_activity.datname = current_database()
  AND pid != pg_backend_pid()

See active queries.sql

SELECT * FROM pg_stat_activity 
WHERE state = 'active'
  AND pid != pg_backend_pid() 
  AND datname = current_database() 
  ORDER BY backend_start;

why_is_empty.sql

-- Detect why a view does not yield any results (PostgreSQL)
-- Very useful for large cascades of views where each view is supposed to return >0 rows.
CREATE OR REPLACE FUNCTION why_is_empty(table_name text, schema text, indent int DEFAULT 0)
RETURNS void
AS $$
DECLARE
    row record;
    row_count int;
BEGIN
    RAISE NOTICE '%', repeat('  ', indent) || table_name;
    FOR row IN (
        SELECT DISTINCT
            dependent_view.relname as dependent_view,
            source_table.relname as source_table,
            source_table.relkind,
            dependent_ns.nspname as dependent_schema
        FROM pg_depend 
            JOIN pg_rewrite ON pg_depend.objid = pg_rewrite.oid 
            JOIN pg_class as dependent_view ON pg_rewrite.ev_class = dependent_view.oid 
            JOIN pg_class as source_table ON pg_depend.refobjid = source_table.oid 
            JOIN pg_namespace dependent_ns ON dependent_ns.oid = dependent_view.relnamespace
            JOIN pg_namespace source_ns ON source_ns.oid = source_table.relnamespace
        WHERE dependent_view.relname = $1
          AND dependent_ns.nspname = $2
          AND source_table.relkind IN ('r', 'v', 'm')
          AND source_table.oid != dependent_view.oid  -- no idea why we get such cases
        ORDER BY 1,2
    ) LOOP
        EXECUTE 'SELECT 1 FROM ' || row.source_table || ' LIMIT 1' INTO row_count;
        IF row_count > 0 THEN
        ELSE
            -- RAISE NOTICE '%!!! No results from %', repeat('  ', indent + 1), row.source_table;
            PERFORM why_is_empty(row.source_table, row.dependent_schema, indent + 1);
        END IF;
    END LOOP;
END;
$$ LANGUAGE plpgsql;