Hi,
a recent change in postgresql is causing a segfault when a function is called in filter section, e.g. with a plan like this:
Merge Left Join (cost=0.30..207.75 rows=400 width=7) (actual time=0.006..0.009 rows=0 loops=1)
Merge Cond: (di.itemid = foo.itemid)
Filter: (COALESCE((min(foo.last_update)), '1970-01-01 00:00:00'::timestamp without time zone) < di.download_time)
-> Index Scan using downloaded_images_pkey on downloaded_images di (cost=0.15..29.25 rows=500 width=15) (actual time=0.005..0.005 rows=0 loops=1)
-> Materialize (cost=0.15..135.25 rows=1200 width=40) (never executed)
-> GroupAggregate (cost=0.15..96.25 rows=1200 width=40) (never executed)
Group Key: foo.itemid
-> Index Scan using foo_pkey on foo (cost=0.15..54.25 rows=1200 width=40) (never executed)
At least 13.4-1.pgdg100+1 and 12.8-1.pgdg100+1 are affected. 13.3-1.pgdg100+1 works fine. Here are steps to reproduce the issue.
cat <<EOF> postgresql-segfault.sql
CREATE SCHEMA debug;
CREATE TABLE debug.downloaded_images (
itemid text NOT NULL,
download_time timestamp,
PRIMARY KEY(itemId)
);
INSERT INTO debug.downloaded_images (itemid, download_time) VALUES ('1190300','2021-09-07 11:00:10.255831');
BEGIN;
CREATE TABLE IF NOT EXISTS "debug"."foo"
(itemId TEXT,
last_update TIMESTAMP,
PRIMARY KEY(itemId)
);
DECLARE "test-cursor-crash" CURSOR WITH HOLD FOR
SELECT di.itemId FROM "debug".downloaded_images di
LEFT JOIN (SELECT itemId, MIN(last_update) as last_update FROM "debug"."foo" GROUP BY itemId) computed ON di.itemId=computed.itemId
WHERE COALESCE(last_update, '1970-01-01') < download_time;
FETCH 10000 IN "test-cursor-crash";
COMMIT;
EOF
createdb testdb
psql -d testdb -f postgresql-segfault.sql
here's output from server logs:
[3325] LOG: starting PostgreSQL 12.8 (Debian 12.8-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
[3325] LOG: listening on IPv4 address "127.0.0.1", port 5433
[3325] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
[3326] LOG: database system was shut down at 2021-09-09 10:26:19 UTC
[3325] LOG: database system is ready to accept connections
[3325] LOG: server process (PID 3409) was terminated by signal 11: Segmentation fault
[3325] DETAIL: Failed process was running: COMMIT;
[3325] LOG: terminating any other active server processes
[3330] WARNING: terminating connection because of crash of another server process
[3330] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
[3330] HINT: In a moment you should be able to reconnect to the database and repeat your command.
[3325] LOG: all server processes terminated; reinitializing
[3411] LOG: database system was interrupted; last known up at 2021-09-09 10:27:05 UTC
[3411] LOG: database system was not properly shut down; automatic recovery in progress
Best Regards,
Tomas Barton
