BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery

The following bug has been logged on the website:

Bug reference: 19779
Logged by: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
Email address: simon(dot)tenbusch(at)cloudelevator(dot)de
PostgreSQL version: 18.6
Operating system: Linux 6.18 x86_64, docker, alpine/debian
Description:

A client backend is terminated by signal 11 when an UPDATE locks rows via
FOR UPDATE in a CTE that is joined to a non-materialized UNION ALL subquery
under row-level security, and three of the rows to be locked were updated by
a concurrent transaction. The postmaster then restarts all server processes.

Reproduced on:

PostgreSQL 18.6 on x86_64-pc-linux-musl, compiled by gcc (Alpine 15.2.0)
15.2.0, 64-bit (image postgres:18-alpine), psql 18.6
PostgreSQL 17.11 (Debian 17.11-1.pgdg13+2) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit (image postgres:17), psql
17.11
also 17.11 on postgres:17-alpine

Platform: Linux 6.18.44 x86_64, Intel Xeon @ 2.10GHz, 4 vCPU, 15 GB RAM.
Server started by the official Docker entrypoint with POSTGRES_PASSWORD
only; no configuration changes from the image defaults. The only non-default
settings are the SET commands in session B below. No ~/.psqlrc (psql -X).

Steps (fresh database postgres):

As superuser, run setup.sql:

create role app login password 'app';
create table ts(id uuid primary key, tenant_id uuid not null, k int);
create index on ts(tenant_id);
create table pos(id uuid primary key, tenant_id uuid not null, k int, status
text);
create index on pos(tenant_id, k);
alter table ts enable row level security; alter table pos enable row level
security;
create policy m on ts using (tenant_id = (select
current_setting('app.t')::uuid));
create policy m on pos using (tenant_id = (select
current_setting('app.t')::uuid));
grant select, update on ts, pos to app;
insert into ts select gen_random_uuid(), case when g%10=0 then
'00000000-0000-0000-0000-000000000001'::uuid else gen_random_uuid() end, g
from generate_series(1,2000) g;
insert into pos select gen_random_uuid(), tenant_id, k, 'OFFEN' from ts;
analyze;

As app, start session_a.sql (psql -X -U app -h 127.0.0.1 -d postgres -f
session_a.sql):

\set VERBOSITY verbose
set app.t = '00000000-0000-0000-0000-000000000001';
begin;
update pos set status = 'OFFEN' where k in (10, 20, 30);
select pg_sleep(2);
commit;

While session A sleeps, run session_b.sql as app in a second connection:

\set VERBOSITY verbose
set app.t = '00000000-0000-0000-0000-000000000001';
set enable_indexscan = off; set enable_mergejoin = off; set enable_nestloop
= off;
with v as materialized (select * from unnest((select array_agg(id) from
(select id from ts where k in (10,20,30)) x)) as v(ts_id)),
u as materialized (select * from unnest(cast('{}' as uuid[])) as u(ts_id)),
g as (
select p.id, true as voll from v join ts s on s.id = v.ts_id join pos p on
p.k = s.k
union all
select p.id, false from u join ts s on s.id = u.ts_id join pos p on p.k =
s.k
),
ziel as (select p.id from pos p join g on g.id = p.id where p.status in
('OFFEN','NEU') order by p.k, p.id for update of p)
update pos p set status = 'NEU' from ziel where p.id = ziel.id;

Actual output (18.6), session B:

SET
SET
SET
SET
psql:/tmp/bug_session_b.sql:13: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
psql:/tmp/bug_session_b.sql:13: error: connection to server was lost

Session A completes normally (UPDATE 3, COMMIT).

Server log:

LOG: client backend (PID 121) was terminated by signal 11: Segmentation
fault
DETAIL: Failed process was running: with v as materialized (select * from
unnest((select array_agg(id) from (select id from ts where k in (10,20,30))
x)) as v(ts_id)),
u as materialized (select * from unnest(cast('{}' as uuid[])) as
u(ts_id)),
g as (
...
update pos p set status = 'NEU' from ziel where p.id = ziel.id;
LOG: terminating any other active server processes
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at ...
LOG: database system was not properly shut down; automatic recovery in
progress

Kernel: postgres[…]: segfault at 0 ip … error 4 in ld-musl-x86_64.so.1
(Alpine).

Backtrace from a core dump of 17.11 on Alpine (release build, function names
only):

#0 memcmp () ld-musl-x86_64.so.1
#1 uuid_cmp ()
#2 FunctionCall2Coll ()
#3 _bt_compare ()
#4 _bt_binsrch ()
#5 _bt_search ()
#6 _bt_first ()
#7 btgetbitmap ()
#8 index_getbitmap ()
#9 MultiExecBitmapIndexScan ()
#10 BitmapHeapNext ()
#11 ExecHashJoin ()
#12 ExecNestLoop ()
#13 ExecScan ()
#14 ExecAppend ()
#15 ExecNestLoop ()
#16 ExecSort ()
#17 EvalPlanQualNext ()
#18 ExecLockRows ()
#19 CteScanNext ()
#20 ExecScan ()
#21 ExecNestLoop ()
#22 ExecModifyTable ()
#23 standard_ExecutorRun ()

(That core came from our application's statement of the same shape, not from
the script above.)

Expected output: UPDATE 3 in session B, with status NEU for the three rows.

Further observations:

If session A updates only two of the rows (k in (10, 20)), session B
returns UPDATE 3 without crashing.
If g is declared as materialized, session B returns UPDATE 3 on 18.6 and
17.11.