Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT5Sx-0000000060t-2YvN for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 22:46:24 +0000 Received: from localhost ([127.0.0.1]:52278 helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.98.2) (envelope-from ) id 1xT5Sw-00000001otv-32L3 for pgsql-bugs@arkaria.postgresql.org; Mon, 16 Nov 2026 22:46:22 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]:58950) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT5Sw-00000001otm-1pdB for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 22:46:22 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.98.2) (envelope-from ) id 1xT5Su-0000000047Q-0Czc for pgsql-bugs@lists.postgresql.org; Mon, 16 Nov 2026 22:46:22 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.18.1/8.18.1) with ESMTP id 6AGMkH59266211; Mon, 16 Nov 2026 17:46:17 -0500 DKIM-Filter: OpenDKIM Filter v2.11.0 sss.pgh.pa.us 6AGMkH59266211 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=sss.pgh.pa.us; s=default; t=1794869177; bh=/oJa3/khfHTs6Dz/hyOE/xyDRtKGOH4BsdY9sadBYu8=; h=From:To:cc:Subject:In-reply-to:References:Date:From; b=38R/RVAUqd6ScJHV9DXwMbkDqbqjluJoXbmXY24MxrkpgSDrI3scyTXoS4QVJCQtX baLB7KYD5zA/kHv4D90RkIXP/Lbs23m04yUt30dBT+vL8jcqSrOve9EGoYpxa03td8 03tj/D1QVINlmE6dd9qxJqX6jk7cZ81OzKubCcwU= From: Tom Lane To: =?UTF-8?B?0JHQvtGA0LjRgSDQnNGD0YDQsNGI0L7Qsg==?= cc: Ayush Tiwari , 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- In-reply-to: References: <19906-229033275b593ef0@postgresql.org> <126458.1794857339@sss.pgh.pa.us> Comments: In-reply-to =?UTF-8?B?0JHQvtGA0LjRgSDQnNGD0YDQsNGI0L7Qsg==?= message dated "Mon, 16 Nov 2026 22:23:02 +0200" MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0" Content-ID: <266182.1794869165.0@sss.pgh.pa.us> Date: Mon, 16 Nov 2026 17:46:17 -0500 Message-ID: <266210.1794869177@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------- =_aaaaaaaaaa0 Content-Type: text/plain; charset="us-ascii" Content-ID: <266182.1794869165.1@sss.pgh.pa.us> =?UTF-8?B?0JHQvtGA0LjRgSDQnNGD0YDQsNGI0L7Qsg==?= 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 ------- =_aaaaaaaaaa0 Content-Type: text/x-diff; name="draft-fix-coerce_record_to_complex.patch"; charset="us-ascii" Content-ID: <266182.1794869165.2@sss.pgh.pa.us> Content-Description: draft-fix-coerce_record_to_complex.patch Content-Transfer-Encoding: quoted-printable diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_= coerce.c index 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 o= f - * a previously UNKNOWN Param. It is okay to pass pstate =3D NULL if the - * caller does not want type information updated for Params. + * pstate can be NULL in calls from outside the parser. This mostly work= s, + * 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 it= ems + * 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 ad= d * 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-r= ow - * Vars. + * Vars. pstate =3D=3D 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 =3D=3D InvalidAttrNumber) { - Var *var =3D (Var *) node; - ParseNamespaceItem *nsitem; + if (pstate) + { + Var *var =3D (Var *) node; + ParseNamespaceItem *nsitem; = - nsitem =3D GetNSItemByVar(pstate, var); - args =3D expandNSItemVars(pstate, nsitem, var->varlevelsup, - var->location, NULL); + nsitem =3D GetNSItemByVar(pstate, var); + args =3D 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 *n= ode, * * 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 par= ser. */ 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 par= ser. */ Node * coerce_to_specific_type_typmod(ParseState *pstate, Node *node, @@ -1252,8 +1265,7 @@ coerce_to_specific_type_typmod(ParseState *pstate, N= ode *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 par= ser. */ 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 *t= ypeids, 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 par= ser. */ Node * coerce_to_common_type(ParseState *pstate, Node *node, diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/e= xpected/rangefuncs.out index 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 rn= gfunc2 WHERE rngfuncid =3D $1 ORDER BY f2;' LANGUAGE SQL; +CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as + 'SELECT * FROM rngfunc2 WHERE rngfuncid =3D $1 ORDER BY f2;' LANGUAGE S= QL; +-- 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/ra= ngefuncs.sql index 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 rn= gfunc2 WHERE rngfuncid =3D $1 ORDER BY f2;' LANGUAGE SQL; +CREATE FUNCTION rngfunct(int) returns setof rngfunc2 as + 'SELECT * FROM rngfunc2 WHERE rngfuncid =3D $1 ORDER BY f2;' LANGUAGE S= QL; + +-- 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); ------- =_aaaaaaaaaa0--