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

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)?

With Regards,
Amit Kapila.

Well, it's a lot of data.
May I send it atached?

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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:

Show quoted text

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.

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.

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

The error message:
ERROR: unsupported type: 202886
ROLLBACK

Here 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-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

On Mon, Apr 8, 2013 at 11:25 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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-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:

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-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

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:

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-numeric operand.
*/
elog(ERROR, "unsupported type: %u", typid);

regards, tom lane

Yes, I found it in the code, but I followed the example from the postgres
documentation that uses this function.
And why does it work sometimes? Why not other times?

Here is a very simple case and weird behavior. I select * from a table and
returns 4 entries.
But when I run with a filter the error raises and crazy values are printed
from the params.

Here is my funcitons where I compare the values:

typedef uint32_t TmUInt32;

static int
tmuint32_int32_abs_cmp_internal(TmUInt32 a, int32_t b)
{
int ret;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
if (a < b) ret = -1;
else if (a > b) ret = 1;
else ret = 0;
elog(NOTICE, "funcao:%s linha:%d, ret: %d a: %u\n", *_FUNCTION_*, *_LINE_*,
ret, a);
return ret;
}

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*,
*_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

And here is the simple test.

-- SIMPLE QUERY
select * from a;

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

a
---
0
1
2
3
(4 rows)

_________________________________________________________________

-- QUERY WHITH FILTER
select * from a where a > 1;

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1

ERROR: unsupported type: 220200

I found that the problem is in the highlithed line. I'm getting the wrong
value from param1. But why this behavior?

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*, *
_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

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:

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-numeric operand.
*/
elog(ERROR, "unsupported type: %u", typid);

regards, tom lane

Yes, I found it in the code, but I followed the example from the
postgres documentation that uses this function.
And why does it work sometimes? Why not other times?

Here is a very simple case and weird behavior. I select * from a table
and returns 4 entries.
But when I run with a filter the error raises and crazy values are
printed from the params.

Here is my funcitons where I compare the values:

typedef uint32_t TmUInt32;

static int
tmuint32_int32_abs_cmp_internal(TmUInt32 a, int32_t b)
{
int ret;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
if (a < b) ret = -1;
else if (a > b) ret = 1;
else ret = 0;
elog(NOTICE, "funcao:%s linha:%d, ret: %d a: %u\n", *_FUNCTION_*, *_LINE_
*, ret, a);
return ret;
}

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*,
*_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

And here is the simple test.

-- SIMPLE QUERY
select * from a;

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

a
---
0
1
2
3
(4 rows)

_________________________________________________________________

-- QUERY WHITH FILTER
select * from a where a > 1;

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1

ERROR: unsupported type: 220200

I found that the problem is in the highlithed line. I'm getting the wrong
value from param1. But why this behavior?

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*,
*_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

Wow, this simple test reproduces the problem.
After running this script a few times, this simple query fails:
select * from tm32 where a > 1;
What is weird is when I remove the commutator from the operator '>', there
is no error. But the crazy values are still there, like it was never
removed with the drop database.

#!/bin/sh
export PGPASSWORD=mypass;

psql -U testuser testdb -c "truncade tm64;"
psql -U testuser testdb -c "create table tm64 (a tmuint64);"

for ((i=1; i<100; i++));do
psql -U testuser testdb <<ENDOFSQLDATA
insert into tm64 values($i);
ENDOFSQLDATA
done

for ((i=0; i<100; i++ )); do
psql -U testuser testdb <<ENDOFSQLDATA
BEGIN;
UPDATE tm64 SET a = a + 1 WHERE a > $i;
END;
ENDOFSQLDATA
done

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:

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-numeric operand.
*/
elog(ERROR, "unsupported type: %u", typid);

regards, tom lane

Yes, I found it in the code, but I followed the example from the
postgres documentation that uses this function.
And why does it work sometimes? Why not other times?

Here is a very simple case and weird behavior. I select * from a table
and returns 4 entries.
But when I run with a filter the error raises and crazy values are
printed from the params.

Here is my funcitons where I compare the values:

typedef uint32_t TmUInt32;

static int
tmuint32_int32_abs_cmp_internal(TmUInt32 a, int32_t b)
{
int ret;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);
if (a < b) ret = -1;
else if (a > b) ret = 1;
else ret = 0;
elog(NOTICE, "funcao:%s linha:%d, ret: %d a: %u\n", *_FUNCTION_*, *
_LINE_*, ret, a);
return ret;
}

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*,
*_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

And here is the simple test.

-- SIMPLE QUERY
select * from a;

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

NOTICE: funcao:tmuint32_out linha:191

a
---
0
1
2
3
(4 rows)

_________________________________________________________________

-- QUERY WHITH FILTER
select * from a where a > 1;

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 0, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: -1 a: 0

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 99, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 99

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 50, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 50

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 24, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 24

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 12, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 12

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 6, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 6

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 2, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 1 a: 2

NOTICE: funcao:tmuint32_int32_abs_gt linha:1296

NOTICE: funcao:tmuint32_int32_abs_gt linha:1303 param1: 1, param2: 1

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:742

NOTICE: funcao:tmuint32_int32_abs_cmp_internal linha:746, ret: 0 a: 1

ERROR: unsupported type: 220200

I found that the problem is in the highlithed line. I'm getting the wrong
value from param1. But why this behavior?

PG_FUNCTION_INFO_V1(tmuint32_int32_abs_gt);

Datum
tmuint32_int32_abs_gt(PG_FUNCTION_ARGS)
{
TmUInt32 *param1;
int32_t param2;
elog(NOTICE, "funcao:%s linha:%d\n", *_FUNCTION_*, *_LINE_*);

if(PG_ARGISNULL(0) || PG_ARGISNULL(1)) PG_RETURN_NULL();

param1 = (TmUInt32 *) PG_GETARG_POINTER(0);
param2 = DatumGetInt32(PG_GETARG_DATUM(1));

elog(NOTICE, "funcao:%s linha:%d param1: %u, param2: %d\n", *_FUNCTION_*
, *_LINE_*, *param1, param2);
PG_RETURN_BOOL(tmuint32_int32_abs_cmp_internal(*param1, param2) > 0);
}

Wow, this simple test reproduces the problem.
After running this script a few times, this simple query fails:
select * from tm32 where a > 1;
What is weird is when I remove the commutator from the operator '>', there
is no error. But the crazy values are still there, like it was never
removed with the drop database.

#!/bin/sh
export PGPASSWORD=mypass;

psql -U testuser testdb -c "truncade tm64;"
psql -U testuser testdb -c "create table tm64 (a tmuint64);"

for ((i=1; i<100; i++));do
psql -U testuser testdb <<ENDOFSQLDATA
insert into tm64 values($i);
ENDOFSQLDATA
done

for ((i=0; i<100; i++ )); do
psql -U testuser testdb <<ENDOFSQLDATA
BEGIN;
UPDATE tm64 SET a = a + 1 WHERE a > $i;
END;
ENDOFSQLDATA
done

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

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

DatumGetFloat8(DirectFunctionCall1(numeric_float8_no_overflow,

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

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

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.
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.

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.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

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?