pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: simon.tenbusch@cloudelevator.de
Subject: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
Date: Wed, 21 Oct 2026 13:07:41 +0000
Message-ID: <19779-ecc6e58203be9d8f@postgresql.org> (raw)

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.tenbusch@cloudelevator.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.








view thread (2+ messages)  latest in thread

Message-ID: <19779-ecc6e58203be9d8f@postgresql.org>
Permalink:  ../19779-ecc6e58203be9d8f@postgresql.org/
Also on:    postgresql.org/message-id/19779-ecc6e58203be9d8f@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, simon.tenbusch@cloudelevator.de
  Subject: Re: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
  In-Reply-To: <19779-ecc6e58203be9d8f@postgresql.org>
  References: <19779-ecc6e58203be9d8f@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox