BUG #17906: Segmentation fault and database crash during procedure call

The following bug has been logged on the website:

Bug reference:      17906
Logged by:          Václav Pink
Email address:      vaclav.pink@tietoevry.com
PostgreSQL version: 13.10
Operating system:   running on linux server RHEL 7.9 (x64)
Description:

Good afternoon gentlemans, 

we are fighting more than 3 weeks with error: server process (PID 10028) was
terminated by signal 11: Segmentation fault
This error occur when I call db procedure (whole procedure is below).
Database is in cluster, version of Postgresql is 13.10 and it's runnig on
linux server RHEL 7.9 (x64).
We can't find reason why it fails and whole database crashes.
We will be thankful of any comments, hints, suggestions how to fix it.

If I can provide any other info what can help you, please let me know.

Thank you very much!

Best regards
Vaclav Pink

details from log:
2023-04-21 08:15:58 UTC [10028]: [8-1] user=dm_admin,db=dm_data CONTEXT: 
PL/pgSQL function dm_dev.get_domains(character varying,character
varying,character varying,character varying,jsonb) line 93 at RAISE
2023-04-21 08:15:58 UTC [8848]: [10-1] user=,db= LOG:  server process (PID
10028) was terminated by signal 11: Segmentation fault
2023-04-21 08:15:58 UTC [8848]: [11-1] user=,db= DETAIL:  Failed process was
running: CALL dm_dev.get_domains (
                'te_mf',
            null,
                null,
                null,
                null
                );
2023-04-21 08:15:58 UTC [8848]: [12-1] user=,db= LOG:  terminating any other
active server processes
2023-04-21 08:15:58 UTC [20494]: [3-1] user=snowmirror,db=dm_data WARNING: 
terminating connection because of crash of another server process
2023-04-21 08:15:58 UTC [20494]: [4-1] user=snowmirror,db=dm_data 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.

/var/log/messages : 

Apr 21 10:15:58 tfiubb4dmrepo1 kernel: postmaster[10028]: segfault at
2e19000 ip 00007fbac32f857f sp 00007fff722d8c48 error 4 in
libc-2.17.so[7fbac31a2000+1c4000]


procedure definiton:

CREATE OR REPLACE PROCEDURE dm_dev.get_domains(
    user_name character varying,
    query character varying,
    INOUT response_code character varying,
    INOUT response_message character varying,
    INOUT response_body jsonb)
LANGUAGE 'plpgsql'

AS $BODY$ 

DECLARE
  user_internal boolean;
  user_pii boolean;
  user_domain_path character varying (40);
  user_fixed_filter1 character varying (200);
  user_fixed_filter2 character varying (200);
  user_fixed_filter3 character varying (200);
  user_domain character varying (40);
  record_count integer;
  dd_count integer;
  r_data_row jsonb;
  sql_command character varying (2000) DEFAULT '';   
  hlp_object json;  

BEGIN

CREATE UNLOGGED TABLE IF NOT EXISTS record_temp_unlog (
  id serial,
  unique_call_id character varying     (100),
  r_data jsonb
 );

  CREATE UNLOGGED TABLE IF NOT EXISTS record_temp_unlog2 (
  id serial,
  unique_call_id character varying     (100),
  r_data jsonb
 );

SELECT internal, pii, domain, domain_path, fixed_filter1, fixed_filter2,
fixed_filter3
  INTO user_internal, user_pii, user_domain, user_domain_path,
user_fixed_filter1, user_fixed_filter2, user_fixed_filter3 
  FROM dm.apim_users
 WHERE username = user_name; 

  response_code := '200'; 
  response_message := 'Returns list of ServiceNow domains'; 

sql_command := sql_command || 
'INSERT INTO record_temp_unlog (r_data)
SELECT row_to_json (r)  
  FROM (
SELECT sys_id AS "id",
           name AS "name",
           active AS active,
           type AS type,
           json_build_object( ''id'', parent_id, ''name'', parent_name) AS
parent,
           sys_domain_path AS domain_path,
           full_name AS full_name,
           level AS level,
           sn_instance_code AS sn_instance_code,
           sn_instance AS sn_instance_label,
           case when sys_domain = ' ||''''|| user_domain ||'''' || ' then true
else false end as domain_flag
    FROM dm.domains dom
   WHERE dom.sys_domain_path LIKE ' || ''''  || user_domain_path ||'%'
||''''    
     || ' AND ' || COALESCE(user_fixed_filter1, ' 1 = 1 ') 
     || ' AND ' || COALESCE(user_fixed_filter2, ' 1 = 1 ') 
     || ' AND ' || COALESCE(user_fixed_filter3, ' 1 = 1 ')       
     || ' LIMIT 10 ) r'; 

RAISE NOTICE 'sql_string (%)', sql_command;  
EXECUTE sql_command;

SELECT COUNT(*)
  INTO record_count
  FROM record_temp_unlog;

SELECT MAX(order_by) 
  INTO dd_count
  FROM dm.data_dictionary
 WHERE entity = 'domains'; 

response_body := '{}'::jsonb;

BEGIN 
   FOR i IN 1..record_count LOOP  
     SELECT r_data
       INTO r_data_row
       FROM record_temp_unlog
      WHERE id = i; 
            
      RAISE NOTICE 'pocet radku (%)', r_data_row::text;  
      
      BEGIN 
        FOR j IN 1..dd_count LOOP   
        
        SELECT CASE 
                    WHEN (r_data_row ->> 'domain_flag')::boolean IS TRUE
THEN r_data_row
                    WHEN (dd.internal IS TRUE AND user_internal IS FALSE) OR (dd.pii IS
TRUE AND user_pii IS FALSE) THEN jsonb_set_lax(r_data_row,
string_to_array(dd.name, ','), NULL, false, 'use_json_null') 
                    ELSE r_data_row                    
            END          
        INTO r_data_row
        FROM dm.data_dictionary AS dd 
       WHERE dd.entity = 'domains' 
         AND dd.order_by = j;
     
      r_data_row := jsonb_set_lax(r_data_row,
string_to_array(r_data_row->>'domain_flag', ','), NULL, false,
'use_json_null'); 
        END LOOP;
      END;
    
    INSERT INTO record_temp_unlog2 (id, r_data)
    VALUES (i, r_data_row::jsonb);
    
    RAISE NOTICE 'pocet radku po smycce (%)', r_data_row::text;

         
   END LOOP;
END;
--$do$;

SELECT array_to_json(array_agg(jsonb_strip_nulls(r_data) ORDER BY
r_data->>'id' ))
  INTO response_body
  FROM record_temp_unlog2;    

END $BODY$;