Re: Unrecognized type error (postgres 9.1.4)

On Mon, Apr 8, 2013 at 4:30 PM, Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com>wrote:

>
>
>
>
> On Mon, Apr 8, 2013 at 12:14 PM, Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com>wrote:
>
>>
>>
>>
>> On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com
>> > wrote:
>>
>>>
>>>
>>>
>>> On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza <
>>> rodrigombufrj(at)gmail(dot)com> wrote:
>>>
>>>>
>>>>
>>>> On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>
>>>>> Rodrigo Barboza <rodrigombufrj(at)gmail(dot)com> writes:
>>>>> > UPDATE tm32 SET a = a + 1 WHERE a > $i;
>>>>> > ERROR: unsupported type: 202886
>>>>>
>>>>> I'm betting that's coming from scalargtsel, which doesn't know anything
>>>>> about your type, but you've nominated it to be the selectivity function
>>>>> for ">" anyway.
>>>>>
>>>>> /*
>>>>> * Can't get here unless someone tries to use
>>>>> scalarltsel/scalargtsel on
>>>>> * an operator with one numeric and one non-numeric operand.
>>>>> */
>>>>> elog(ERROR, "unsupported type: %u", typid);
>>>>>
>>>>> regards, tom lane
>>>>>
>>>>
>>>>
>>>> Yes, I found it in the code, but I followed the example from the
>>>> postgres documentation that uses this function.
>>>> And why does it work sometimes? Why not other times?
>>>>
>>>>
>>>
>>> Here is a very simple case and weird behavior. I select * from a table
>>> and returns 4 entries.
>>> But when I run with a filter the error raises and crazy values are
>>> printed from the params.
>>>
>>> Here is my funcitons where I compare the values:
>>>
>>> typedef uint32_t TmUInt32;
>>>
>>> static int
>>> tmuint32_int32_abs_cmp_internal(TmUInt32 a, int32_t b)
>>> {
>>> int ret;
>>> elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
>>> if (a < b) ret = -1;
>>> else if (a > b) ret = 1;
>>> else ret = 0;
>>> elog(NOTICE, "funcao:%s linha:%d, ret: %d a: %u\n", *_FUNCTION_*, *
>>> _LINE_*, ret, a);
>>> return ret;
>>> }
>>>
>>> PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);
>>>
>>> Datum
>>> tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
>>> {
>>> TmUInt32 *param1;
>>> int32_t param2;
>>> elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
>>>
>>> if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();
>>>
>>> param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
>>> param2 = DatumGetInt32(PG_GETARG_DATUM(1));
>>>
>>> elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*,
>>> *_LINE_*, *param1, param2);
>>> PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
>>> }
>>>
>>>
>>> And here is the simple test.
>>>
>>> -- SIMPLE QUERY
>>> select * from a;
>>>
>>> NOTICE: funcao:tmuint32_out linha:191
>>>
>>> NOTICE: funcao:tmuint32_out linha:191
>>>
>>> NOTICE: funcao:tmuint32_out linha:191
>>>
>>> NOTICE: funcao:tmuint32_out linha:191
>>>
>>> a
>>> ---
>>> 0
>>> 1
>>> 2
>>> 3
>>> (4 rows)
>>>
>>>
>>> _________________________________________________________________
>>>
>>> -- QUERY WHITH FILTER
>>> select * from a where a > 1;
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1296
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742
>>>
>>> NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1
>>>
>>> ERROR: unsupported type: 220200
>>>
>>>
>>
>> I found that the problem is in the highlithed line. I'm getting the wrong
>> value from param1. But why this behavior?
>>
>> PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);
>>
>> Datum
>> tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
>> {
>> TmUInt32 *param1;
>> int32_t param2;
>> elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
>>
>> if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();
>>
>> param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
>> param2 = DatumGetInt32(PG_GETARG_DATUM(1));
>>
>> elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*
>> , *_LINE_*, *param1, param2);
>> PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
>> }
>>
>
>
> Wow, this simple test reproduces the problem.
> After running this script a few times, this simple query fails:
> select * from tm32 where a > 1;
> What is weird is when I remove the commutator from the operator '>', there
> is no error. But the crazy values are still there, like it was never
> removed with the drop database.
>
>
> #!/bin/sh
> export PGPASSWORD=mypass;
>
> psql -U testuser testdb -c "truncade tm64;"
> psql -U testuser testdb -c "create table tm64 (a tmuint64);"
>
>
> for ((i=1; i<100; i++));do
> psql -U testuser testdb <<ENDOFSQLDATA
> insert into tm64 values($i);
> ENDOFSQLDATA
> done
>
> for ((i=0; i<100; i++ )); do
> psql -U testuser testdb <<ENDOFSQLDATA
> BEGIN;
> UPDATE tm64 SET a = a + 1 WHERE a > $i;
> END;
> ENDOFSQLDATA
> done
>
>

Guys, I found that function that raises the error and included my current
type id in the switch and the error is gone. The problem is that this id is
dynamic, every time you create it, you receive a new id.
Is there solution to this problem?

convert_numeric_to_scalar(Datum value, Oid typid)
{
switch (typid)
{
case 271351:
elog(NOTICE,"%s %s %d
%lf",__FILE__,__FUNCTION__,__LINE__,(double)(*(uint32_t
*)DatumGetPointer(value)));
return (double)(*(uint32_t
*)DatumGetPointer(value));
case BOOLOID:
return (double) DatumGetBool(value);
case INT2OID:
return (double) DatumGetInt16(value);
case INT4OID:
elog(NOTICE,"%s %s %d
%lf",__FILE__,__FUNCTION__,__LINE__,(double)DatumGetInt32(value));
return (double) DatumGetInt32(value);
case INT8OID:
return (double) DatumGetInt64(value);
case FLOAT4OID:
return (double) DatumGetFloat4(value);
case FLOAT8OID:
return (double) DatumGetFloat8(value);
case NUMERICOID:
/* Note: out-of-range values will be clamped to
+-HUGE_VAL */
return (double)

DatumGetFloat8(DirectFunctionCall1(numeric_float8_no_overflow,

value));
case OIDOID:
case REGPROCOID:
case REGPROCEDUREOID:
case REGOPEROID:
case REGOPERATOROID:
case REGCLASSOID:
case REGTYPEOID:
case REGCONFIGOID:
case REGDICTIONARYOID:
/* we can treat OIDs as integers... */
return (double) DatumGetObjectId(value);
}

/*
* Can't get here unless someone tries to use
scalarltsel/scalargtsel on
* an operator with one numeric and one non-numeric operand.
*/
elog(ERROR, "unsupported type: %u", typid);
return 0;
}