Re: [HACKERS] 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 rodrigombu...@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.kap...@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.kap...@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; i100; i++));do psql -U testuser testdb ENDOFSQLDATA insert into tm32 values($i); ENDOFSQLDATA done for ((i=0; i100; 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila amit.kap...@huawei.com wrote: On Monday, April 08, 2013 7:28 PM Rodrigo Barboza wrote: On Sat, Apr 6, 2013 at 10:39 PM, Rodrigo Barboza rodrigombu...@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.kap...@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.kap...@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; i100; i++));do psql -U testuser testdb ENDOFSQLDATA insert into tm32 values($i); ENDOFSQLDATA done for ((i=0; i100; 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?
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote: On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila amit.kap...@huawei.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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Wed, Apr 10, 2013 at 12:24 AM, Amit Kapila amit.kap...@huawei.comwrote: On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote: On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila amit.kap...@huawei.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?
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
Rodrigo Barboza rodrigombu...@gmail.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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote: Rodrigo Barboza rodrigombu...@gmail.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?
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Rodrigo Barboza rodrigombu...@gmail.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
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza
rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Rodrigo Barboza rodrigombu...@gmail.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 =
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Mon, Apr 8, 2013 at 12:14 PM, Rodrigo Barboza rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza
rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza rodrigombu...@gmail.com
wrote:
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Rodrigo Barboza rodrigombu...@gmail.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))
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Mon, Apr 8, 2013 at 4:30 PM, Rodrigo Barboza rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 12:14 PM, Rodrigo Barboza
rodrigombu...@gmail.comwrote:
On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza rodrigombu...@gmail.com
wrote:
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza
rodrigombu...@gmail.com wrote:
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Rodrigo Barboza rodrigombu...@gmail.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,
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Sat, Apr 6, 2013 at 12:23 AM, Amit Kapila amit.kap...@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)? With Regards, Amit Kapila. Well, it's a lot of data. May I send it atached?
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
On Saturday, April 06, 2013 12:18 PM Rodrigo Barboza wrote: On Sat, Apr 6, 2013 at 12:23 AM, Amit Kapila amit.kap...@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. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
Ok! I will try to reproduce in a smaller scenario. On Sat, Apr 6, 2013 at 9:53 PM, Amit Kapila amit.kap...@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.kap...@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. With Regards, Amit Kapila.
Re: [HACKERS] Unrecognized type error (postgres 9.1.4)
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)? With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
