BigInt woes

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(at)commandprompt(dot)com - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org