Re: Unrecognized type error (postgres 9.1.4)

On Monday, April 08, 2013 7:28 PM Rodrigo Barboza wrote:
On Sat, Apr 6, 2013 at 10:39 PM, Rodrigo Barboza <rodrigombufrj@gmail.com>
wrote:
Ok! I will try to reproduce in a smaller scenario. 
On Sat, Apr 6, 2013 at 9:53 PM, Amit Kapila <amit.kapila@huawei.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.kapila@huawei.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.