Weiping He <[EMAIL PROTECTED]> writes:Sorry for didn't describe my problem clearly. I mean the function implement
we found the problem:
We used IMMUTABLE modifier in our CREATE FUNCTION definition,
though it's correct for our function to return same value if input the same *data*,
but our data are passed by reference, not by value, so, some times we can't
retrive out data. Remove IMMUTABLE fixed the problem.
So, it seems to make it clear in docs would be a good help to function writers, would commit a documentation patch later if necessary.
I'm not sure what problem you're really describing, but it would be entirely wrong for the docs to claim that pass-by-reference datatypes shouldn't have immutable functions. float8 is pass-by-ref, for instance, but they don't come any more immutable than sqrt(x) ...
I'd suggest taking a closer look to understand what the problem really is. Trying to index on a non-immutable function makes no sense, which is why the system forbids it.
regards, tom lane
the operator, like compare function for equal ('=') etc, not to build an index
on an function.
Here is full version:
First we build a user type using CREATE FUNCTION, CREATE TYPE, CREATE OPERATOR
and CREATE OPERATOR CLASS command, of course we wrote those C functions
needed for operator, type etc.
Then we try to test if our type (which is named UUID) could be indexable, and found
it didn't use the index, but, we don't know why.
Later, we ask the question here why the index didn't get used, and you point out that we
should assign the selective restriction function for our operators, espically for '=' operator,
we use 'eqsel' per your suggestion. But found out that though the idnex got used, but sometimes
not data row return (and sometimes we could get the data row)!
Then we re-check our definition, and found out may be we shouldn't use IMMUTABLE
key word in the function definition used by the '=' operator to implement the equation compare,
the wrong definition is:
Datum uuid_eq(PG_FUNCTION_ARGS)
{
struct uuid *uptr1 = (struct uuid *) PG_GETARG_POINTER(0);
struct uuid *uptr2 = (struct uuid *) PG_GETARG_POINTER(1);PG_RETURN_BOOL(uuidcmp(uptr1, uptr2) == 0); }
CREATE OR REPLACE FUNCTION uuid_eq(uuid, uuid) RETURNS boolean IMMUTABLE STRICT AS '$libdir/uuid' LANGUAGE 'C';
CREATE OPERATOR = ( LEFTARG = uuid, RIGHTARG = uuid, COMMUTATOR = =, NEGATOR = <>, PROCEDURE = uuid_eq, RESTRICT = eqsel, JOIN = eqjoinsel );
because the data type (UUID) is a struct,
and the uuid_eq() function accept two pointer to the value of struct uuid,
if make it IMMUTABLE, postgresql would think it should not try to run
the function, but return the cached value instead when it get two same pointers input,
but, the pointers may be unchanged, the data pointers point to may have changed.
So it will cause the weird symptom we found. And removed IMMUTABLE fix the
problem. So we think may be the doc for CREATE FUNCTION should point out
the difference of passed by ref and passed by value. Thus may avoid this kind of
error.
Thanks and Regards
Laser
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
