Re: Unrecognized type error (postgres 9.1.4)

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:
> On Sat, Apr 6, 2013 at 10:39 PM, Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com>
> wrote:
> Ok! I will try to reproduce in a smaller scenario.
> On Sat, Apr 6, 2013 at 9:53 PM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> On Saturday, April 06, 2013 12:18 PM Rodrigo Barboza wrote:
> >On Sat, Apr 6, 2013 at 12:23 AM, Amit Kapila <amit(dot)kapila(at)huawei(dot)com>
> wrote:
> >>>> On Saturday, April 06, 2013 3:57 AM Rodrigo Barboza wrote:
>
> >>>>Hello.
> >>>> I created a type my_uint that is a unsigned int 32.
>
> >>>> I am trying to update data of a table that contains a column of this
> type.
> >>>> Here is what happens:
>
> >>>> postgresql=> explain analyze UPDATE attribute_type_conf SET rowform =
> rowform +1 where rowform <= 18;
> >>>> ERROR: unsupported type: 132852
> >>>> Can you post your complete test (like your type creation and its use
> for
> >>>> table and any initial data you loaded to it)?
>
>
> >>> Well, it's a lot of data.
> >>> May I send it atached?
> >>If you can't make it to small reproducible test, then you can send.
>
>
> >I was trying to reproduce the error, but it is was not raising error.
> >I didn't change anything.
> >Last week I dropped the database, created it again, populated my db and
> when it was time to run the query, the error raised.
> >I'm puzzled. I can't trust it...
>
> > But now I run this script and the error finally raised. It seems random.
>
> The reason for seldom behavior is that, it occurs only when the value you
> are giving in your where clause lies in valid boundary of histogram (refer
> function ineq_histogram_selectivity).
>
> > psql -U testuser testdb -c "drop table if exists tm32;"
> > psql -U testuser testdb -c "create table tm32 (a tmuint32);"
>
>
> > for ((i=0; i<100; i++));do
> > psql -U testuser testdb <<ENDOFSQLDATA
> > insert into tm32 values($i);
> > ENDOFSQLDATA
> > done
>
> > for ((i=0; i<100; i++ )); do
> > psql -U testuser testdb <<ENDOFSQLDATA
> > BEGIN;
> > UPDATE tm32 SET a = a + 1 WHERE a > $i;
> > END;
> > ENDOFSQLDATA
> > done
>
> > The error message:
> > ERROR: unsupported type: 202886
> > ROLLBACK
>
> 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.
>
> With Regards,
> Amit Kapila.
>
>

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?
I didn't understand option 3, what did you mean?