Re: Unrecognized type error (postgres 9.1.4)

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