Обсуждение: 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@commandprompt.com - http://www.commandprompt.com
PostgreSQL.Org - Editor-N-Chief - http://www.postgresql.org

"Joshua D. Drake" <jd@commandprompt.com> writes:
> Will this be fixed in 7.4?

No.
        regards, tom lane

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?

Well, this is not an issue actually but fact that postgresql is very strict 
about it's data types. You need to cast explicitly even for those types which 
'seem' compatible, such as int4 and int2.

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

Try explain select bid_id, bid_time from bid where bid_id = 10000::bigint

> 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

Again try typecasting.

if (select count(*) from bid where bid_id::bigint = in_bid_id::bigint) = 1 then

I doubt in_bid_id needs to be casted that explicitly but I am sure it will be 
safe..:-)

HTH
 Shridhar

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 #