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

Started by PG Bug reporting formabout 1 month ago3 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

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.

#2Dilip Kumar
dilipbalaut@gmail.com
In reply to: PG Bug reporting form (#1)
Re: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery

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]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;, 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]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; } }. 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]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). 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; } }
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;
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]
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

#3Andrey Borodin
amborodin@acm.org
In reply to: Dilip Kumar (#2)
Re: BUG #19779: Backend SIGSEGV in UPDATE with FOR UPDATE CTE joined to UNION ALL subquery

Hi Dilip,

I found a wrong-result case that remains with the guard. Using the same
two-row table as your example, have session 1 update both ids and keep
the transaction open:

BEGIN;
UPDATE t SET id = -id;

Then run this in session 2, and commit session 1 after it blocks:

SELECT o.i, q.* FROM (VALUES (1), (2)) o(i)
CROSS JOIN LATERAL (
SELECT t.id, t.val, u.id AS uid FROM t
JOIN (
SELECT id FROM t WHERE id > 0
UNION ALL
SELECT -id FROM t WHERE (SELECT o.i OFFSET 0) = o.i
) u ON t.id = u.id
FOR UPDATE OF t
) q;

With the guard this returns three rows, missing (2, -2, 'a', -2).
Replacing (SELECT o.i OFFSET 0) with o.i returns all four rows.

The parent's InitPlan slot already has execPlan == NULL, but its value
is still 1 from the previous LATERAL iteration. EPQ has computed 2 for
the current iteration. The next reset copies the parent's 1 over it.
So checking whether the parent has evaluated the parameter is not
enough: this output belongs to the recheck plan's own execution.

I tried copying only the recheck plan's extParam inputs in both
EvalPlanQualStart() and EvalPlanQualBegin(). ExecSetParamPlanMulti()
already evaluates external InitPlan outputs before the copy. Locally
produced values stay in the child.

With this change, both your original example and this one match their
reference queries. Do you see any inputs outside extParam that EPQ
needs to copy?

Thank you!

Best regards, Andrey Borodin.