Re: [HACKERS] a problem with index and user define type
Tom Lane wrote: Wang Mike [EMAIL PROTECTED] writes: but this query: select * from test_uuid where id = 'df2b10aa-a31d-11d7-9867-0050babb6029'::uuid dosn't use index QUERY PLAN --- Seq Scan on test_uuid (cost=0.00..22.50 rows=500 width=140) Filter: (id = 'df2b10aa-a31d-11d7-9867-0050babb6029'::uuid) why ?? The rows estimate looks pretty fishy --- I think you are getting the 0.5 default selectivity estimate for an operator that has no restriction estimator. Most likely you should have created the operator using eqsel and eqjoinsel as the restriction/join estimators. regards, tom lane Hi, Tom, I'm trying to test it, but don't know if I understood you correctly, you mean we should try to create the operator using eqsel/eqjoinsel estimators, right? But after we added those estimators like this: CREATE OPERATOR = ( LEFTARG = uuid, RIGHTARG = uuid, COMMUTATOR = =, NEGATOR = , PROCEDURE = uuid_eq, RESTRICT = eqsel, JOIN = eqjoinsel ); the situation trun worse: now the explain shows the query using the index, the we can't select out the match row! Any hint about what's wrong with us? Thanks and Reagards Laser ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] a problem with index and user define type
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. Thank you! Regards Laser Tom Lane wrote: Weiping He [EMAIL PROTECTED] writes: the situation trun worse: now the explain shows the query using the index, the we can't select out the match row! Any hint about what's wrong with us? My bet: either your operators are broken or your operator class definition is wrong. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] a problem with index and user define type
Tom Lane wrote:
Weiping He [EMAIL PROTECTED] writes:
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
Sorry for didn't describe my problem clearly. I mean the function implement
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]
Re: [HACKERS] a problem with index and user define type
Tom Lane wrote: Weiping He [EMAIL PROTECTED] writes: 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, No, it will not. Your claim above is entirely wrong; the fact that the datatype is pass-by-reference doesn't affect anything (unless you've failed to declare the datatype that way, but if so I'd not think it would work at all). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] yeah, you are right, it's out fault. We've mistakenly use PG_RETURN_INT16() to return from our am support function, which prune the sign information from the memcmp(), but we still declare the function to return INTEGER when CREATE FUNCTION. So the error, it's fixed now, and the datatype and index run smoothly. Thanks and Regards Laser ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] cvs version compile error
Hi, I'm trying to compile pgsql-7.4devl on Solaris8, but got the error below: 8- numeric.c: In function `PGTYPESnumeric_cmp': numeric.c:1308: `INT_MAX' undeclared (first use in this function) numeric.c:1308: (Each undeclared identifier is reported only once numeric.c:1308: for each function it appears in.) numeric.c:1310: warning: control reaches end of non-void function numeric.c: In function `PGTYPESnumeric_to_int': numeric.c:1452: `INT_MAX' undeclared (first use in this function) numeric.c: In function `PGTYPESnumeric_to_long': numeric.c:1474: `LONG_MAX' undeclared (first use in this function) make[4]: *** [numeric.o] Error 1 make[4]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces/ecpg/pgtypeslib' make[3]: *** [all] Error 2 make[3]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces/ecpg' make[2]: *** [all] Error 2 make[2]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src/interfaces' make[1]: *** [all] Error 2 make[1]: Leaving directory `/export/home/postdb/pgsql-7.4/pgsql/src' make: *** [all] Error 2 $ gcc --version 2.95.3 --8- the source file are just updated from CVS. ISTM a little bug on Solaris8 platform. Thanks and Regards Laser ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] cvs version compile error
Tom Lane wrote: I think Bruce already fixed this. How old is your CVS pull? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings upgraded this morning, around 2003-07-04 09:29:00 CST or 2003-07-03 17:29:00 PST. and later I add a #include limits.h to src/interfaces/ecpg/pgtypeslib/numeric.c fix it temporary, don't know if it's correct, but make check all passed. Will try newer cvs tip later. Thank you laser ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] configure error in HP-UX 11.00
When trying to build the CVS source on a HP-UX 11.00 box: HP-UX hpux B.11.00 A 9000/800 We got the configure error: error checking types of arguments for accept()... configure: error: could not determine argument types /error Check the config.log, it said that the configure script can't find the argument types fo 'accept()'. but in /usr/include/sys/socket.h, there is prototype of accept: codesnip #if defined(_XOPEN_SOURCE_EXTENDED) !defined(_KERNEL_BUILD) extern int accept __((int, struct sockaddr *, socklen_t *)); ... #else /* !_XOPEN_SOURCE_EXTENDED*/ extern int accept __((int, void *, int *)); /codesnip so what's wrong with the OS? Thank you Laser ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] configure error in HP-UX 11.00
Tom Lane wrote: Weiping He [EMAIL PROTECTED] writes: Check the config.log, it said that the configure script can't find the argument types fo 'accept()'. Could you show us the relevant section of config.log, instead of giving a summary with no details? I'd like to see the compiler error messages and failed test progams. I've put the config.log on: http://www.pgsqldb.org/config.log in case the list filt out my last message with attachement. Thank you Laser ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] configure error in HP-UX 11.00
Tom Lane wrote: Weiping He [EMAIL PROTECTED] writes: I've put the config.log on: http://www.pgsqldb.org/config.log configure:10847: cc -Ae -c +O2 -D_XOPEN_SOURCE_EXTENDED conftest.c 5 (Bundled) cc: warning 480: The -A option is available only with the C/ANSI C product; ignored. Get a real compiler :-( got it. done! Thank you Laser ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
