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;
beginif (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;
beginif (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 #