public inbox for [email protected]
help / color / mirror / Atom feedERROR: failed to find conversion function from "unknown" to text
17+ messages / 6 participants
[nested] [flat]
* ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 06:23 Gurjeet Singh <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 06:23 UTC (permalink / raw)
To: PGSQL General <[email protected]>; pgsql-hackers
Q1: select '' union all select ''
Q2: select '' union all select * from (select '' ) as s
version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
Hi All,
Q1 works just fine, but Q2 fails with:
ERROR: failed to find conversion function from "unknown" to text
Q2 is a generalization of a huge query we are facing, which we cannot
modify. I don't think this is a 'removed-casts' problem generally faced in
8.3, but I may be wrong. Will adding some cast resolve this?
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 06:30 Pavel Stehule <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2009-01-06 06:30 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: PGSQL General <[email protected]>; pgsql-hackers
Hello
2009/1/6 Gurjeet Singh <[email protected]>:
> Q1: select '' union all select ''
> Q2: select '' union all select * from (select '' ) as s
>
> version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>
> Hi All,
>
> Q1 works just fine, but Q2 fails with:
>
> ERROR: failed to find conversion function from "unknown" to text
>
> Q2 is a generalization of a huge query we are facing, which we cannot
> modify. I don't think this is a 'removed-casts' problem generally faced in
> 8.3, but I may be wrong. Will adding some cast resolve this?
yes
postgres=# select '' union all select * from (select ''::text ) as s;
?column?
----------
(2 rows)
regards
Pavel Stehule
>
> Best regards,
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 06:37 Gurjeet Singh <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 06:37 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: PGSQL General <[email protected]>; pgsql-hackers
As I mentioned, we cannot change the query, so adding casts to the query is
not an option. I was looking for something external to the query, like a
CREATE CAST command that'd resolve the issue.
Best regards,
On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <[email protected]>wrote:
> Hello
>
> 2009/1/6 Gurjeet Singh <[email protected]>:
> > Q1: select '' union all select ''
> > Q2: select '' union all select * from (select '' ) as s
> >
> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
> >
> > Hi All,
> >
> > Q1 works just fine, but Q2 fails with:
> >
> > ERROR: failed to find conversion function from "unknown" to text
> >
> > Q2 is a generalization of a huge query we are facing, which we cannot
> > modify. I don't think this is a 'removed-casts' problem generally faced
> in
> > 8.3, but I may be wrong. Will adding some cast resolve this?
>
> yes
>
> postgres=# select '' union all select * from (select ''::text ) as s;
> ?column?
> ----------
>
>
> (2 rows)
>
> regards
> Pavel Stehule
>
> >
> > Best regards,
> > --
> > gurjeet[.singh]@EnterpriseDB.com
> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 06:45 Pavel Stehule <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Pavel Stehule @ 2009-01-06 06:45 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: PGSQL General <[email protected]>; pgsql-hackers
2009/1/6 Gurjeet Singh <[email protected]>:
> As I mentioned, we cannot change the query, so adding casts to the query is
> not an option. I was looking for something external to the query, like a
> CREATE CAST command that'd resolve the issue.
I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
(but I am have old 8.3)
postgres=# create function unknown2text(unknown) returns text as
$$select $1::text$$ language sql;
CREATE FUNCTION
postgres=# create cast(unknown as text) with function
unknown2text(unknown) as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth", after
ensuring the platform's stack depth limit is adequate.
CONTEXT: SQL function "unknown2text" during startup
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
SQL function "unknown2text" statement 1
It working on 8.4
postgres=# create cast (unknown as text) with inout as implicit;
CREATE CAST
postgres=# select '' union all select * from (select '' ) as s;
?column?
----------
(2 rows)
regards
Pavel Stehule
>
> Best regards,
>
> On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <[email protected]>
> wrote:
>>
>> Hello
>>
>> 2009/1/6 Gurjeet Singh <[email protected]>:
>> > Q1: select '' union all select ''
>> > Q2: select '' union all select * from (select '' ) as s
>> >
>> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
>> >
>> > Hi All,
>> >
>> > Q1 works just fine, but Q2 fails with:
>> >
>> > ERROR: failed to find conversion function from "unknown" to text
>> >
>> > Q2 is a generalization of a huge query we are facing, which we
>> > cannot
>> > modify. I don't think this is a 'removed-casts' problem generally faced
>> > in
>> > 8.3, but I may be wrong. Will adding some cast resolve this?
>>
>> yes
>>
>> postgres=# select '' union all select * from (select ''::text ) as s;
>> ?column?
>> ----------
>>
>>
>> (2 rows)
>>
>> regards
>> Pavel Stehule
>>
>> >
>> > Best regards,
>> > --
>> > gurjeet[.singh]@EnterpriseDB.com
>> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>> >
>> > EnterpriseDB http://www.enterprisedb.com
>> >
>> > Mail sent from my BlackLaptop device
>> >
>
>
>
> --
> gurjeet[.singh]@EnterpriseDB.com
> singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 09:04 Gurjeet Singh <[email protected]>
parent: Pavel Stehule <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 09:04 UTC (permalink / raw)
To: Pavel Stehule <[email protected]>; +Cc: PGSQL General <[email protected]>; pgsql-hackers
I took your cue, and have formulated this solution for 8.3.1 :
create or replace function unknown2text(unknown) returns text as
$$ begin return text($1::char); end $$ language plpgsql;
drop cast (unknown as text);
create cast (unknown as text) with function unknown2text( unknown ) as
implicit;
select '' union all select * from (select '' ) as s;
Thanks for your help Pavel.
Best regards,
PS: I was getting the same error as yours (stack depth) in EDB version
8.3.0.12, so I had to use the following code for unknown2text:
return charin( unknownout($1) );
It works for PG 8.3.1 too.
On Tue, Jan 6, 2009 at 12:15 PM, Pavel Stehule <[email protected]>wrote:
> 2009/1/6 Gurjeet Singh <[email protected]>:
> > As I mentioned, we cannot change the query, so adding casts to the query
> is
> > not an option. I was looking for something external to the query, like a
> > CREATE CAST command that'd resolve the issue.
>
> I am sorry, I blind - I tested casting on 8.3.0 and it doesn't work
> (but I am have old 8.3)
> postgres=# create function unknown2text(unknown) returns text as
> $$select $1::text$$ language sql;
> CREATE FUNCTION
> postgres=# create cast(unknown as text) with function
> unknown2text(unknown) as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ERROR: stack depth limit exceeded
> HINT: Increase the configuration parameter "max_stack_depth", after
> ensuring the platform's stack depth limit is adequate.
> CONTEXT: SQL function "unknown2text" during startup
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
> SQL function "unknown2text" statement 1
>
> It working on 8.4
>
> postgres=# create cast (unknown as text) with inout as implicit;
> CREATE CAST
> postgres=# select '' union all select * from (select '' ) as s;
> ?column?
> ----------
>
>
> (2 rows)
>
> regards
> Pavel Stehule
>
>
> >
> > Best regards,
>
>
> >
> > On Tue, Jan 6, 2009 at 12:00 PM, Pavel Stehule <[email protected]>
> > wrote:
> >>
> >> Hello
> >>
> >> 2009/1/6 Gurjeet Singh <[email protected]>:
> >> > Q1: select '' union all select ''
> >> > Q2: select '' union all select * from (select '' ) as s
> >> >
> >> > version: PostgreSQL 8.3.1, compiled by Visual C++ build 1400
> >> >
> >> > Hi All,
> >> >
> >> > Q1 works just fine, but Q2 fails with:
> >> >
> >> > ERROR: failed to find conversion function from "unknown" to text
> >> >
> >> > Q2 is a generalization of a huge query we are facing, which we
> >> > cannot
> >> > modify. I don't think this is a 'removed-casts' problem generally
> faced
> >> > in
> >> > 8.3, but I may be wrong. Will adding some cast resolve this?
> >>
> >> yes
> >>
> >> postgres=# select '' union all select * from (select ''::text ) as s;
> >> ?column?
> >> ----------
> >>
> >>
> >> (2 rows)
> >>
> >> regards
> >> Pavel Stehule
> >>
> >> >
> >> > Best regards,
> >> > --
> >> > gurjeet[.singh]@EnterpriseDB.com
> >> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
> >> >
> >> > EnterpriseDB http://www.enterprisedb.com
> >> >
> >> > Mail sent from my BlackLaptop device
> >> >
> >
> >
> >
> > --
> > gurjeet[.singh]@EnterpriseDB.com
> > singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 09:13 Scott Marlowe <[email protected]>
parent: Gurjeet Singh <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Scott Marlowe @ 2009-01-06 09:13 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <[email protected]> wrote:
> I took your cue, and have formulated this solution for 8.3.1 :
Is there a good reason you're running against a db version with known
bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a
version missing over a year of updates is not a best practice.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 09:24 Gurjeet Singh <[email protected]>
parent: Scott Marlowe <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 09:24 UTC (permalink / raw)
To: Scott Marlowe <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe <[email protected]>wrote:
> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <[email protected]>
> wrote:
> > I took your cue, and have formulated this solution for 8.3.1 :
>
> Is there a good reason you're running against a db version with known
> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a
> version missing over a year of updates is not a best practice.
>
That's just a development instance that I have kept for long; actual issue
was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
8.3.0.12, so had to come up with a different code for that!
Best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 09:29 Scott Marlowe <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Scott Marlowe @ 2009-01-06 09:29 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh <[email protected]> wrote:
> On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe <[email protected]>
> wrote:
>>
>> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <[email protected]>
>> wrote:
>> > I took your cue, and have formulated this solution for 8.3.1 :
>>
>> Is there a good reason you're running against a db version with known
>> bugs instead of 8.3.5? Seriously, it's an easy upgrade and running a
>> version missing over a year of updates is not a best practice.
>
> That's just a development instance that I have kept for long; actual issue
> was on EDB 8.3.0.12, which the customer is using. As noted in the PS of
> previous mail, the solution that worked for PG 8.3.1 didn't work on EDB
> 8.3.0.12, so had to come up with a different code for that!
Ahh, ok. I was just worried you were ignoring updates. I don't know
anything about the numbering scheme for EDB. What does 8.3.0.12
translate to in regular pgsql versions?
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 13:01 Tom Lane <[email protected]>
parent: Gurjeet Singh <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Tom Lane @ 2009-01-06 13:01 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
"Gurjeet Singh" <[email protected]> writes:
> create cast (unknown as text) with function unknown2text( unknown ) as
> implicit;
This is a horrendously bad idea; it will bite your *ss sooner or later,
probably sooner.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 13:38 Gurjeet Singh <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 13:38 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane <[email protected]> wrote:
> "Gurjeet Singh" <[email protected]> writes:
> > create cast (unknown as text) with function unknown2text( unknown ) as
> > implicit;
>
> This is a horrendously bad idea; it will bite your *ss sooner or later,
> probably sooner.
>
> regards, tom lane
>
I guessed so, but couldn't figure out exactly how! That's why I have
suggested this as a temp solution until we confirmed this with someone more
knowledgeable.
Can you please let us know how this would be problematic? And can you
suggest a better solution?
Thanks and best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 13:48 Tom Lane <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2009-01-06 13:48 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
"Gurjeet Singh" <[email protected]> writes:
>> This is a horrendously bad idea; it will bite your *ss sooner or later,
>> probably sooner.
> Can you please let us know how this would be problematic?
The point is that it's going to have unknown, untested effects on the
default coercion rules, possibly leading to silent changes in the
behavior of queries that used to work. If you'd rather retest every one
of your other queries than fix this one, then go ahead.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 17:43 Gurjeet Singh <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Gurjeet Singh @ 2009-01-06 17:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane <[email protected]> wrote:
> "Gurjeet Singh" <[email protected]> writes:
> >> This is a horrendously bad idea; it will bite your *ss sooner or later,
> >> probably sooner.
>
> > Can you please let us know how this would be problematic?
>
> The point is that it's going to have unknown, untested effects on the
> default coercion rules, possibly leading to silent changes in the
> behavior of queries that used to work. If you'd rather retest every one
> of your other queries than fix this one, then go ahead.
>
>
Changing the query is an option not given to us. It is being migrated from a
BigDB.
I was working on these solutions assuming that these are workarounds to a
bug. But from your mails, it seems that it is an expected behaviour; is it?
If we consider the second branch of UNION ALL of both the queries above, if
"select '' " yields a text column, then so should a "select * from (select
'')".
Its not exactly a bug, but sure is a problem that we should try to resolve.
Thanks and best regards,
--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
Mail sent from my BlackLaptop device
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text
@ 2009-01-06 22:13 Martijn van Oosterhout <[email protected]>
parent: Gurjeet Singh <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Martijn van Oosterhout @ 2009-01-06 22:13 UTC (permalink / raw)
To: Gurjeet Singh <[email protected]>; +Cc: Tom Lane <[email protected]>; Pavel Stehule <[email protected]>; PGSQL General <[email protected]>; pgsql-hackers
On Tue, Jan 06, 2009 at 11:13:59PM +0530, Gurjeet Singh wrote:
> If we consider the second branch of UNION ALL of both the queries above, if
> "select '' " yields a text column, then so should a "select * from (select
> '')".
The problem is ofcourse that "select ''" doesn't produce a text column
in postgres. This generally works fine, except in the case of UNION
where none of the branches provide the necessary type info.
Have a nice day,
--
Martijn van Oosterhout <[email protected]> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.
Attachments:
[application/pgp-signature] signature.asc (189B, 2-signature.asc)
download
^ permalink raw reply [nested|flat] 17+ messages in thread
* ERROR: failed to find conversion function from unknown to text
@ 2026-01-29 01:18 jian he <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: jian he @ 2026-01-29 01:18 UTC (permalink / raw)
To: pgsql-hackers
Hi.
select cast(NULL::text as unknown);
ERROR: failed to find conversion function from unknown to text
I found similar issues in [1] and [2], and both have already been resolved.
Looking at resolveTargetListUnknowns -> coerce_type, it seems it can cope with
transforming a source expression from an Unknown Const to a Text Const. However,
it cannot coerce other Unknown type expressions, such as COERCEVIAIO, to a Text
Const.
It can fail for real table data, not just constant literals, specifically when
you try to cast a text column to an Unknown data type.
While people generally don't do this, it is still possible.
create table t(a text);
select cast(a as unknown) from t;
we don't need to worry about the domain over UNKNOWNOID, since it's not allowed.
seems like coerce_type don't have logic handle targettype as UNKNOWNOID.
in function coerce_type, right above find_coercion_pathway, we can add
if (targetTypeId == UNKNOWNOID)
{
Oid inputBaseTypeId = getBaseType(inputTypeId);
TYPCATEGORY s_typcategory = TypeCategory(inputBaseTypeId);
if (s_typcategory == TYPCATEGORY_STRING)
return node;
}
to solve this issue.
[1]: https://www.postgresql.org/message-id/flat/41E555DA.1060707%40gmail.com
[2]: https://postgr.es/m/[email protected]
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch (1.1K, 2-v1-0001-ERROR-failed-to-find-conversion-function-from-unknown-to-text.patch)
download | inline diff:
From a497d39fdd4aca2db190b21153e19093adb3dec7 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Thu, 29 Jan 2026 09:10:02 +0800
Subject: [PATCH v1 1/1] ERROR: failed to find conversion function from
unknown to text
demo:
create table t(a text);
select cast(a as unknown) from t;
discussion: https://postgr.es/m/
---
src/backend/parser/parse_coerce.c | 10 ++++++++++
1 file changed, 10 insertions(+)
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 913ca53666f..68ac99e471c 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -411,6 +411,16 @@ coerce_type(ParseState *pstate, Node *node,
}
return result;
}
+
+ if (targetTypeId == UNKNOWNOID)
+ {
+ Oid inputBaseTypeId = getBaseType(inputTypeId);
+ TYPCATEGORY s_typcategory = TypeCategory(inputBaseTypeId);
+
+ if (s_typcategory == TYPCATEGORY_STRING)
+ return node;
+ }
+
pathtype = find_coercion_pathway(targetTypeId, inputTypeId, ccontext,
&funcId);
if (pathtype != COERCION_PATH_NONE)
--
2.34.1
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
@ 2026-01-29 01:24 Tom Lane <[email protected]>
parent: jian he <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2026-01-29 01:24 UTC (permalink / raw)
To: jian he <[email protected]>; +Cc: pgsql-hackers
jian he <[email protected]> writes:
> select cast(NULL::text as unknown);
> ERROR: failed to find conversion function from unknown to text
Is there a good reason why that shouldn't be an error?
I certainly don't like the cast pathway you suggest adding
to make it not be one --- that seems likely to cause lots of
not-very-desirable behaviors.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
@ 2026-01-29 03:53 jian he <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: jian he @ 2026-01-29 03:53 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Jan 29, 2026 at 9:24 AM Tom Lane <[email protected]> wrote:
>
> Is there a good reason why that shouldn't be an error?
at that time, i want
select cast('a'::text as unknown);
behave the same as
select cast('a' as unknown);
To make it an error, meaning it's not possible to coerce to the unknown type.
We can do it in the function find_coercion_pathway, just
after the ``if (sourceTypeId == targetTypeId)`` check:
if (targetTypeId == UNKNOWNOID)
return COERCION_PATH_NONE;
it's also doable in the function can_coerce_type,
right after the ``if (inputTypeId == UNKNOWNOID)``:
if (targetTypeId == UNKNOWNOID)
return false;
--
jian
https://www.enterprisedb.com/
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: ERROR: failed to find conversion function from unknown to text
@ 2026-04-22 06:33 jian he <[email protected]>
parent: jian he <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: jian he @ 2026-04-22 06:33 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-hackers
On Thu, Jan 29, 2026 at 11:53 AM jian he <[email protected]> wrote:
>
> To make it an error, meaning it's not possible to coerce to the unknown type.
> We can do it in the function find_coercion_pathway, just
> after the ``if (sourceTypeId == targetTypeId)`` check:
>
> if (targetTypeId == UNKNOWNOID)
> return COERCION_PATH_NONE;
>
> it's also doable in the function can_coerce_type,
> right after the ``if (inputTypeId == UNKNOWNOID)``:
>
> if (targetTypeId == UNKNOWNOID)
> return false;
I choose to disallow UNKNOWN target types in find_coercion_pathway.
do $$ declare a int;
begin a := '1'::text::unknown;
end$$;
This DO statement does not cause any error in the HEAD, because of
function find_coercion_pathway:
```
/*
* When parsing PL/pgSQL assignments, allow an I/O cast to be used
* whenever no normal coercion is available.
*/
if (result == COERCION_PATH_NONE &&
ccontext == COERCION_PLPGSQL)
result = COERCION_PATH_COERCEVIAIO;
```
but will result error with the attached V2:
ERROR: cannot cast type text to unknown
--
jian
https://www.enterprisedb.com/
Attachments:
[text/x-patch] v2-0001-Disallow-UNKNOWN-target-types.patch (2.2K, 2-v2-0001-Disallow-UNKNOWN-target-types.patch)
download | inline diff:
From 9f3d8471db578d63729064d117ca2fed1abec112 Mon Sep 17 00:00:00 2001
From: jian he <[email protected]>
Date: Wed, 22 Apr 2026 14:12:25 +0800
Subject: [PATCH v2 1/1] Disallow UNKNOWN target types
discussion: https://postgr.es/m/CACJufxHu0sXO8791FDcNXp2bFnE89jyuGkJbLCQkhgWq6XuNLg@mail.gmail.com
commitfest entry: https://commitfest.postgresql.org/patch/
---
src/backend/parser/parse_coerce.c | 4 ++++
src/test/regress/expected/misc.out | 12 ++++++++++++
src/test/regress/sql/misc.sql | 6 ++++++
3 files changed, 22 insertions(+)
diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c
index 913ca53666f..aec75612ca1 100644
--- a/src/backend/parser/parse_coerce.c
+++ b/src/backend/parser/parse_coerce.c
@@ -3166,6 +3166,10 @@ find_coercion_pathway(Oid targetTypeId, Oid sourceTypeId,
if (OidIsValid(targetTypeId))
targetTypeId = getBaseType(targetTypeId);
+ /* Disallow UNKNOWN target types. */
+ if (targetTypeId == UNKNOWNOID)
+ return COERCION_PATH_NONE;
+
/* Domains are always coercible to and from their base type */
if (sourceTypeId == targetTypeId)
return COERCION_PATH_RELABELTYPE;
diff --git a/src/test/regress/expected/misc.out b/src/test/regress/expected/misc.out
index 6e816c57f1f..1c6ae0a3bbc 100644
--- a/src/test/regress/expected/misc.out
+++ b/src/test/regress/expected/misc.out
@@ -396,3 +396,15 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+do $$ declare a text;
+begin a := 's'::text::unknown;
+end$$;
+ERROR: cannot cast type text to unknown
+LINE 1: a := 's'::text::unknown
+ ^
+QUERY: a := 's'::text::unknown
+CONTEXT: PL/pgSQL function inline_code_block line 2 at assignment
+select 's'::text::unknown;
+ERROR: cannot cast type text to unknown
+LINE 1: select 's'::text::unknown;
+ ^
diff --git a/src/test/regress/sql/misc.sql b/src/test/regress/sql/misc.sql
index 165a2e175fb..c568b4239f8 100644
--- a/src/test/regress/sql/misc.sql
+++ b/src/test/regress/sql/misc.sql
@@ -273,3 +273,9 @@ SELECT *, (equipment(CAST((h.*) AS hobbies_r))).name FROM hobbies_r h;
--
-- rewrite rules
--
+
+do $$ declare a text;
+begin a := 's'::text::unknown;
+end$$;
+
+select 's'::text::unknown;
--
2.34.1
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2026-04-22 06:33 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-01-06 06:23 ERROR: failed to find conversion function from "unknown" to text Gurjeet Singh <[email protected]>
2009-01-06 06:30 ` Pavel Stehule <[email protected]>
2009-01-06 06:37 ` Gurjeet Singh <[email protected]>
2009-01-06 06:45 ` Pavel Stehule <[email protected]>
2009-01-06 09:04 ` Gurjeet Singh <[email protected]>
2009-01-06 09:13 ` Scott Marlowe <[email protected]>
2009-01-06 09:24 ` Gurjeet Singh <[email protected]>
2009-01-06 09:29 ` Scott Marlowe <[email protected]>
2009-01-06 13:01 ` Tom Lane <[email protected]>
2009-01-06 13:38 ` Gurjeet Singh <[email protected]>
2009-01-06 13:48 ` Tom Lane <[email protected]>
2009-01-06 17:43 ` Gurjeet Singh <[email protected]>
2009-01-06 22:13 ` Martijn van Oosterhout <[email protected]>
2026-01-29 01:18 ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
2026-01-29 01:24 ` Re: ERROR: failed to find conversion function from unknown to text Tom Lane <[email protected]>
2026-01-29 03:53 ` Re: ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
2026-04-22 06:33 ` Re: ERROR: failed to find conversion function from unknown to text jian he <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox