BUG #16610: server process was terminated by signal 11: Segmentation fault

The following bug has been logged on the website:

Bug reference: 16610
Logged by: Sylwester Kogowski
Email address: s(dot)kogowski(at)netfactory(dot)pl
PostgreSQL version: 12.2
Operating system: Debian 8
Description:

Hi,
I had a following error on our development server (luckily):
server process (PID 7748) was terminated by signal 11: Segmentation fault
This was always after the same statement: DELETE FROM "blockades" WHERE
"saas_user_id" = 92
However, DELETE FROM "blockades" WHERE id=.... worked ok.
Column saas_user_id is an indexed non-unique column. There are triggers on
this table that where executed and those triggers use pg_notify to
communicate with external processes (don't know if that had influence). The
statement was not executed in a transaction. No replication is used or
anything like that. The table is not partitioned, though a trigger modifies
'blockades_optimized' table which is partitioned and uses a btree_gist
indexing (don't know if that matters, because clearing blockades table was
enough to remove the error, so I don't think that blockades_optimized or
pg_notify had any influence on it).

None other statements were affected in this error.
The error was fixed by TRUNCATE TABLE blockades CASCADE;
This was a dev server, so we could've just clear that table without
repercussions, but it would've been a bigger problem on the production
server (though I don't imagine a scenario where we would remove multiple
blockades at the same time on production server).
I will provide more info if I will encounter the error again, just reply to
this with list of information that you will need (or an advice if this error
can be circumvented somehow).

Logs from postgresql:
2020-09-09 10:19:56.413 CEST [3512] LOG: server process (PID 7748) was
terminated by signal 11: Segmentation fault
2020-09-09 10:19:56.413 CEST [3512] DETAIL: Failed process was running:
DELETE FROM "blockades" WHERE "saas_user_id" = 92
2020-09-09 10:19:56.413 CEST [3512] LOG: terminating any other active
server processes
2020-09-09 10:19:56.413 CEST [7809] root(at)nfhotel WARNING: terminating
connection because of crash of another server process
2020-09-09 10:19:56.413 CEST [7809] root(at)nfhotel DETAIL: The postmaster has
commanded this server process to roll back the current transaction and exit,
because another server process exited abnormally and poss
ibly corrupted shared memory.
2020-09-09 10:19:56.413 CEST [7809] root(at)nfhotel HINT: In a moment you
should be able to reconnect to the database and repeat your command.
2020-09-09 10:19:56.414 CEST [7722] root(at)nfhotel WARNING: terminating
connection because of crash of another server process
2020-09-09 10:19:56.414 CEST [7722] root(at)nfhotel 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.
(... and all other connections were also disconnected)

Logs from messages
Sep 9 10:19:56 NFhotel-dev kernel: [97212.380440] postgres[7748]: segfault
at 0 ip 0000559032e2c5d8 sp 00007fffa3243750 error 4 in
postgres[559032d5f000+77b000]

The 'blockades' table has following construction:
-- Table: nfhotel.blockades

-- DROP TABLE nfhotel.blockades;

CREATE TABLE nfhotel.blockades
(
id integer NOT NULL DEFAULT nextval('blockades_id_seq'::regclass),
saas_user_id integer NOT NULL,
bl_name character varying(400) COLLATE pg_catalog."default" NOT NULL,
bl_deleted integer DEFAULT 0,
bl_type_id integer NOT NULL DEFAULT 1,
b_created timestamp without time zone NOT NULL DEFAULT
CURRENT_TIMESTAMP,
b_modified timestamp without time zone,
CONSTRAINT "idx_16863_PRIMARY" PRIMARY KEY (id),
CONSTRAINT "FK_blockades_blockade_types" FOREIGN KEY (bl_type_id)
REFERENCES nfhotel.blockade_types (id) MATCH SIMPLE
ON UPDATE CASCADE
ON DELETE CASCADE
)

TABLESPACE pg_default;

-- Index: idx_16863_FK_blockades_blockade_types

-- DROP INDEX nfhotel."idx_16863_FK_blockades_blockade_types";

CREATE INDEX "idx_16863_FK_blockades_blockade_types"
ON nfhotel.blockades USING btree
(bl_type_id ASC NULLS LAST)
TABLESPACE pg_default;
-- Index: idx_16863_idx_saas_user_id

-- DROP INDEX nfhotel.idx_16863_idx_saas_user_id;

CREATE INDEX idx_16863_idx_saas_user_id
ON nfhotel.blockades USING btree
(saas_user_id ASC NULLS LAST)
TABLESPACE pg_default;

-- Trigger: blockades_optimized_on_delete

-- DROP TRIGGER blockades_optimized_on_delete ON nfhotel.blockades;

CREATE TRIGGER blockades_optimized_on_delete
AFTER DELETE
ON nfhotel.blockades
REFERENCING OLD TABLE AS old_blockades
FOR EACH STATEMENT
EXECUTE PROCEDURE nfhotel.on_blockades_changed();

-- Trigger: blockades_optimized_on_insert

-- DROP TRIGGER blockades_optimized_on_insert ON nfhotel.blockades;

CREATE TRIGGER blockades_optimized_on_insert
AFTER INSERT
ON nfhotel.blockades
REFERENCING NEW TABLE AS new_blockades
FOR EACH STATEMENT
EXECUTE PROCEDURE nfhotel.on_blockades_changed();

-- Trigger: blockades_optimized_on_update

-- DROP TRIGGER blockades_optimized_on_update ON nfhotel.blockades;

CREATE TRIGGER blockades_optimized_on_update
AFTER UPDATE
ON nfhotel.blockades
REFERENCING NEW TABLE AS new_blockades OLD TABLE AS old_blockades
FOR EACH STATEMENT
EXECUTE PROCEDURE nfhotel.on_blockades_changed();

CREATE FUNCTION nfhotel.on_blockades_changed()
RETURNS trigger
LANGUAGE 'plpgsql'
COST 100
VOLATILE NOT LEAKPROOF
AS $BODY$
BEGIN
RAISE NOTICE 'on_blockades_changed, op:%', TG_OP;
IF TG_OP = 'DELETE' THEN



RAISE NOTICE 'on_blockades_changed, op:DELETE, bo count:%', (SELECT COUNT(*)

FROM old_blockades oldb
INNER JOIN blockades_optimized AS bo ON oldb.id = bo.blockade_id AND
bo.saas_user_id = oldb.saas_user_id);

PERFORM send_availability_synchronization(oldb.saas_user_id::int,
array_agg((range_begin_to_date(bo.date_range),range_end_to_date(bo.date_range),bo.room_id)::availability_change))

FROM old_blockades oldb
INNER JOIN blockades_optimized AS bo ON oldb.id = bo.blockade_id AND
bo.saas_user_id = oldb.saas_user_id
INNER JOIN rooms r ON r.id = bo.room_id AND r.ro_enabled = 1 AND
r.ro_type=1
GROUP BY oldb.saas_user_id,bo.room_id;

DELETE FROM blockades_optimized AS bo
USING old_blockades oldb
WHERE oldb.id = bo.blockade_id AND bo.saas_user_id =
oldb.saas_user_id;

ELSIF TG_OP = 'UPDATE' THEN

PERFORM send_availability_synchronization(oldb.saas_user_id::int,
array_agg((range_begin_to_date(bo.date_range),range_end_to_date(bo.date_range),bo.room_id)::availability_change))

FROM old_blockades oldb
INNER JOIN blockades_optimized AS bo ON oldb.id = bo.blockade_id AND
bo.saas_user_id = oldb.saas_user_id
INNER JOIN rooms r ON r.id = bo.room_id AND r.ro_enabled = 1 AND
r.ro_type=1
GROUP BY oldb.saas_user_id,bo.room_id;

DELETE FROM blockades_optimized AS bo
USING old_blockades oldb
WHERE oldb.id = bo.blockade_id AND bo.saas_user_id =
oldb.saas_user_id;


INSERT INTO blockades_optimized
(blockade_id,room_id,term_id,room_standard_id,date_range,saas_user_id)
(SELECT
newb.id,br.room_id,bt.id,r.room_standard_id,int4range(date_to_number(bt.bl_from),
date_to_number(bt.bl_to)),newb.saas_user_id
FROM new_blockades newb
INNER JOIN blockades_terms bt ON bt.blockade_id = newb.id
INNER JOIN blockades_rooms br on br.term_id = bt.id
INNER JOIN rooms r ON r.id = br.room_id
where newb.bl_deleted = 0 AND bt.bl_to > current_date)
ON CONFLICT DO NOTHING;


PERFORM 1 FROM (
WITH requires_sync AS (
SELECT newb.saas_user_id,br.room_id,
(bt.bl_from,bt.bl_to,br.room_id)::availability_change AS change
FROM new_blockades newb
INNER JOIN blockades_terms bt ON bt.blockade_id = newb.id AND bt.bl_to
> current_date
INNER JOIN blockades_rooms br on br.term_id = bt.id
INNER JOIN rooms r ON r.id = br.room_id and
r.ro_enabled=1 and r.ro_type=1
WHERE newb.bl_deleted = 0
)
SELECT send_availability_synchronization(saas_user_id::int,
array_agg(change))
FROM requires_sync
GROUP BY saas_user_id, room_id) AS smth;


ELSIF TG_OP = 'INSERT' THEN

INSERT INTO blockades_optimized
(blockade_id,room_id,term_id,room_standard_id,date_range,saas_user_id)
(SELECT
newb.id,br.room_id,bt.id,r.room_standard_id,int4range(date_to_number(bt.bl_from),
date_to_number(bt.bl_to)),newb.saas_user_id
FROM new_blockades newb
INNER JOIN blockades_terms bt ON bt.blockade_id = newb.id
INNER JOIN blockades_rooms br on br.term_id = bt.id
INNER JOIN rooms r ON r.id = br.room_id
where newb.bl_deleted = 0 AND bt.bl_to > current_date)
ON CONFLICT DO NOTHING;


PERFORM send_availability_synchronization(newb.saas_user_id::int,
array_agg((bt.bl_from,bt.bl_to,br.room_id)::availability_change))
FROM new_blockades newb
INNER JOIN blockades_terms bt ON bt.blockade_id = newb.id AND
bt.bl_to > current_date
INNER JOIN blockades_rooms br on br.term_id = bt.id
INNER JOIN rooms r ON r.id = br.room_id AND r.ro_enabled = 1 AND
r.ro_type=1
WHERE newb.bl_deleted = 0
GROUP BY newb.saas_user_id,br.room_id;
end if;
return NULL;
END
$BODY$;

CREATE OR REPLACE FUNCTION nfhotel.send_availability_synchronization(
saas_user_id integer,
changes availability_change[])
RETURNS void
LANGUAGE 'plpgsql'

COST 100
VOLATILE

AS $BODY$
DECLARE
lower_range int;
upper_range int;
change_range int4range;
change availability_change;
BEGIN
FOREACH change in ARRAY changes LOOP
change_range :=
int4range(date_to_number(change._from),date_to_number(change._to),'[]');
PERFORM 1 FROM pending_availability_changes pac WHERE
pac.saas_user_id = send_availability_synchronization.saas_user_id AND
pac.room_id = change.room_id
AND pac.date_range && change_range FOR UPDATE;
lower_range := LEAST((SELECT MIN(lower(pac.date_range)) FROM
pending_availability_changes pac
WHERE pac.saas_user_id =
send_availability_synchronization.saas_user_id AND pac.room_id =
change.room_id
AND pac.date_range @> lower(change_range) ),
date_to_number(change._from));
upper_range := GREATEST((SELECT MAX(upper(pac.date_range)) FROM
pending_availability_changes pac
WHERE pac.saas_user_id =
send_availability_synchronization.saas_user_id AND pac.room_id =
change.room_id
AND pac.date_range @> upper(change_range) ),
date_to_number(change._to));
change_range := int4range(lower_range,upper_range,'[]');
DELETE FROM pending_availability_changes WHERE
pending_availability_changes.saas_user_id =
send_availability_synchronization.saas_user_id AND room_id = change.room_id

AND date_range && change_range;
INSERT INTO pending_availability_changes(saas_user_id, room_id,
date_range) VALUES(send_availability_synchronization.saas_user_id,
change.room_id,

change_range);

end loop;
RAISE NOTICE 'send_availability_synchronization for saas:%, json:%',
saas_user_id,json_build_object('command_type','changes_detected','node_based_prices',1,
'saas_user_id',saas_user_id, 'changes_array',changes)::text;
PERFORM pg_notify('nfhotel_availability_sync',
json_build_object('command_type','changes_detected','node_based_prices',1,
'saas_user_id',saas_user_id, 'changes_array',changes)::text);
END
$BODY$;

As you can see, saas_user_id is not a unique column.
Server configuration is default, except for adding btree_gist and pgcrypto
extensions (not used in this particular table).
Postgresql version is "PostgreSQL 12.2 (Debian 12.2-2.pgdg80+1) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u2) 4.9.2,
64-bit"

I will update postgresql to a newer version and see if it helps, but in case
that the error will come up again, please instruct me if you need more
information.