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.kogowski@netfactory.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@nfhotel WARNING:  terminating
connection because of crash of another server process
2020-09-09 10:19:56.413 CEST [7809] root@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@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@nfhotel WARNING:  terminating
connection because of crash of another server process
2020-09-09 10:19:56.414 CEST [7722] root@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.