Re: Unrecognized type error (postgres 9.1.4)

On Wed, Apr 10, 2013 at 12:24 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>wrote:

>
> On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote:
> On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)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.
>
>
Write the c funtions is OK. I've written some functions for my type, but
write the selectivity function is not clear for me.
In documentation it says this is out the doc scope. But I read the source
code and it doesn't seem to be trivial.
There are lots of function calls to deal.

Would it be very bad to offer no selectivity function for my type?