pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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) download 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 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.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 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.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 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);

view thread (9+ messages)  latest in thread

Message-ID: <266210.1794869177@sss.pgh.pa.us>
Permalink:  ../266210.1794869177@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/266210.1794869177@sss.pgh.pa.us

 ·  · 

reply

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