sql-fn-coercion-crash.sql

CREATE TABLE public.stat_user_tables (
    now timestamp with time zone,
    relid oid,
    schemaname name,
    relname name,
    seq_scan bigint,
    seq_tup_read bigint,
    idx_scan bigint,
    idx_tup_fetch bigint,
    n_tup_ins bigint,
    n_tup_upd bigint,
    n_tup_del bigint,
    n_tup_hot_upd bigint,
    n_live_tup bigint,
    n_dead_tup bigint,
    n_mod_since_analyze bigint,
    last_vacuum timestamp with time zone,
    last_autovacuum timestamp with time zone,
    last_analyze timestamp with time zone,
    last_autoanalyze timestamp with time zone,
    vacuum_count bigint,
    autovacuum_count bigint,
    analyze_count bigint,
    autoanalyze_count bigint
);

CREATE TABLE public.table_sizes (
    now timestamp with time zone,
    oid oid,
    table_schema name,
    table_name name,
    row_estimate real,
    total_bytes bigint,
    index_bytes bigint,
    toast_bytes bigint,
    table_bytes bigint,
    total text,
    index text,
    toast text,
    "table" text
);

COPY public.stat_user_tables (now, relid, schemaname, relname, seq_scan,
seq_tup_read, idx_scan, idx_tup_fetch, n_tup_ins, n_tup_upd, n_tup_del,
n_tup_hot_upd, n_live_tup, n_dead_tup, n_mod_since_analyze, last_vacuum,
last_autovacuum, last_analyze, last_autoanalyze, vacuum_count,
autovacuum_count, analyze_count, autoanalyze_count) FROM stdin;
2020-10-17 01:35:01.935309+02	16802	public	promotion	311	7775	0	0	0	0	0	0	25	0	0	\N	2020-10-07 09:08:41.061339+02	2020-10-16 04:47:58.421258+02	\N	0	1	1	0
2020-10-17 01:35:01.935309+02	123040	public	promotion	2	0	1	0	0	0	0	0	0	0	0	\N	2020-10-07 09:08:41.264478+02	2020-10-16 04:47:31.591375+02	\N	0	1	1	0
\.

COPY public.table_sizes (now, oid, table_schema, table_name, row_estimate,
total_bytes, index_bytes, toast_bytes, table_bytes, total, index, toast,
"table") FROM stdin;
2020-10-16 18:13:57.089157+02	123045	public	promotion	1910	23142400	5496832	139264	17506304	22 MB	5368 kB	136 kB	17 MB
2020-10-16 18:13:57.089157+02	16802	public	promotion	25	57344	16384	\N	40960	56 kB	16 kB	\N	40 kB
\.

CREATE FUNCTION public.abs(interval) RETURNS interval
    LANGUAGE sql IMMUTABLE
    AS $_$ select case when ($1<interval '0') then -$1 else $1 end; $_$;

CREATE FUNCTION public.vacuum_dead_size(i_now timestamp with time zone, OUT
schemaname text, OUT relname text, OUT total_bytes numeric, OUT
dead_tup_size numeric) RETURNS SETOF record
    LANGUAGE sql
    AS $_$

    WITH closest_metric_stat_user_tables AS (
        SELECT now FROM stat_user_tables ORDER BY abs(now-$1) LIMIT 1
    ), closest_metric_table_sizes AS (
        SELECT now FROM table_sizes ORDER BY abs(now - $1) LIMIT 1
    )
SELECT sut.schemaname, sut.relname, ts.total_bytes, 1::numeric
FROM stat_user_tables sut
         LEFT JOIN table_sizes ts ON ts.table_name = sut.relname AND
ts.table_schema = sut.schemaname
    WHERE ts.now = (SELECT now FROM closest_metric_table_sizes) AND sut.now
= (SELECT now FROM closest_metric_stat_user_tables)
ORDER BY 1;
$_$;

SELECT * FROM vacuum_dead_size(now());