Re: Postgres 13 signal 11: Segmentation fault tested on 2 independent machines - Mailing list pgsql-bugs

pinker <pinker@onet.eu> writes:
> cast to text those 2 columns helped:
> sut.schemaname::TEXT, sut.relname::TEXT

Yeah, I was just concluding that something is failing to handle
the required implicit coercions from name to text.  Haven't found
where yet, though I'm betting 913bbd88d overlooked something.

For the archives' sake, attached is an actual reproduction script,
hopefully without the multiple whitespace problems in the previous
messages.

            regards, tom lane


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
22MB    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());