pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
2+ messages / 2 participants
[nested] [flat]
* BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
@ 2026-10-21 13:07 PG Bug reporting form <noreply@postgresql.org>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-10-21 13:07 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: simon.tenbusch@cloudelevator.de
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery
@ 2026-10-26 10:38 Dilip Kumar <dilipbalaut@gmail.com>
parent: PG Bug reporting form <noreply@postgresql.org>
0 siblings, 0 replies; 2+ messages in thread
From: Dilip Kumar @ 2026-10-26 10:38 UTC (permalink / raw)
To: simon.tenbusch@cloudelevator.de; pgsql-bugs@lists.postgresql.org
On Fri, Oct 23, 2026 at 1:37 PM PG Bug reporting form
<noreply@postgresql.org> wrote:
>
> 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.
I was able to reproduce this issue and here is a bit simplified
vcersion of the test to reproduce the same[2], while debugging this,
what I have noticed is that in EvalPlanQualBegin(), we unconditioanlly
copy the param value from paranetestate to recheckestate, so if this
is a initplan and parent estate haven't yet executed it once you are
going to overwrite the recheckstate's data with uninitialized
value[1]. One simple fix could be to only copy the parentestate
params to recheckestate if
'parentestate->es_param_exec_vals[i].execPlan' is NULL. This means if
it is an initPlan, it is executed and execPlan is set to NULL[3]. This
is fixing this particular problem for me but I am doing more analysis
on this and will update on the thread soon.
[1]
EvalPlanQualBegin()
{
....
while (--i >= 0)
{
/* copy value if any, but not execPlan link */
recheckestate->es_param_exec_vals[i].value =
parentestate->es_param_exec_vals[i].value;
recheckestate->es_param_exec_vals[i].isnull =
parentestate->es_param_exec_vals[i].isnull;
}
}
[2]
Setup:
DROP TABLE IF EXISTS t;
CREATE TABLE t (id int PRIMARY KEY, val text);
INSERT INTO t VALUES (1, 'a'), (2, 'a');
ANALYZE t;
Session1:
BEGIN;
UPDATE t SET id = id + 10;
-- Leave open until Session2 blocks, then:
-- COMMIT;
Session2:
SELECT * FROM t
JOIN (
SELECT id FROM t WHERE id > 0
UNION ALL
SELECT id FROM t WHERE val = (SELECT 'a'::text OFFSET 0)
) u ON t.id = u.id
FOR UPDATE OF t;
[3]
inline patch 1: src/backend/executor/execMain.c (+0/-3 lines) download inline-1.patch
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index d7a8f36ca1d..d5adf844e99 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -3030,11 +3030,20 @@ EvalPlanQualBegin(EPQState *epqstate)
while (--i >= 0)
{
- /* copy value if any, but not execPlan link */
- recheckestate->es_param_exec_vals[i].value =
-
parentestate->es_param_exec_vals[i].value;
- recheckestate->es_param_exec_vals[i].isnull =
-
parentestate->es_param_exec_vals[i].isnull;
+ /*
+ * Copy value if any, unless parent's
execPlan link is still
+ * set (meaning parent hasn't
evaluated this InitPlan yet, so
+ * its value/isnull are not valid and
copying them would
+ * clobber a value already evaluated
in recheckestate).
+ */
+ if
(parentestate->es_param_exec_vals[i].execPlan == NULL)
+ {
+
recheckestate->es_param_exec_vals[i].execPlan = NULL;
+
recheckestate->es_param_exec_vals[i].value =
+
parentestate->es_param_exec_vals[i].value;
+
recheckestate->es_param_exec_vals[i].isnull =
+
parentestate->es_param_exec_vals[i].isnull;
+ }
}
}
--
Regards,
Dilip Kumar
Google
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-10-26 10:38 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-21 13:07 BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery PG Bug reporting form <noreply@postgresql.org>
2026-10-26 10:38 ` Dilip Kumar <dilipbalaut@gmail.com>
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