Hello,
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 - jd@commandprompt.com - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org