From: Tom Lane <tgl@sss.pgh.pa.us>
To: Борис Мурашов <murasovb@gmail.com>
Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #19906: Backend crash (signal 11) creating a LANGUAGE sql function that returns a composite type from a sub-
Date: Mon, 16 Nov 2026 17:46:17 -0500
Message-ID: <266210.1794869177@sss.pgh.pa.us> (raw)
In-Reply-To: <CAF_qW9dJpZV0Cv_F3rUh=77dAtQGbMjjEf9v7ng4mz3Ai=cngQ@mail.gmail.com>
References: <19906-229033275b593ef0@postgresql.org>
<CAJTYsWUm1j=YQsHpt12=E_Xmx7GhtmVtRAZ2x+ySOo3bkJtWyQ@mail.gmail.com>
<126458.1794857339@sss.pgh.pa.us>
<CAF_qW9dJpZV0Cv_F3rUh=77dAtQGbMjjEf9v7ng4mz3Ai=cngQ@mail.gmail.com>
=?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
Attachments:draft-fix-coerce_record_to_complex.patch (text/x-diff, 6.3K) downloaddiff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.cindex d3240f4b265..bd4e12c9989 100644--- a/src/backend/parser/parse_coerce.c+++ b/src/backend/parser/parse_coerce.c@@ -147,9 +147,14 @@ coerce_to_target_type(ParseState *pstate, Node *expr, Oid exprtype,
* applies length conversion, and in those cases only, the result will
* already be properly coerced to the specified typmod.
*
- * pstate is only used in the case that we are able to resolve the type of- * a previously UNKNOWN Param. It is okay to pass pstate = NULL if the- * caller does not want type information updated for Params.+ * pstate can be NULL in calls from outside the parser. This mostly works,+ * but note the following restrictions:+ * 1. No p_coerce_param_hook processing is possible, so Params receive+ * no special treatment.+ * 2. Whole-row Vars of type RECORD (those referring to non-table FROM items+ * such as joins or sub-selects) cannot be cast to named composite types,+ * since we can't resolve their columns.+ * 3. No error position information will be provided.
*
* Note: this function must not modify the given expression tree, only add
* decoration on top of it. See transformSetOperationTree, for example.
@@ -1009,7 +1014,7 @@ build_coercion_expression(Node *node,
* Coerce a RECORD to a specific composite type.
*
* Currently we only support this for inputs that are RowExprs or whole-row
- * Vars.+ * Vars. pstate == NULL prevents handling the latter case.
*/
static Node *
coerce_record_to_complex(ParseState *pstate, Node *node,
@@ -1039,12 +1044,22 @@ coerce_record_to_complex(ParseState *pstate, Node *node,
else if (node && IsA(node, Var) &&
((Var *) node)->varattno == InvalidAttrNumber)
{
- Var *var = (Var *) node;- ParseNamespaceItem *nsitem;+ if (pstate)+ {+ Var *var = (Var *) node;+ ParseNamespaceItem *nsitem;- nsitem = GetNSItemByVar(pstate, var);- args = expandNSItemVars(pstate, nsitem, var->varlevelsup,- var->location, NULL);+ nsitem = GetNSItemByVar(pstate, var);+ args = expandNSItemVars(pstate, nsitem, var->varlevelsup,+ var->location, NULL);+ }+ else+ ereport(ERROR,+ (errcode(ERRCODE_CANNOT_COERCE),+ errmsg("cannot cast type %s to %s",+ format_type_be(RECORDOID),+ format_type_be(targetTypeId)),+ errhint("Cast the whole-row variable to the desired type explicitly.")));
}
else
ereport(ERROR,
@@ -1156,8 +1171,7 @@ coerce_record_to_complex(ParseState *pstate, Node *node,
*
* Returns the possibly-transformed node tree.
*
- * As with coerce_type, pstate may be NULL if no special unknown-Param- * processing is wanted.+ * As with coerce_type, pstate may be NULL for calls from outside the parser.
*/
Node *
coerce_to_boolean(ParseState *pstate, Node *node,
@@ -1203,8 +1217,7 @@ coerce_to_boolean(ParseState *pstate, Node *node,
*
* Returns the possibly-transformed node tree.
*
- * As with coerce_type, pstate may be NULL if no special unknown-Param- * processing is wanted.+ * As with coerce_type, pstate may be NULL for calls from outside the parser.
*/
Node *
coerce_to_specific_type_typmod(ParseState *pstate, Node *node,
@@ -1252,8 +1265,7 @@ coerce_to_specific_type_typmod(ParseState *pstate, Node *node,
*
* Returns the possibly-transformed node tree.
*
- * As with coerce_type, pstate may be NULL if no special unknown-Param- * processing is wanted.+ * As with coerce_type, pstate may be NULL for calls from outside the parser.
*/
Node *
coerce_to_specific_type(ParseState *pstate, Node *node,
@@ -1330,6 +1342,7 @@ parser_coercion_errposition(ParseState *pstate,
* This is used for determining the output type of CASE, UNION,
* and similar constructs.
*
+ * 'pstate' is used only for error reporting, and can be NULL.
* 'exprs' is a *nonempty* list of expressions. Note that earlier items
* in the list will be preferred if there is doubt.
* 'context' is a phrase to use in the error message if we fail to select
@@ -1569,8 +1582,7 @@ select_common_type_from_oids(int nargs, const Oid *typeids, bool noerror)
* expressions to the desired type. 'context' is a phrase to use in the
* error message if we fail to coerce.
*
- * As with coerce_type, pstate may be NULL if no special unknown-Param- * processing is wanted.+ * As with coerce_type, pstate may be NULL for calls from outside the parser.
*/
Node *
coerce_to_common_type(ParseState *pstate, Node *node,
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.outindex a7cb1b5611d..1f52b149907 100644--- a/src/test/regress/expected/rangefuncs.out+++ b/src/test/regress/expected/rangefuncs.out@@ -2,7 +2,14 @@ CREATE TABLE rngfunc2(rngfuncid int, f2 int);
INSERT INTO rngfunc2 VALUES(1, 11);
INSERT INTO rngfunc2 VALUES(2, 22);
INSERT INTO rngfunc2 VALUES(1, 111);
-CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;+CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as+ 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;+-- this variant doesn't work+CREATE FUNCTION rngfunct2(int) returns setof rngfunc2 as+ 'SELECT s FROM (select * from rngfunc2) s;' LANGUAGE SQL;+ERROR: cannot cast type record to rngfunc2+HINT: Cast the whole-row variable to the desired type explicitly.+CONTEXT: SQL function "rngfunct2"
-- function with ORDINALITY
select * from rngfunct(1) with ordinality as z(a,b,ord);
a | b | ord
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sqlindex 3c47c98e113..4780c78395b 100644--- a/src/test/regress/sql/rangefuncs.sql+++ b/src/test/regress/sql/rangefuncs.sql@@ -3,7 +3,12 @@ INSERT INTO rngfunc2 VALUES(1, 11);
INSERT INTO rngfunc2 VALUES(2, 22);
INSERT INTO rngfunc2 VALUES(1, 111);
-CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;+CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as+ 'SELECT * FROM rngfunc2 WHERE rngfuncid = $1 ORDER BY f2;' LANGUAGE SQL;++-- this variant doesn't work+CREATE FUNCTION rngfunct2(int) returns setof rngfunc2 as+ 'SELECT s FROM (select * from rngfunc2) s;' LANGUAGE SQL;
-- function with ORDINALITY
select * from rngfunct(1) with ordinality as z(a,b,ord);
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: tgl@sss.pgh.pa.us, murasovb@gmail.com, ayushtiwari.slg01@gmail.com
Subject: Re: BUG #19906: Backend crash (signal 11) creating a LANGUAGE sql function that returns a composite type from a sub-
In-Reply-To: <266210.1794869177@sss.pgh.pa.us>
References: <19906-229033275b593ef0@postgresql.org> <CAJTYsWUm1j=YQsHpt12=E_Xmx7GhtmVtRAZ2x+ySOo3bkJtWyQ@mail.gmail.com> <126458.1794857339@sss.pgh.pa.us> <CAF_qW9dJpZV0Cv_F3rUh=77dAtQGbMjjEf9v7ng4mz3Ai=cngQ@mail.gmail.com> <266210.1794869177@sss.pgh.pa.us>
* 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