Thread: Unrecognized type error (postgres 9.1.4)
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
postgresql=> explain analyze UPDATE attribute_type_conf SET rowform = rowform +1 where rowform <= 17;
ERROR: unsupported type: 132852
postgresql=> explain analyze UPDATE attribute_type_conf SET rowform = rowform +1 where rowform <= 16;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update on attribute_type_conf (cost=0.00..25.92 rows=432 width=121) (actual time=4.522..4.522 rows=0 loops=1)
-> Seq Scan on attribute_type_conf (cost=0.00..25.92 rows=432 width=121) (actual time=0.023..0.592 rows=387 loops=1)
Filter: (rowform <= 16)
Trigger attribute_type_conf_trigger: time=0.150 calls=1
Total runtime: 4.721 ms
(5 rows)
As you can see I get an error of unsupported type for values above 16.
While I was writing this email I tried again and surprisling I got this:
postgresql=> explain analyze UPDATE attribute_type_conf SET rowform = rowform +1 where rowform <= 17::bigint;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update on attribute_type_conf (cost=0.00..27.20 rows=390 width=121) (actual time=3.507..3.507 rows=0 loops=1)
-> Seq Scan on attribute_type_conf (cost=0.00..27.20 rows=390 width=121) (actual time=0.012..0.403 rows=390 loops=1)
Filter: (rowform <= 17::bigint)
Trigger attribute_type_conf_trigger: time=0.126 calls=1
Total runtime: 3.666 ms
(5 rows)
postgresql=> explain analyze UPDATE attribute_type_conf SET rowform = rowform +1 where rowform <= 17;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
Update on attribute_type_conf (cost=0.00..27.20 rows=390 width=121) (actual time=3.200..3.200 rows=0 loops=1)
-> Seq Scan on attribute_type_conf (cost=0.00..27.20 rows=390 width=121) (actual time=0.031..0.507 rows=353 loops=1)
Filter: (rowform <= 17)
Trigger attribute_type_conf_trigger: time=0.130 calls=1
Total runtime: 3.364 ms
It seems like it is randomly failing.
Does anybody know about this issue?
> 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.
<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Sat, Apr 6, 2013 at 12:23 AM, AmitKapila <span dir="ltr"><<a href="mailto:amit.kapila@huawei.com" target="_blank">amit.kapila@huawei.com</a>></span>wrote:<br /><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px#ccc solid;padding-left:1ex"><div class="im">> On Saturday, April 06, 2013 3:57 AM Rodrigo Barbozawrote:<br /><br /> >Hello.<br /> > I created a type my_uint that is a unsigned int 32.<br /><br /> > I amtrying to update data of a table that contains a column of this type.<br /> > Here is what happens:<br /><br /> >postgresql=> explain analyze UPDATE attribute_type_conf SET rowform =<br /> rowform +1 where rowform <= 18;<br/> > ERROR: unsupported type: 132852<br /><br /></div>Can you post your complete test (like your type creationand its use for<br /> table and any initial data you loaded to it)?<br /><br /><br /> With Regards,<br /> Amit Kapila.<br/><br /></blockquote></div><br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra" style="style">Well,it's a lot of data.</div><div class="gmail_extra" style="style">May I send it atached?</div></div>
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. With Regards, Amit Kapila.
<div dir="ltr">Ok! I will try to reproduce in a smaller scenario. </div><div class="gmail_extra"><br /><br /><div class="gmail_quote">OnSat, Apr 6, 2013 at 9:53 PM, Amit Kapila <span dir="ltr"><<a href="mailto:amit.kapila@huawei.com"target="_blank">amit.kapila@huawei.com</a>></span> wrote:<br /><blockquote class="gmail_quote"style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="im">On Saturday, April06, 2013 12:18 PM Rodrigo Barboza wrote:<br /> >On Sat, Apr 6, 2013 at 12:23 AM, Amit Kapila <<a href="mailto:amit.kapila@huawei.com">amit.kapila@huawei.com</a>><br/> wrote:<br /> >> On Saturday, April 06, 20133:57 AM Rodrigo Barboza wrote:<br /><br /> >>Hello.<br /> >> I created a type my_uint that is a unsignedint 32.<br /><br /> >> I am trying to update data of a table that contains a column of this<br /> type.<br/> >> Here is what happens:<br /><br /> >> postgresql=> explain analyze UPDATE attribute_type_confSET rowform =<br /> rowform +1 where rowform <= 18;<br /> >> ERROR: unsupported type: 132852<br/> >> Can you post your complete test (like your type creation and its use for<br /> >> table and anyinitial data you loaded to it)?<br /><br /><br /><br /></div><div class="im">> Well, it's a lot of data.<br /> >May I send it atached?<br /></div>If you can't make it to small reproducible test, then you can send.<br /><br /> WithRegards,<br /> Amit Kapila.<br /><br /></blockquote></div><br /></div>
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.If you can't make it to small reproducible test, then you can send.
> May I send it atached?
With Regards,
Amit Kapila.
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.
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
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
ROLLBACKHere is my script for creating the types. I changed the installation path to a generic name in this email to be clear where to put the path to the so files.
#!/bin/sh
export PGPASSWORD=mypass
psql -U root testdb --quiet -v ON_ERROR_STOP=1 trafip <<ENDOFSQLDATA
BEGIN;
CREATE TYPE tmuint32;
CREATE TYPE tmuint64;
CREATE FUNCTION tmuint32_in(cstring)
RETURNS tmuint32
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_out(tmuint32)
RETURNS cstring
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_recv(internal)
RETURNS tmuint32
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_send(tmuint32)
RETURNS bytea
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_in(cstring)
RETURNS tmuint64
AS '/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_out(tmuint64)
RETURNS cstring AS
'/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_recv(internal)
RETURNS tmuint64 AS
'/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_send(tmuint64)
RETURNS bytea AS
'/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE TYPE tmuint32 (
internallength = 4,
input = tmuint32_in,
output = tmuint32_out,
receive = tmuint32_recv,
send = tmuint32_send,
alignment = int
);
CREATE TYPE tmuint64 (
internallength = 8,
input = tmuint64_in,
output = tmuint64_out,
receive = tmuint64_recv,
send = tmuint64_send,
alignment = double
);
CREATE FUNCTION int2_to_tmuint32(int2)
RETURNS tmuint32
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int4_to_tmuint32(int4)
RETURNS tmuint32
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int8_to_tmuint32(int8)
RETURNS tmuint32
AS '/my/install/dir/tmuint32'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int2_to_tmuint64(int2)
RETURNS tmuint64
AS '/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int4_to_tmuint64(int4)
RETURNS tmuint64
AS '/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int8_to_tmuint64(int8)
RETURNS tmuint64
AS '/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION numeric_to_tmuint64(numeric)
RETURNS tmuint64
AS '/my/install/dir/tmuint64'
LANGUAGE C IMMUTABLE STRICT;
CREATE CAST (int2 AS tmuint32) WITH FUNCTION int2_to_tmuint32(int2) AS ASSIGNMENT;
CREATE CAST (int4 AS tmuint32) WITH FUNCTION int4_to_tmuint32(int4) AS ASSIGNMENT;
CREATE CAST (int8 AS tmuint32) WITH FUNCTION int8_to_tmuint32(int8) AS ASSIGNMENT;
CREATE CAST (int2 AS tmuint64) WITH FUNCTION int2_to_tmuint64(int2) AS ASSIGNMENT;
CREATE CAST (int4 AS tmuint64) WITH FUNCTION int4_to_tmuint64(int4) AS ASSIGNMENT;
CREATE CAST (int8 AS tmuint64) WITH FUNCTION int8_to_tmuint64(int8) AS ASSIGNMENT;
CREATE CAST (numeric AS tmuint64) WITH FUNCTION numeric_to_tmuint64(numeric) AS ASSIGNMENT;
CREATE FUNCTION tmuint32_int16_add(tmuint32, int2) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_add(tmuint32, int4) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_add(int2, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_add(int4, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_add(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_sub(tmuint32, int2) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_sub(tmuint32, int4) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_sub(int2, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_sub(int4, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_sub(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_mul(tmuint32, int2) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_mul(tmuint32, int4) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_mul(int2, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_mul(int4, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_mul(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_div(tmuint32, int2) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_div(tmuint32, int4) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_div(int2, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_div(int4, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_div(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_accum(tmuint64[], tmuint32) RETURNS tmuint64[] AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_avg(tmuint64[]) RETURNS float8 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_max(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_min(tmuint32, tmuint32) RETURNS tmuint32 AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_add(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_add(tmuint64, smallint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_add(tmuint64, integer) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_add(tmuint64, bigint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_add(smallint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_add(integer, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_add(bigint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_sub(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_sub(tmuint64, smallint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_sub(tmuint64, integer) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_sub(tmuint64, bigint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_sub(smallint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_sub(integer, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_sub(bigint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_mul(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_mul(tmuint64, smallint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_mul(tmuint64, integer) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_mul(tmuint64, bigint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_mul(smallint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_mul(integer, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_mul(bigint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_div(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_div(tmuint64, smallint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_div(tmuint64, integer) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_div(tmuint64, bigint) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_div(smallint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_div(integer, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_div(bigint, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_accum(tmuint64[], tmuint64) RETURNS tmuint64[] AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_avg(tmuint64[]) RETURNS float8 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_max(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_min(tmuint64, tmuint64) RETURNS tmuint64 AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR + ( leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_add);
CREATE OPERATOR + ( leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_add);
CREATE OPERATOR + ( leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_add);
CREATE OPERATOR + ( leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_add);
CREATE OPERATOR + ( leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_add);
CREATE OPERATOR - ( leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_sub);
CREATE OPERATOR - ( leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_sub);
CREATE OPERATOR - ( leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_sub);
CREATE OPERATOR - ( leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_sub);
CREATE OPERATOR - ( leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_sub);
CREATE OPERATOR * ( leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_mul);
CREATE OPERATOR * ( leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_mul);
CREATE OPERATOR * ( leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_mul);
CREATE OPERATOR * ( leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_mul);
CREATE OPERATOR * ( leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_mul);
CREATE OPERATOR / ( leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_div);
CREATE OPERATOR / ( leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_div);
CREATE OPERATOR / ( leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_div);
CREATE OPERATOR / ( leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_div);
CREATE OPERATOR / ( leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_div);
CREATE OPERATOR + (leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_add);
CREATE OPERATOR + (leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_add);
CREATE OPERATOR + (leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_add);
CREATE OPERATOR + (leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_add);
CREATE OPERATOR + (leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_add);
CREATE OPERATOR + (leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_add);
CREATE OPERATOR + (leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_add);
CREATE OPERATOR - (leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_sub);
CREATE OPERATOR - (leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_sub);
CREATE OPERATOR - (leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_sub);
CREATE OPERATOR - (leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_sub);
CREATE OPERATOR - (leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_sub);
CREATE OPERATOR - (leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_sub);
CREATE OPERATOR - (leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_sub);
CREATE OPERATOR * (leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_mul);
CREATE OPERATOR * (leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_mul);
CREATE OPERATOR * (leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_mul);
CREATE OPERATOR * (leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_mul);
CREATE OPERATOR * (leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_mul);
CREATE OPERATOR * (leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_mul);
CREATE OPERATOR * (leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_mul);
CREATE OPERATOR / (leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_div);
CREATE OPERATOR / (leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_div);
CREATE OPERATOR / (leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_div);
CREATE OPERATOR / (leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_div);
CREATE OPERATOR / (leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_div);
CREATE OPERATOR / (leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_div);
CREATE OPERATOR / (leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_div);
CREATE AGGREGATE sum (tmuint32)
(
sfunc = tmuint32_tmuint32_add,
stype = tmuint32,
initcond = '0'
);
CREATE AGGREGATE max (tmuint32)
(
sfunc = tmuint32_max,
stype = tmuint32
);
CREATE AGGREGATE min (tmuint32)
(
sfunc = tmuint32_min,
stype = tmuint32
);
CREATE AGGREGATE avg (tmuint32)
(
sfunc = tmuint32_accum,
stype = tmuint64[],
finalfunc = tmuint32_avg,
initcond = '{0,0}'
);
CREATE AGGREGATE sum (tmuint64)
(
sfunc = tmuint64_tmuint64_add,
stype = tmuint64,
initcond = '0'
);
CREATE AGGREGATE max (tmuint64)
(
sfunc = tmuint64_max,
stype = tmuint64
);
CREATE AGGREGATE min (tmuint64)
(
sfunc = tmuint64_min,
stype = tmuint64
);
CREATE AGGREGATE avg (tmuint64)
(
sfunc = tmuint64_accum,
stype = tmuint64[],
finalfunc = tmuint64_avg,
initcond = '{0,0}'
);
CREATE FUNCTION tmuint32_int16_abs_lt(tmuint32, int2) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_lt(tmuint32, int4) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int64_abs_lt(tmuint32, int8) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_lt(int2, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_lt(int4, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint32_abs_lt(int8, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_lt(tmuint32, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_abs_le(tmuint32, int2) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_le(tmuint32, int4) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int64_abs_le(tmuint32, int8) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_le(int2, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_le(int4, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint32_abs_le(int8, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_le(tmuint32, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_abs_eq(tmuint32, int2) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_eq(tmuint32, int4) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int64_abs_eq(tmuint32, int8) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_eq(int2, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_eq(int4, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint32_abs_eq(int8, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_eq(tmuint32, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_abs_ge(tmuint32, int2) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_ge(tmuint32, int4) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int64_abs_ge(tmuint32, int8) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_ge(int2, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_ge(int4, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint32_abs_ge(int8, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_ge(tmuint32, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int16_abs_gt(tmuint32, int2) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_gt(tmuint32, int4) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int64_abs_gt(tmuint32, int8) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_gt(int2, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_gt(int4, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint32_abs_gt(int8, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_gt(tmuint32, tmuint32) RETURNS bool
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_abs_lt(tmuint64, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_abs_le(tmuint64, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_abs_eq(tmuint64, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_abs_ge(tmuint64, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_tmuint64_abs_gt(tmuint64, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_lt(tmuint64, smallint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_le(tmuint64, smallint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_eq(tmuint64, smallint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_ge(tmuint64, smallint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_gt(tmuint64, smallint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_lt(smallint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_le(smallint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_eq(smallint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_ge(smallint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_gt(smallint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_lt(tmuint64, integer) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_le(tmuint64, integer) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_eq(tmuint64, integer) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_ge(tmuint64, integer) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_gt(tmuint64, integer) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_lt(integer, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_le(integer, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_eq(integer, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_ge(integer, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_gt(integer, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_lt(tmuint64, bigint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_le(tmuint64, bigint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_eq(tmuint64, bigint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_ge(tmuint64, bigint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_gt(tmuint64, bigint) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_lt(bigint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_le(bigint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_eq(bigint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_ge(bigint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_gt(bigint, tmuint64) RETURNS bool
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR < (
leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint32, rightarg = int8, procedure = tmuint32_int64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = int8, rightarg = tmuint32, procedure = int64_tmuint32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint32, rightarg = int8, procedure = tmuint32_int64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = int8, rightarg = tmuint32, procedure = int64_tmuint32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint32, rightarg = int8, procedure = tmuint32_int64_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = int8, rightarg = tmuint32, procedure = int64_tmuint32_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_abs_eq,
commutator = = ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint32, rightarg = int8, procedure = tmuint32_int64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = int8, rightarg = tmuint32, procedure = int64_tmuint32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint32, rightarg = int2, procedure = tmuint32_int16_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint32, rightarg = int4, procedure = tmuint32_int32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint32, rightarg = int8, procedure = tmuint32_int64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = int2, rightarg = tmuint32, procedure = int16_tmuint32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = int4, rightarg = tmuint32, procedure = int32_tmuint32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = int8, rightarg = tmuint32, procedure = int64_tmuint32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint32, rightarg = tmuint32, procedure = tmuint32_tmuint32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint64, rightarg = tmuint64, procedure = tmuint64_tmuint64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint64, rightarg = smallint, procedure = tmuint64_int16_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = smallint, rightarg = tmuint64, procedure = int16_tmuint64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint64, rightarg = integer, procedure = tmuint64_int32_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = integer, rightarg = tmuint64, procedure = int32_tmuint64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = tmuint64, rightarg = bigint, procedure = tmuint64_int64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR < (
leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_abs_lt,
commutator = > , negator = >= ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR <= (
leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_abs_le,
commutator = >= , negator = > ,
restrict = scalarltsel, join = scalarltjoinsel
);
CREATE OPERATOR = (
leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_abs_eq,
commutator = = ,
-- leave out negator since we didn't create <> operator
-- negator = <> ,
restrict = eqsel, join = eqjoinsel
);
CREATE OPERATOR >= (
leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_abs_ge,
commutator = <= , negator = < ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE OPERATOR > (
leftarg = bigint, rightarg = tmuint64, procedure = int64_tmuint64_abs_gt,
commutator = < , negator = <= ,
restrict = scalargtsel, join = scalargtjoinsel
);
CREATE FUNCTION tmuint32_int16_abs_cmp(tmuint32, int2) RETURNS int4
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_int32_abs_cmp(tmuint32, int4) RETURNS int4
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint32_abs_cmp(int2, tmuint32) RETURNS int4
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint32_abs_cmp(int4, tmuint32) RETURNS int4
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint32_tmuint32_abs_cmp(tmuint32, tmuint32) RETURNS int4
AS '/my/install/dir/tmuint32' LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR CLASS tmuint32_tmuint32_abs_ops
DEFAULT FOR TYPE tmuint32 USING btree AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 tmuint32_tmuint32_abs_cmp(tmuint32, tmuint32);
CREATE FUNCTION tmuint64_tmuint64_abs_cmp(tmuint64, tmuint64) RETURNS int4
AS '/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int16_abs_cmp(tmuint64, smallint) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int32_abs_cmp(tmuint64, integer) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION tmuint64_int64_abs_cmp(tmuint64, bigint) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int16_tmuint64_abs_cmp(smallint, tmuint64) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int32_tmuint64_abs_cmp(integer, tmuint64) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION int64_tmuint64_abs_cmp(bigint, tmuint64) RETURNS int4 AS
'/my/install/dir/tmuint64' LANGUAGE C IMMUTABLE STRICT;
CREATE OPERATOR CLASS tmuint64_tmuint64_abs_ops DEFAULT FOR TYPE tmuint64 USING btree AS
OPERATOR 1 < ,
OPERATOR 2 <= ,
OPERATOR 3 = ,
OPERATOR 4 >= ,
OPERATOR 5 > ,
FUNCTION 1 tmuint64_tmuint64_abs_cmp(tmuint64, tmuint64);
END;
ENDOFSQLDATA
unset PGPASSWORD
Rodrigo Barboza <rodrigombufrj@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-numericoperand. */elog(ERROR, "unsupported type: %u", typid);
regards, tom lane
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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?
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:> 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 laneYes, 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;
}
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);
}
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
On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:> 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 laneYes, 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 QUERYselect * from a;NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191a---0123(4 rows)_________________________________________________________________-- QUERY WHITH FILTERselect * from a where a > 1;NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1ERROR: 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);
}
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);
}
On Mon, Apr 8, 2013 at 12:14 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:> 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 laneYes, 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 QUERYselect * from a;NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191a---0123(4 rows)_________________________________________________________________-- QUERY WHITH FILTERselect * from a where a > 1;NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1ERROR: unsupported type: 220200I 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
On Mon, Apr 8, 2013 at 4:30 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:
On Mon, Apr 8, 2013 at 12:14 PM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:44 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:27 AM, Rodrigo Barboza <rodrigombufrj@gmail.com> wrote:On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:> 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 laneYes, 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 QUERYselect * from a;NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191NOTICE: funcao:tmuint32_out linha:191a---0123(4 rows)_________________________________________________________________-- QUERY WHITH FILTERselect * from a where a > 1;NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2NOTICE: funcao:tmuint32_int32_abs_gt linha:1296NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1ERROR: unsupported type: 220200I 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/shexport 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++));dopsql -U testuser testdb <<ENDOFSQLDATAinsert into tm64 values($i);ENDOFSQLDATAdonefor ((i=0; i<100; i++ )); dopsql -U testuser testdb <<ENDOFSQLDATABEGIN;UPDATE tm64 SET a = a + 1 WHERE a > $i;END;ENDOFSQLDATAdone
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;
}
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.
On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit.kapila@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 <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.The reason for seldom behavior is that, it occurs only when the value you
>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.
are giving in your where clause lies in valid boundary of histogram (refer
function ineq_histogram_selectivity).You have identified rightly in your other mail that it happens in function
> 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
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?
On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote: On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit.kapila@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.
On Wed, Apr 10, 2013 at 12:24 AM, Amit Kapila <amit.kapila@huawei.com> wrote:
On Tuesday, April 09, 2013 6:19 PM Rodrigo Barboza wrote:
On Tue, Apr 9, 2013 at 3:05 AM, Amit Kapila <amit.kapila@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.You need to write C function and use them while creating operator's, Refer
> 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?
link:
http://www.postgresql.org/docs/9.2/static/xfunc-c.htmlOption 3 means you can use prepared statements to specify bind values for
> I didn't understand option 3, what did you mean?
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?