ERROR: failed to find conversion function from "unknown" to text

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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Hello

2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
> singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>

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 <pavel(dot)stehule(at)gmail(dot)com>wrote:

> Hello
>
> 2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
> > singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
> wrote:
>>
>> Hello
>>
>> 2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
>> > singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
>> >
>> > EnterpriseDB http://www.enterprisedb.com
>> >
>> > Mail sent from my BlackLaptop device
>> >
>
>
>
> --
> gurjeet[(dot)singh](at)EnterpriseDB(dot)com
> singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
>
> EnterpriseDB http://www.enterprisedb.com
>
> Mail sent from my BlackLaptop device
>

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(dot)stehule(at)gmail(dot)com>wrote:

> 2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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(dot)stehule(at)gmail(dot)com>
> > wrote:
> >>
> >> Hello
> >>
> >> 2009/1/6 Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)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[(dot)singh](at)EnterpriseDB(dot)com
> >> > singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
> >> >
> >> > EnterpriseDB http://www.enterprisedb.com
> >> >
> >> > Mail sent from my BlackLaptop device
> >> >
> >
> >
> >
> > --
> > gurjeet[(dot)singh](at)EnterpriseDB(dot)com
> > singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com
> >
> > EnterpriseDB http://www.enterprisedb.com
> >
> > Mail sent from my BlackLaptop device
> >
>

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> 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.

On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

On Tue, Jan 6, 2009 at 2:24 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com> wrote:
> On Tue, Jan 6, 2009 at 2:43 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>
> wrote:
>>
>> On Tue, Jan 6, 2009 at 2:04 AM, Gurjeet Singh <singh(dot)gurjeet(at)gmail(dot)com>
>> 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?

"Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> 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

On Tue, Jan 6, 2009 at 6:31 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

"Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> 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

Greetings!

I just tried to do a search in the archives of this list for ".Net
provider". The search returned results contained "provided" and
"providing". Is there a way to make sure that my searches return only
messages containing strings that exactly match what I'm looking for?

Thank you very much.

RobR

On Tue, Jan 06, 2009 at 09:06:43AM -0500, Rob Richardson wrote:
> I just tried to do a search in the archives of this list for ".Net
> provider". The search returned results contained "provided" and
> "providing". Is there a way to make sure that my searches return only
> messages containing strings that exactly match what I'm looking for?

would google do what you want?

http://www.google.com/search?q=".Net+provider"+site:archives.postgresql.org/pgsql-general

a few useful variations:

``intext:".net provider" site:archives.postgresql.org''
``intitle:".net provider" site:archives.postgresql.org''

gleaned from:

http://www.googleguide.com/advanced_operators.html

Sam

On Tue, Jan 6, 2009 at 7:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "Gurjeet Singh" <singh(dot)gurjeet(at)gmail(dot)com> 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[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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 <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.