I believe that the Int8/BigInt items are known issues but I have a knew programmer that ran into it
over the weekend (he didn't call me when he encountered the problem, when he should of) and we have a
customer that burned some significant time on it as well. Will this be fixed in 7.4?
Here is a test case a customer sent me:
Suppose you have a table:
create table bid ( bid_id bigint not null, bid_time timestamp, constraint bid_pk primary key (bid_id));
Populate it with a million rows or so.
This query:
explain select bid_id, bid_time from bid where bid_id = 10000
Will always sequential scan.
This query:
explain select bid_id, bid_time from bid where bid_id = '10000'
Will use the index.
Where this really gets to be a pain in the butt is with a UDF in plpgsql... this UDF will only sequential scan:
create function bid_check(bigint) returns bool as ' declare in_bid_id alias for $1; begin
if (select count(*) from bid where bid_id = in_bid_id) = 1 then return true; else return false; end if; end; ' language 'plpgsql';
The work around is to build the SQL statement in a string, embedding the value of the variable with the quote_literal function and execute it.
-- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC - S/JDBC Postgresql support, programming, shared hosting and dedicated hosting. +1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org
---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
