Обсуждение: Weird behavior with custom operators

Greetings,

I am using postgresql 8.4 (debian backport). In order to optimize some
of my code I decided to go with a custom data type to which I associated
operators and an operator class for indexation.

Here is the code I use :

-- 8<-------

CREATE TYPE tagvalue AS (storedvalue text);

CREATE OR REPLACE FUNCTION num_lt(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r < $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_gt(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r > $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_lte(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r <= $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_gte(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r >= $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_eq(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r = $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION num_neq(tagvalue, double precision)
RETURNS BOOLEAN AS $$
DECLARE
 r FLOAT;
 retval BOOLEAN;
BEGIN
 r := CAST(($1).storedvalue AS double precision);
 SELECT r != $2 INTO retval;
 RETURN retval;
EXCEPTION WHEN OTHERS THEN
 RETURN FALSE;
END;
$$
LANGUAGE plpgsql IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_lt(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue < $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_gt(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue > $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_lte(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue <= $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_gte(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue >= $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_eq(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue = $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION txt_neq(tagvalue, text)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue != $2;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_lt(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue < ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_gt(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue > ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_lte(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue <= ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_gte(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue >= ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_eq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue = ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_eq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue = ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OR REPLACE FUNCTION tv_neq(tagvalue, tagvalue)
RETURNS BOOLEAN AS $$
 SELECT ($1).storedvalue != ($2).storedvalue;
$$
LANGUAGE 'sql' IMMUTABLE;

CREATE OPERATOR > (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_gt,
 commutator = <,
 negator = <=,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_lt,
 commutator = >,
 negator = >=,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_gte,
 commutator = <=,
 negator = <,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_lte,
 commutator = >=,
 negator = >,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_eq,
 commutator = =,
 negator = !=,
 RESTRICT = eqsel,
 JOIN = eqjoinsel,
 HASHES,
 MERGES
);

CREATE OPERATOR != (
 LEFTARG = tagvalue,
 RIGHTARG = double precision,
 PROCEDURE = num_neq,
 commutator = !=,
 negator = =,
 RESTRICT = neqsel,
 JOIN = neqjoinsel
);

CREATE OPERATOR > (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_gt,
 commutator = <,
 negator = <=,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_lt,
 commutator = >,
 negator = >=,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_gte,
 commutator = <=,
 negator = <,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_lte,
 commutator = >=,
 negator = >,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_eq,
 commutator = =,
 negator = !=,
 RESTRICT = eqsel,
 JOIN = eqjoinsel,
 HASHES,
 MERGES
);

CREATE OPERATOR != (
 LEFTARG = tagvalue,
 RIGHTARG = text,
 PROCEDURE = txt_neq,
 commutator = !=,
 negator = =,
 RESTRICT = neqsel,
 JOIN = neqjoinsel
);

CREATE OPERATOR > (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_gt,
 commutator = <,
 negator = <=,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR < (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_lt,
 commutator = >,
 negator = >=,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR >= (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_gte,
 commutator = <=,
 negator = <,
 RESTRICT = scalargtsel,
 JOIN = scalargtjoinsel
);

CREATE OPERATOR <= (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_lte,
 commutator = >=,
 negator = >,
 RESTRICT = scalarltsel,
 JOIN = scalarltjoinsel
);

CREATE OPERATOR = (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_eq,
 commutator = =,
 negator = !=,
 RESTRICT = eqsel,
 JOIN = eqjoinsel,
 HASHES,
 MERGES
);

CREATE OPERATOR != (
 LEFTARG = tagvalue,
 RIGHTARG = tagvalue,
 PROCEDURE = tv_neq,
 commutator = !=,
 negator = =,
 RESTRICT = neqsel,
 JOIN = neqjoinsel
);

CREATE OR REPLACE FUNCTION tagvalue_cmp(tagvalue, tagvalue)
RETURNS integer AS $$
BEGIN
IF ($1).storedvalue < ($2).storedvalue THEN
 RETURN -1;
ELSEIF ($1).storedvalue > ($2).storedvalue THEN
 RETURN 1;
ELSE
 RETURN 0;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION tagvalue_hash(tagvalue)
RETURNS integer AS $$
 SELECT hashtext(($1).storedvalue);
$$ LANGUAGE 'sql' STRICT IMMUTABLE;

CREATE OPERATOR CLASS tagvalue_btree_ops
    DEFAULT FOR TYPE tagvalue USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       tagvalue_cmp(tagvalue, tagvalue);

CREATE OPERATOR CLASS tagvalue_hash
    DEFAULT FOR TYPE tagvalue USING hash AS
        OPERATOR        1       =,
        FUNCTION        1       tagvalue_hash(tagvalue);

-- 8<-------

I use this on the following table:

CREATE TABLE tags (
       id       bigint       NOT NULL,
       name     text         NOT NULL,
       value    tagvalue     NOT NULL
  );

All the operators work as expected, excepted for > and >= :

xxx=> SELECT value FROM tags WHERE value < 3 LIMIT 1;
 value
-------
 (2)
(1 row)

xxx=> SELECT value FROM tags WHERE value = 3 LIMIT 1;
 value
-------
 (3)
(1 row)

xxx=> SELECT value FROM tags WHERE value > 3 LIMIT 1;
ERROR:  unsupported type: 17886



Obviously there's got to be something wrong with these operators, but
when I use the comparison function directly, it works fine :

xxx=> SELECT value FROM tags WHERE num_gt(value, 3) LIMIT 1;
 value
-------
 (21)
(1 row)


I am at a loss of ideas about what causes this behavior. Any help
welcome !


Matthieu Huin

Matthieu HUIN <matthieu.huin@wallix.com> writes:
> xxx=> SELECT value FROM tags WHERE value > 3 LIMIT 1;
> ERROR:  unsupported type: 17886

I think you're probably hitting this:

    /*
     * 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);

While you could possibly make it work by writing wrappers around those
selectivity functions instead of using them directly, I'm kind of
wondering what is the point of this datatype anyway?  Seems like
declaring it as a domain over text might be easier.

            regards, tom lane

Hello Tom,

Thanks for the answer. I think this is exactly what's happening here.
What I cannot understand though, is why this specific error isn't thrown
when using the others operators I defined that have the same kind of
operands.

About your last question; I created this datatype so that I could store
values as text and compare them to floats easily if they can be
converted into a numeric type (since numeric order differs from
alphanumeric order). I used to call CASTs in my queries when needed, but
I realized that doing so made my various existing indexes useless. And
since I'd rather not create a specific table for numeric values, this is
what I came up with.

I have very little experience with postgresql or databases in general so
any advice on that will be gladly taken.

You are suggesting to use a domain : would I be able to extend operators
that way ?

Le jeudi 26 août 2010 à 11:05 -0400, Tom Lane a écrit :
> Matthieu HUIN <matthieu.huin@wallix.com> writes:
> > xxx=> SELECT value FROM tags WHERE value > 3 LIMIT 1;
> > ERROR:  unsupported type: 17886
>
> I think you're probably hitting this:
>
>     /*
>      * 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);
>
> While you could possibly make it work by writing wrappers around those
> selectivity functions instead of using them directly, I'm kind of
> wondering what is the point of this datatype anyway?  Seems like
> declaring it as a domain over text might be easier.
>
>             regards, tom lane