BUG #17727: A query crashes server process: ... was terminated by signal 7: Bus error / Segmentation Fault - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      17727
Logged by:          Oleg S
Email address:      oserov@clickup.com
PostgreSQL version: 14.5
Operating system:   Ubuntu 22.04 LTS
Description:

I have a query that consistently results in server crash and "server process
... was terminated by signal 7: Bus error" running on any replica or master
of the DB. I also have a more generic query that also crashes other
masters/replicas in prod environment but with 'Segmentation Fault'. I can't
reproduce the same crash in other non-prod environments. I am unable to
provide repro steps that could reproduce this error on a local machine and
looking for more guidance how to debug/mitigate this error. 

The query below is a minimal query that reproduces this error. Removing more
columns, clauses, changing conditions makes the error go away. 

Query:
WITH union_table AS (
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0001.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0002.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0003.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0004.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0005.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0010.assets
    UNION ALL
    SELECT id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy',
'dummy', 'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy',
fingerprint, 'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(),
4000102944500000000
    FROM sh0011.assets
)
SELECT union_table::text
FROM union_table
WHERE id < 4000102944500000000 AND id > 0;

Query plan:
                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Result  (cost=1677.32..104665.28 rows=119283 width=32)
   ->  Append  (cost=1677.32..102876.04 rows=119283 width=32)
         ->  Bitmap Heap Scan on assets  (cost=1677.32..102263.61
rows=119277 width=32)
               Recheck Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
               ->  Bitmap Index Scan on assets_pkey  (cost=0.00..1647.50
rows=119277 width=0)
                     Index Cond: ((id < '4000102944500000000'::bigint) AND
(id > 0))
         ->  Index Scan using assets_pkey on assets assets_1
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
         ->  Index Scan using assets_pkey on assets assets_2
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
         ->  Index Scan using assets_pkey on assets assets_3
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
         ->  Index Scan using assets_pkey on assets assets_4
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
         ->  Index Scan using assets_pkey on assets assets_5
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
         ->  Index Scan using assets_pkey on assets assets_6
(cost=0.43..2.67 rows=1 width=32)
               Index Cond: ((id < '4000102944500000000'::bigint) AND (id >
0))
 JIT:
   Functions: 29
   Options: Inlining false, Optimization false, Expressions true, Deforming
true
(21 rows)

PG Version:
 PostgreSQL 14.5 (Ubuntu 14.5-0ubuntu0.22.04.1) on
aarch64-unknown-linux-gnu, compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0,
64-bit

Log:

2022-12-21 10:48:24 PST [1466]: [25-1] user=,db=,app= LOG:  database system
is ready to accept read-only connections
2022-12-21 10:48:24 PST [14192]: [6-1] user=,db=,app= LOG:  invalid record
length at DB2/B9E9DE70: wanted 24, got 0
2022-12-21 10:48:24 PST [14198]: [1-1] user=,db=,app= LOG:  started
streaming WAL from primary at DB2/B9000000 on timeline 1
2022-12-21 10:49:14 PST [1466]: [26-1] user=,db=,app= LOG:  server process
(PID 15453) was terminated by signal 7: Bus error
2022-12-21 10:49:14 PST [1466]: [27-1] user=,db=,app= DETAIL:  Failed
process was running: WITH union_table AS (
        SELECT
            id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy', 'dummy',
'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy', fingerprint,
'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(), 4000102944500000000
        FROM
            sh0001.assets
        UNION ALL
        SELECT
            id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy', 'dummy',
'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy', fingerprint,
'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(), 4000102944500000000
        FROM
            sh0002.assets
        UNION ALL
        SELECT
            id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy', 'dummy',
'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy', fingerprint,
'dummy', 'dummy', 'dummy', 'dummy', 'dummy', NOW(), 4000102944500000000
        FROM
            sh0003.assets
        UNION ALL
        SELECT
            id, 'dummy', NOW(), 'dummy', NOW(), NOW(), NOW(), 'dummy', 'dummy',
'dummy', NOW(), NOW(), 'dummy', 'dummy', 'dummy', 'dummy', fingerprint,
'dummy', 'dumm
2022-12-21 10:49:14 PST [1466]: [28-1] user=,db=,app= LOG:  terminating any
other active server processes
2022-12-21 10:49:14 PST [15643]: [1-1] user=postgres,db=<>,app=[unknown]
FATAL:  the database system is in recovery mode
2022-12-21 10:49:14 PST [1466]: [29-1] user=,db=,app= LOG:  all server
processes terminated; reinitializing