Обсуждение: BUG #15057: Issue with UNKNOW type when calling a PostgreSQL functionfrom java code

Hi Pavel,

Thank you for quick answer,

This is a PostgreSQL bug, I tried this without orafce plugin, still the same error ,Let me know if you want me to share screenshots.

Thanks,

Praveen

On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Redhat Linux 6
Description:

Hi Guys,

We have recently migrated our oracle database to PostgreSQL database,and
migration went successfully.
We have used ORAFCE to provide backward compatibility.
But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or any
other user defined PostgreSQL functions

That is type UNKNOWN

If I try to call a function as below

select myFunction(1,'This is unknown type',90.01,'Again a unknown type
column');

It is raising an error like function myFunction(integer, unknown, double
precision, unknown) does not exist.

But in reality, I have this function like myFunction(integer,text,double
precision,text)

I have gone through all PostgreSQL documents and mail threads but couldn't
find any accurate solution.
Kindly provide an immediate workaround or a permanent solution.

FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no use of
it

select 'hello' return text type

select pg_typeof('hello') returns unknown type

Please help out to fix this.

By default any string literal is of unknown type because it can be 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from context real type and does retyping.

for example || operator is defined for text string, so when I write 'Hello' || 'world' is clean, so both string literal are of text type. Sometime there is not possible to detect real type - usually when context is not unambiguous. Then type of string literal stay "unknown".

you can use explicit typing This is unknown type'::text, or you should to check some typo error in your code.

check:

myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type column'::text);

Regards

Pavel

 

Thanks,
Praveen

Hi Pavel,

I agree with it,

But, We are running our query on different other databases like Oracle and MySql where they doensn't need ::text to append to define a text or character or vachar type,

The same query is being executed on Oracle and MySql fine but PostgreSQL treating it as unknown.

Another use case,

Here I just queried an integer without specifying ::integet ,but how could pg_typeof know that is an integer. ?

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

Thank you for quick answer,

This is a PostgreSQL bug, I tried this without orafce plugin, still the same error ,Let me know if you want me to share screenshots.

No, it is usual behave - you have to fix (change) your application. String literal in PostgreSQL is not varchar or text by default. Without context info, it is unknown.

Regards

Pavel

omega=# select pg_typeof('aaaa'::text);
+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+
(1 row)

omega=# select pg_typeof('aaaa');
+-----------+
| pg_typeof |
+-----------+
| unknown   |
+-----------+
(1 row)

it 100% correct

 

Thanks,

Praveen

On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Redhat Linux 6
Description:

Hi Guys,

We have recently migrated our oracle database to PostgreSQL database,and
migration went successfully.
We have used ORAFCE to provide backward compatibility.
But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or any
other user defined PostgreSQL functions

That is type UNKNOWN

If I try to call a function as below

select myFunction(1,'This is unknown type',90.01,'Again a unknown type
column');

It is raising an error like function myFunction(integer, unknown, double
precision, unknown) does not exist.

But in reality, I have this function like myFunction(integer,text,double
precision,text)

I have gone through all PostgreSQL documents and mail threads but couldn't
find any accurate solution.
Kindly provide an immediate workaround or a permanent solution.

FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no use of
it

select 'hello' return text type

select pg_typeof('hello') returns unknown type

Please help out to fix this.

By default any string literal is of unknown type because it can be 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from context real type and does retyping.

for example || operator is defined for text string, so when I write 'Hello' || 'world' is clean, so both string literal are of text type. Sometime there is not possible to detect real type - usually when context is not unambiguous. Then type of string literal stay "unknown".

you can use explicit typing This is unknown type'::text, or you should to check some typo error in your code.

check:

myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type column'::text);

Regards

Pavel

 

Thanks,
Praveen

I understand that Pavel,

But, in our context, we always have text/varchar2 inside single quotes,

Do we have any workaround ,to make PostgreSQL parse think single quoted string as text ?

Thanks,

Praveen.K

 

On Sat, Feb 10, 2018 at 2:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:50 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

I agree with it,

But, We are running our query on different other databases like Oracle and MySql where they doensn't need ::text to append to define a text or character or vachar type,

The same query is being executed on Oracle and MySql fine but PostgreSQL treating it as unknown.

PostgreSQL is not Oracle, or MySQL. It has different type system with different advantages and disadvantages.

PostgreSQL parser generate some type info, when it is possible

11111 --> integere

1111.22 --> numeric

'xxxxx' --> unknown, because

* 'hello' ... text

* '20180210' ... date

* '{xxx,xxxxs,dddd,kkkk}' ... array

* '(10,22,hhh,kkk)'  ... composite type

* '[10, 20, 30]' ... json maybe jsonb

* 'WKB (....)' ... postgresql custom type

There is not possible to detect just from value used type. Against Oracle or MySQL, PostgreSQL is very expandable -- there is possibility to have custom types, custom functions, ... that means so PostgreSQL extendible type system is very different from other databases.

 

Another use case,

Here I just queried an integer without specifying ::integet ,but how could pg_typeof know that is an integer. ?

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

Thank you for quick answer,

This is a PostgreSQL bug, I tried this without orafce plugin, still the same error ,Let me know if you want me to share screenshots.

No, it is usual behave - you have to fix (change) your application. String literal in PostgreSQL is not varchar or text by default. Without context info, it is unknown.

Regards

Pavel

omega=# select pg_typeof('aaaa'::text);
+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+
(1 row)

omega=# select pg_typeof('aaaa');
+-----------+
| pg_typeof |
+-----------+
| unknown   |
+-----------+
(1 row)

it 100% correct

 

Thanks,

Praveen

On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Redhat Linux 6
Description:

Hi Guys,

We have recently migrated our oracle database to PostgreSQL database,and
migration went successfully.
We have used ORAFCE to provide backward compatibility.
But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or any
other user defined PostgreSQL functions

That is type UNKNOWN

If I try to call a function as below

select myFunction(1,'This is unknown type',90.01,'Again a unknown type
column');

It is raising an error like function myFunction(integer, unknown, double
precision, unknown) does not exist.

But in reality, I have this function like myFunction(integer,text,double
precision,text)

I have gone through all PostgreSQL documents and mail threads but couldn't
find any accurate solution.
Kindly provide an immediate workaround or a permanent solution.

FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no use of
it

select 'hello' return text type

select pg_typeof('hello') returns unknown type

Please help out to fix this.

By default any string literal is of unknown type because it can be 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from context real type and does retyping.

for example || operator is defined for text string, so when I write 'Hello' || 'world' is clean, so both string literal are of text type. Sometime there is not possible to detect real type - usually when context is not unambiguous. Then type of string literal stay "unknown".

you can use explicit typing This is unknown type'::text, or you should to check some typo error in your code.

check:

myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type column'::text);

Regards

Pavel

 

Thanks,
Praveen

My functions are not ambiguous they are defined with proper types.

I have another question,

May I know how is PostgreSQL able to define its TYPE in the below scenario.

May I also request for some feed on the term COLLISION .

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 10:12 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

I understand that Pavel,

But, in our context, we always have text/varchar2 inside single quotes,

Postgresql cannot to know it.

 

Do we have any workaround ,to make PostgreSQL parse think single quoted string as text ?

explicit typing is correct solution for Postgres.

You can write own functions, where context will be clear and not ambiguous - and that is all. Check your functions, maybe you have forgotten collision.

Thanks,

Praveen.K

 

On Sat, Feb 10, 2018 at 2:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:50 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

I agree with it,

But, We are running our query on different other databases like Oracle and MySql where they doensn't need ::text to append to define a text or character or vachar type,

The same query is being executed on Oracle and MySql fine but PostgreSQL treating it as unknown.

PostgreSQL is not Oracle, or MySQL. It has different type system with different advantages and disadvantages.

PostgreSQL parser generate some type info, when it is possible

11111 --> integere

1111.22 --> numeric

'xxxxx' --> unknown, because

* 'hello' ... text

* '20180210' ... date

* '{xxx,xxxxs,dddd,kkkk}' ... array

* '(10,22,hhh,kkk)'  ... composite type

* '[10, 20, 30]' ... json maybe jsonb

* 'WKB (....)' ... postgresql custom type

There is not possible to detect just from value used type. Against Oracle or MySQL, PostgreSQL is very expandable -- there is possibility to have custom types, custom functions, ... that means so PostgreSQL extendible type system is very different from other databases.

 

Another use case,

Here I just queried an integer without specifying ::integet ,but how could pg_typeof know that is an integer. ?

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

Thank you for quick answer,

This is a PostgreSQL bug, I tried this without orafce plugin, still the same error ,Let me know if you want me to share screenshots.

No, it is usual behave - you have to fix (change) your application. String literal in PostgreSQL is not varchar or text by default. Without context info, it is unknown.

Regards

Pavel

omega=# select pg_typeof('aaaa'::text);
+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+
(1 row)

omega=# select pg_typeof('aaaa');
+-----------+
| pg_typeof |
+-----------+
| unknown   |
+-----------+
(1 row)

it 100% correct

 

Thanks,

Praveen

On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Redhat Linux 6
Description:

Hi Guys,

We have recently migrated our oracle database to PostgreSQL database,and
migration went successfully.
We have used ORAFCE to provide backward compatibility.
But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or any
other user defined PostgreSQL functions

That is type UNKNOWN

If I try to call a function as below

select myFunction(1,'This is unknown type',90.01,'Again a unknown type
column');

It is raising an error like function myFunction(integer, unknown, double
precision, unknown) does not exist.

But in reality, I have this function like myFunction(integer,text,double
precision,text)

I have gone through all PostgreSQL documents and mail threads but couldn't
find any accurate solution.
Kindly provide an immediate workaround or a permanent solution.

FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no use of
it

select 'hello' return text type

select pg_typeof('hello') returns unknown type

Please help out to fix this.

By default any string literal is of unknown type because it can be 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from context real type and does retyping.

for example || operator is defined for text string, so when I write 'Hello' || 'world' is clean, so both string literal are of text type. Sometime there is not possible to detect real type - usually when context is not unambiguous. Then type of string literal stay "unknown".

you can use explicit typing This is unknown type'::text, or you should to check some typo error in your code.

check:

myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type column'::text);

Regards

Pavel

 

Thanks,
Praveen

Yes, Pavel, I do have multiple functions with same name and different number/types of parameters.

Is there any problem with this kind of function creation (I mean method overloading)

Thanks.

Praveen

On Sat, Feb 10, 2018 at 3:31 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 10:31 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

My functions are not ambiguous they are defined with proper types.

I have another question,

May I know how is PostgreSQL able to define its TYPE in the below scenario.

It is different context
 

May I also request for some feed on the term COLLISION .

maybe you have two or more functions with same name

 

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:51 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 10:12 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

I understand that Pavel,

But, in our context, we always have text/varchar2 inside single quotes,

Postgresql cannot to know it.

 

Do we have any workaround ,to make PostgreSQL parse think single quoted string as text ?

explicit typing is correct solution for Postgres.

You can write own functions, where context will be clear and not ambiguous - and that is all. Check your functions, maybe you have forgotten collision.

Thanks,

Praveen.K

 

On Sat, Feb 10, 2018 at 2:27 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:50 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

I agree with it,

But, We are running our query on different other databases like Oracle and MySql where they doensn't need ::text to append to define a text or character or vachar type,

The same query is being executed on Oracle and MySql fine but PostgreSQL treating it as unknown.

PostgreSQL is not Oracle, or MySQL. It has different type system with different advantages and disadvantages.

PostgreSQL parser generate some type info, when it is possible

11111 --> integere

1111.22 --> numeric

'xxxxx' --> unknown, because

* 'hello' ... text

* '20180210' ... date

* '{xxx,xxxxs,dddd,kkkk}' ... array

* '(10,22,hhh,kkk)'  ... composite type

* '[10, 20, 30]' ... json maybe jsonb

* 'WKB (....)' ... postgresql custom type

There is not possible to detect just from value used type. Against Oracle or MySQL, PostgreSQL is very expandable -- there is possibility to have custom types, custom functions, ... that means so PostgreSQL extendible type system is very different from other databases.

 

Another use case,

Here I just queried an integer without specifying ::integet ,but how could pg_typeof know that is an integer. ?

Thanks,

Praveen

On Sat, Feb 10, 2018 at 2:09 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2018-02-10 9:22 GMT+01:00 Praveen Kumar <praveenkumar52028@gmail.com>:

Hi Pavel,

Thank you for quick answer,

This is a PostgreSQL bug, I tried this without orafce plugin, still the same error ,Let me know if you want me to share screenshots.

No, it is usual behave - you have to fix (change) your application. String literal in PostgreSQL is not varchar or text by default. Without context info, it is unknown.

Regards

Pavel

omega=# select pg_typeof('aaaa'::text);
+-----------+
| pg_typeof |
+-----------+
| text      |
+-----------+
(1 row)

omega=# select pg_typeof('aaaa');
+-----------+
| pg_typeof |
+-----------+
| unknown   |
+-----------+
(1 row)

it 100% correct

 

Thanks,

Praveen

On Sat, Feb 10, 2018 at 1:44 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

this is not PostgreSQL bug

2018-02-10 8:57 GMT+01:00 PG Bug reporting form <noreply@postgresql.org>:

The following bug has been logged on the website:

Bug reference:      15057
Logged by:          Praveen Kumar
Email address:      praveenkumar52028@gmail.com
PostgreSQL version: 9.6.0
Operating system:   Redhat Linux 6
Description:

Hi Guys,

We have recently migrated our oracle database to PostgreSQL database,and
migration went successfully.
We have used ORAFCE to provide backward compatibility.
But ,unfortunately we stuck with one issue in calling DECODE /TRUNC/or any
other user defined PostgreSQL functions

That is type UNKNOWN

If I try to call a function as below

select myFunction(1,'This is unknown type',90.01,'Again a unknown type
column');

It is raising an error like function myFunction(integer, unknown, double
precision, unknown) does not exist.

But in reality, I have this function like myFunction(integer,text,double
precision,text)

I have gone through all PostgreSQL documents and mail threads but couldn't
find any accurate solution.
Kindly provide an immediate workaround or a permanent solution.

FYI - I have tried latest PostgreSQL version(PostgreSQL 10) Still no use of
it

select 'hello' return text type

select pg_typeof('hello') returns unknown type

Please help out to fix this.

By default any string literal is of unknown type because it can be 'AHOJ', '222.22', '2000-12-12'. In next step, PostgreSQL try to detect from context real type and does retyping.

for example || operator is defined for text string, so when I write 'Hello' || 'world' is clean, so both string literal are of text type. Sometime there is not possible to detect real type - usually when context is not unambiguous. Then type of string literal stay "unknown".

you can use explicit typing This is unknown type'::text, or you should to check some typo error in your code.

check:

myFunction(1,'This is unknown type'::text,90.01,'Again a unknown type column'::text);

Regards

Pavel

 

Thanks,
Praveen

Hi Andrew,

Thanks for picking up this,

To have  backward compatibility with my application (Specially queries)

We have used orafce plugin, it created multiple DECODE functions with different parameters.

While calling one of these DECODE functions from my existing select query,I am seeing an error saying 

decode(numeric,integer,unknown,unknow) is not available

By the way, PostgreSQL is allowing me to do method overloading,So I am able to write multiple functions with the same name and different parameters

Thanks,

Praveen

On Sat, Feb 10, 2018 at 3:37 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:

>>>>> "PG" == PG Bug reporting form <noreply@postgresql.org> writes:

 PG> If I try to call a function as below

 PG> select myFunction(1,'This is unknown type',90.01,'Again a unknown
 PG> type column');

 PG> It is raising an error like function myFunction(integer, unknown,
 PG> double precision, unknown) does not exist.

I think you need to be a bit more specific about what precisely you did,
because this is what I tried:

create or replace function
  myfunc(a integer, b text, c double precision, d text)
  returns void language plpgsql
  as $$ begin raise info 'myfunc called'; end; $$;

select myfunc(1,'foo',90.01,'bar');
INFO:  myfunc called

In particular: do you have more than one function of the same name? Were
you actually calling the function from JDBC with parameters (and if so
what types) rather than as a plain select? Show us an exact transcript,
not just your editorialized summary.

--
Andrew (irc:RhodiumToad)

In this case I don't have a specific function like decode(integer,integer,text,text) ,I just have decode(anyelement,anyelement,text,text) 

My question is how does this second case working without a specific function with required data types?