Re: Unrecognized type error (postgres 9.1.4)

On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote:
On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
On Monday, April 08, 2013 7:28 PM Rodrigo Barboza wrote:
>> You have identified rightly in your other mail that it happens in
function
>> convert_numeric_to_scalar(). But I think adding user defined datatype
>> handling in this function might
>> not be straight forward. You can refer below text from link
>> http://www.postgresql.org/docs/9.2/static/xoper-optimization.html
>> "You can use scalarltsel and scalargtsel for comparisons on data types
that
>> have some sensible means of being converted into numeric scalars for
range
>> comparisons. If possible, add the data type to those understood by the
>> function convert_to_scalar() in src/backend/utils/adt/selfuncs.c.
>> (Eventually, this function should be replaced by per-data-type functions
>> identified through a column of the pg_type system catalog; but that
hasn't
>> happened yet.) If you do not do this, things will still work, but the
>> optimizer's estimates won't be as good as they could be."

>> I could think of following workaround's for your problem.

>> 1. For your table, set values for autovacuum_analyze_threshold and
>> autovacuum_analyze_scale_factor very high (refer Create Table), so that
it
>> doesn't analyze your
>>  table and return default selectivity, which should work fine if your sql
>> statements are simple.

>> 2. Write your own selectivity functions and return default Selectivity
from
>> them and use them while creating operators.

>> 3. Use bind value in where clause, it will return default selectivity for
>> it.

>> 4. Some other way, with which it does not collect histogram stats (means
it
>> will use minimal stats compute_minimal_stats). I am not sure but you can
try
>> once without defining operators.

>> All the above way's can help to resolve your current problem, but they
are
>> not good way if you have some usage of sql statements with these
datatypes.


> Hi, Amit, thank you for your reply.

> The text says: "if you do not do this, things will still work, but
the optimizer's estimates won't be as good as they could be.".
> But this is not what is happening, he is raising unsupported type error.

> I think option 1 and 4 is not for me.
> Option 2 could be a solution, but I don't know how to start writing this
kind of function. Do you any tips?

You need to write C function and use them while creating operator's, Refer
link:
http://www.postgresql.org/docs/9.2/static/xfunc-c.html

> I didn't understand option 3, what did you mean?

Option 3 means you can use prepared statements to specify bind values for
where clause.
Refer link:
http://www.postgresql.org/docs/9.2/static/sql-prepare.html

With Regards,
Amit Kapila.