Re: BigInt woes

Joshua D. Drake wrote:

> 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';

Without that million rows, my 7.3.4 uses a RESULT plan with a subselect 
of an AGG plan using an INDEX scan ... I guess that's not really 
PL/pgSQL related but more an SPI/param/optimizer issue. The optimizer 
get's different ideas about the selectivity of $n parameters vs. 
constant values, and the in_bid_id variable in that statement get's 
replaced by a $n parameter for preparing an SPI plan.


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #