BUG #19906: Backend crash (signal 11) creating a LANGUAGE sql function that returns a composite type from a sub-
The following bug has been logged on the website:
Bug reference: 19906
Logged by: Boris
Email address: murasovb@gmail.com
PostgreSQL version: 18.7
Operating system: Debian 13 (trixie), x86_64, Docker image pgvector/
Description:
PostgreSQL version:
PostgreSQL 18.7 (Debian 18.7-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(official Debian package in the pgvector/pgvector:0.8.7-pg18-trixie Docker
image; default configuration; empty database)
Minimal reproduction:
CREATE TABLE t (id int, v text);
CREATE FUNCTION m1() RETURNS TABLE (x t) LANGUAGE sql
AS $$ SELECT s FROM (SELECT t.* FROM t) s $$;
Result: "server closed the connection unexpectedly". The server log shows:
client backend (PID ...) was terminated by signal 11: Segmentation fault
and the postmaster runs crash recovery.
The CREATE FUNCTION alone crashes (check_function_bodies = on). The table
can be empty.
Variations (one change at a time):
1. RETURNS TABLE (x t), body SELECT s FROM (SELECT t.* FROM t) s
-> crash
2. RETURNS SETOF t, same body
-> crash
3. RETURNS TABLE (x t), body SELECT t FROM t (whole row of the table
itself) -> OK
4. RETURNS TABLE (n int, x t), body SELECT 1, s FROM (SELECT t.* FROM t) s
-> crash
5. RETURNS t (single row), body of 1
-> crash
6. SET check_function_bodies = off, create function 1, then SELECT * FROM
m1() -> crash at the call
7. Same body in LANGUAGE plpgsql with RETURN QUERY
-> error "structure of query does not match function result type", no crash
8. Plain statement SELECT s::t FROM (SELECT t.* FROM t) s
-> OK
9. RETURNS TABLE (x t), body SELECT ROW(s.*) FROM (SELECT t.* FROM t) s
-> OK
10. RETURNS TABLE (id int, v text), body SELECT s.id, s.v FROM (SELECT t.*
FROM t) s -> OK
11. RETURNS SETOF t, body SELECT s FROM (SELECT id, v FROM t) s
-> crash
Summary: a LANGUAGE sql function whose result (a column, SETOF, or a single
row) is a named composite type,
and whose final SELECT returns the whole-row reference of a sub-select in
FROM (a value of type record that
must be converted to that composite type), crashes the backend. PL/pgSQL
reports an error instead.
SET clauses, SECURITY INVOKER/DEFINER, volatility and ROWS are not needed to
trigger it.
Workaround: return explicit columns, or ROW(s.*), instead of the bare
whole-row reference.
Hi,
On Mon, 16 Nov 2026 at 20:47, PG Bug reporting form
<noreply@postgresql.org> wrote:
The following bug has been logged on the website:
Bug reference: 19906
Logged by: Boris
Email address: murasovb@gmail.com
PostgreSQL version: 18.7
Operating system: Debian 13 (trixie), x86_64, Docker image pgvector/
Description:PostgreSQL version:
PostgreSQL 18.7 (Debian 18.7-1.pgdg13+2) on x86_64-pc-linux-gnu, compiled by
gcc (Debian 14.2.0-19) 14.2.0, 64-bit
(official Debian package in the pgvector/pgvector:0.8.7-pg18-trixie Docker
image; default configuration; empty database)Minimal reproduction:
CREATE TABLE t (id int, v text);
CREATE FUNCTION m1() RETURNS TABLE (x t) LANGUAGE sql
AS $$ SELECT s FROM (SELECT t.* FROM t) s $$;Result: "server closed the connection unexpectedly". The server log shows:
client backend (PID ...) was terminated by signal 11: Segmentation fault
and the postmaster runs crash recovery.
Thanks for the report and repro.
The crash can be fixed by checking for pstate not being NULL in
coerce_record_to_complex(), which makes it error out with "cannot cast
type record to t". But I'm unsure if we should do just that, or rather
teach check_sql_fn_retval() to expand the whole-row Var so that this
works like s::t?
Regards,
Ayush
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
The crash can be fixed by checking for pstate not being NULL in
coerce_record_to_complex(), which makes it error out with "cannot cast
type record to t". But I'm unsure if we should do just that, or rather
teach check_sql_fn_retval() to expand the whole-row Var so that this
works like s::t?
Yeah, I was just looking at this too. It's been a crash case since
v13, and before that you got
ERROR: return type mismatch in function declared to return t
DETAIL: Final statement returns record instead of integer at column 1.
CONTEXT: SQL function "m1"
So it's never worked, and there's been no complaints to speak of,
and it's easy to work around:
CREATE FUNCTION m1() RETURNS TABLE (x t) LANGUAGE sql
AS $$ SELECT s::t FROM (SELECT t.* FROM t) s $$;
So I think that putting in a defense against null pstate in
coerce_record_to_complex() is sufficient. Making it work would be
very very tedious (I don't think we have any prefab code that
would be suitable) and evidently there's no demand.
Actually the annoying part of this is that we ought to change the
comments elsewhere in parse_coerce.c that say that pstate is only
used to return data about Param types, because that's evidently a
lie (and has been since well before v13...).
regards, tom lane
Hi Ayush, Tom,
Thank you both for the quick analysis.
I understand the decision to add the NULL check in
coerce_record_to_complex(). This form has never worked, and the workaround
is simple.
One request from the user side: the new error "cannot cast type record to
t" does not help much here. The function body looks correct, and the same
query works as a plain SELECT, so it is not clear what to change. Is it
possible to add an errhint() that tells the user to cast the row
explicitly, for example "Cast the row value to type t explicitly"? This
hint shows users the s::t workaround at once.
If a patch is available, I can test it on 18.x with our original query.
Regards,
Boris
пн, 16 нояб. 2026 г. в 21:29, Tom Lane <tgl@sss.pgh.pa.us>:
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
The crash can be fixed by checking for pstate not being NULL in
coerce_record_to_complex(), which makes it error out with "cannot cast
type record to t". But I'm unsure if we should do just that, or rather
teach check_sql_fn_retval() to expand the whole-row Var so that this
works like s::t?Yeah, I was just looking at this too. It's been a crash case since
v13, and before that you gotERROR: return type mismatch in function declared to return t
DETAIL: Final statement returns record instead of integer at column 1.
CONTEXT: SQL function "m1"So it's never worked, and there's been no complaints to speak of,
and it's easy to work around:CREATE FUNCTION m1() RETURNS TABLE (x t) LANGUAGE sql
AS $$ SELECT s::t FROM (SELECT t.* FROM t) s $$;So I think that putting in a defense against null pstate in
coerce_record_to_complex() is sufficient. Making it work would be
very very tedious (I don't think we have any prefab code that
would be suitable) and evidently there's no demand.Actually the annoying part of this is that we ought to change the
comments elsewhere in parse_coerce.c that say that pstate is only
used to return data about Param types, because that's evidently a
lie (and has been since well before v13...).regards, tom lane
--
SDH "StudentDrawingHelper"
Aircraft engineer
Boris Murasov
Mob.: +370 63586809
E-mail: murasovb@gmail.com
E-mail2: sdh@drawinghelper.eu
WEB: www.drawinghelper.eu
<http://www.camcons.lt>
=?UTF-8?B?0JHQvtGA0LjRgSDQnNGD0YDQsNGI0L7Qsg==?= <murasovb@gmail.com> writes:
One request from the user side: the new error "cannot cast type record to
t" does not help much here. The function body looks correct, and the same
query works as a plain SELECT, so it is not clear what to change. Is it
possible to add an errhint() that tells the user to cast the row
explicitly, for example "Cast the row value to type t explicitly"? This
hint shows users the s::t workaround at once.
Okay, more or less as attached then.
regards, tom lane
Hi,
On Tue, 17 Nov 2026 at 04:16, Tom Lane <tgl@sss.pgh.pa.us> wrote:
=?UTF-8?B?0JHQvtGA0LjRgSDQnNGD0YDQsNGI0L7Qsg==?= <murasovb@gmail.com> writes:
One request from the user side: the new error "cannot cast type record to
t" does not help much here. The function body looks correct, and the same
query works as a plain SELECT, so it is not clear what to change. Is it
possible to add an errhint() that tells the user to cast the row
explicitly, for example "Cast the row value to type t explicitly"? This
hint shows users the s::t workaround at once.Okay, more or less as attached then.
Thanks, LGTM. Tested the cases from the report, both at create time
and at call time with check_function_bodies = off, all of them give
the error with the hint now.
Post your last mail, I was auditing the places that need these comment
updates, and the difference between that list and your patch is:
1) make_fn_arguments() in parse_func.c still has the old comment, and
clauses.c calls it with NULL.
2) make_row_comparison_op() in parse_expr.c has it too, but it
dereferences pstate anyway, so that line can just go?
Regards,
Ayush
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
Post your last mail, I was auditing the places that need these comment
updates, and the difference between that list and your patch is:
1) make_fn_arguments() in parse_func.c still has the old comment, and
clauses.c calls it with NULL.
2) make_row_comparison_op() in parse_expr.c has it too, but it
dereferences pstate anyway, so that line can just go?
Good catches. Incorporated in less-drafty v2.
regards, tom lane
Hi,
On Tue, 17 Nov 2026 at 22:04, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ayush Tiwari <ayushtiwari.slg01@gmail.com> writes:
Post your last mail, I was auditing the places that need these comment
updates, and the difference between that list and your patch is:
1) make_fn_arguments() in parse_func.c still has the old comment, and
clauses.c calls it with NULL.
2) make_row_comparison_op() in parse_expr.c has it too, but it
dereferences pstate anyway, so that line can just go?Good catches. Incorporated in less-drafty v2.
Thanks for updating the patch, lgtm.
Regards,
Ayush