Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 0233B63228C; Tue, 6 Jan 2009 05:04:38 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 06340-10; Tue, 6 Jan 2009 05:04:30 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from rv-out-0506.google.com (rv-out-0506.google.com [209.85.198.226]) by mail.postgresql.org (Postfix) with ESMTP id 2E8D863228B; Tue, 6 Jan 2009 05:04:27 -0400 (AST) Received: by rv-out-0506.google.com with SMTP id b25so9057841rvf.43 for ; Tue, 06 Jan 2009 01:04:25 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=n9JAqeeABdK5XbCgldYHgIqRRHaELKMzjlK1RR78WqY=; b=T/eqKzUlFW25kWucfx5oAnl6veKgOhgn37bajRVsn5NZN0P57RJXmrW/GNdcHD3WZt ZZMyAY3kBSubZmyZLwc/CdliskJk5AXlfvAsFG12rVOVlvkcLBxSe8IttrC1W1hVrKrB ut8Punh+AuEIDYsW4Y4+Uv5E0ga570dRm1Xpw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=XwzrOJnMC4l5T/uLEUhnio2pRImR95ugwwAyDvSufDxDsHWJObmz1WLqVe9pUlC588 YFp7Fmc3hrS74tf/xg4K4DLohIqxzu/X4LYNMhRrv/EN0tgwGOpe6YsEidzcDcxUpjLC q5+Tr6AWbDI4P+B1lav9jP7/LVQ20IKZVkolI= Received: by 10.140.164.6 with SMTP id m6mr10849842rve.144.1231232665572; Tue, 06 Jan 2009 01:04:25 -0800 (PST) Received: by 10.141.98.4 with HTTP; Tue, 6 Jan 2009 01:04:25 -0800 (PST) Message-ID: <65937bea0901060104n399cdec8ye5e2b8e247e5139a@mail.gmail.com> Date: Tue, 6 Jan 2009 14:34:25 +0530 From: "Gurjeet Singh" To: "Pavel Stehule" Subject: Re: [HACKERS] ERROR: failed to find conversion function from "unknown" to text Cc: "PGSQL General" , "PGSQL Hackers" In-Reply-To: <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_50748_31507576.1231232665556" References: <65937bea0901052223w162a977dyeaaf888a854f7324@mail.gmail.com> <162867790901052230l25b10f08qa35f56856b9cc4c6@mail.gmail.com> <65937bea0901052237j50657573i8c980f9f5f4814d5@mail.gmail.com> <162867790901052245l4e4fa9b2ydc03781b45b13276@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200901/71 X-Sequence-Number: 142156 ------=_Part_50748_31507576.1231232665556 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 wrote: > 2009/1/6 Gurjeet Singh : > > 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 > > wrote: > >> > >> Hello > >> > >> 2009/1/6 Gurjeet Singh : > >> > 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 ------=_Part_50748_31507576.1231232665556 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 <pavel.stehule@gmail.com> wrote:
2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
> 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 <pavel.stehule@gmail.com>
> wrote:
>>
>> Hello
>>
>> 2009/1/6 Gurjeet Singh <singh.gurjeet@gmail.com>:
>> > 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
------=_Part_50748_31507576.1231232665556--