From: Tom Lane <tgl@sss.pgh.pa.us>
To: pinker <pinker@onet.eu>
cc: pgsql-bugs@postgresql.org
Subject: Re: Postgres 13 signal 11: Segmentation fault tested on 2 independent
 machines
In-reply-to: <1603057047424-0.post@n3.nabble.com>
References: <1603050466566-0.post@n3.nabble.com>
 <1603050580276-0.post@n3.nabble.com> <235509.1603051243@sss.pgh.pa.us>
 <1603051414438-0.post@n3.nabble.com> <236292.1603052323@sss.pgh.pa.us>
 <1603057047424-0.post@n3.nabble.com>
Comments: In-reply-to pinker <pinker@onet.eu>
	message dated "Sun, 18 Oct 2020 14:37:27 -0700"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <246984.1603065496.0@sss.pgh.pa.us>
Date: Sun, 18 Oct 2020 20:00:54 -0400
Message-ID: <247080.1603065654@sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <246984.1603065496.1@sss.pgh.pa.us>

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


------- =_aaaaaaaaaa0
Content-Type: text/plain; name="sql-fn-coercion-crash.sql";
	charset="us-ascii"
Content-ID: <246984.1603065496.2@sss.pgh.pa.us>
Content-Description: sql-fn-coercion-crash.sql
Content-Transfer-Encoding: quoted-printable


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	549683=
2	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	409=
60	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, OU=
T
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 =3D sut.relname AND
ts.table_schema =3D sut.schemaname
    WHERE ts.now =3D (SELECT now FROM closest_metric_table_sizes) AND sut.=
now
=3D (SELECT now FROM closest_metric_stat_user_tables)
ORDER BY 1;
$_$;

SELECT * FROM vacuum_dead_size(now());

------- =_aaaaaaaaaa0--