Thread: FUNCTIONs and CASTs

FUNCTIONs and CASTs

From
"Dean Gibson (DB Administrator)"
Date:
Last night I tore my hair out for about three hours with the following problem (v8.3.0):<br /><br /> I had a simple
scalarquery that I wanted to create a function for.  However, when I created the function and called it from another
queryOR the PSQL command line, it was two orders of magnitude SLOWER than when I manually substituted the parameters
intothe query and ran it directly.  Then I figured out what the cause was:<br /><br /> The manual query was doing an
indexedcolumn lookup on the value, a simple text string.  However, when the function was passed the text string as the
value,it was comparing the TEXT type of the parameter to the CHAR( n) type of the indexed column, and apparently
rewritingthe comparison using a cast of the indexed column.  Needless to say, the does not result in an indexed access
(probablythe index is searched sequentially for a match).<br /><br /> I solved the problem by explicitly casting the
functionparameter to the type of the index, and that solved the problem.<br /><br /> So, is this the best (or only) way
tosolve this?  I haven't done exhaustive checking, but it appears that specifying the type of parameters in the
functionprototype is only used for type-checking (and function matching), and no conversion between types is done. 
Giventhat, I'm not sure of the value of the <tt class="LITERAL"><tt class="REPLACEABLE"><i>tablename</i></tt>.<tt
class="REPLACEABLE"><i>columnname</i></tt>%TYPE</tt>notation, especially since apparently it can only be used in the
functionprototype and not in the body of the function.<br /><br /> If I am wrong on any of the above, I would be
pleasedto know it.<br /><br /> -- Dean<br /><pre class="moz-signature" cols="72">-- 
 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>

Re: FUNCTIONs and CASTs

From
Tom Lane
Date:
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> So, is this the best (or only) way to solve this?  I haven't done 
> exhaustive checking, but it appears that specifying the type of 
> parameters in the function prototype is only used for type-checking (and 
> function matching), and no conversion between types is done.

It's not exactly clear what you checked, but it works as expected for
me.  See test case below, proving that indexscan works just fine with
a parameter declared using %type.
        regards, tom lane


regression=# create table tt(f1 char(10) unique);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "tt_f1_key" for table "tt"
CREATE TABLE
regression=# insert into tt select x from generate_series(1,100000) x;
INSERT 0 100000
regression=# \timing
Timing is on.
regression=# select * from tt where f1 = '12345';    f1     
------------12345     
(1 row)

Time: 47.589 ms
regression=# set enable_indexscan TO 0;
SET
Time: 3.146 ms
regression=# set enable_bitmapscan TO 0;
SET
Time: 1.583 ms
regression=# select * from tt where f1 = '12345';    f1     
------------12345     
(1 row)

Time: 414.585 ms
regression=# select * from tt where f1 = '12345';    f1     
------------12345     
(1 row)

Time: 412.167 ms
regression=# reset enable_indexscan;
RESET
Time: 3.037 ms
regression=# select * from tt where f1 = '12345';    f1     
------------12345     
(1 row)

Time: 4.019 ms
regression=# create function foo (tt.f1%type) returns char(10) as $$
declare r tt.f1%type;
begin  select f1 into r from tt where f1 = $1; return r;
end$$ language plpgsql;
NOTICE:  type reference tt.f1%TYPE converted to character
CREATE FUNCTION
Time: 8.193 ms
regression=# \df foo                  List of functionsSchema | Name | Result data type | Argument data types 
--------+------+------------------+---------------------public | foo  | character        | character
(1 row)

regression=# select foo('12345'::text);   foo     
------------12345     
(1 row)

Time: 21.683 ms
regression=# select foo('12345'::text);   foo     
------------12345     
(1 row)

Time: 4.098 ms
regression=#  


Re: FUNCTIONs and CASTs

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-02-14 15:19, Tom Lane wrote:
> It's not exactly clear what you checked, but it works as expected for me.  See test case below, proving that
indexscanworks just fine with a parameter declared using %type.
 
>
>             regards, tom lane
>   

Consider:

CREATE TABLE zzz( aaa CHAR( 10 ) );

CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE   LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS
zzz.aaa%TYPE)= 
 
aaa';

The notation "zzz.aaa%TYPE" fails when specified in the cast;  it's fine 
in the function prototype.  However, specifying it in the function 
prototype doesn't appear to help the performance issue:

Here is the actual function that caused be heartburn.  The types in the 
function prototype match EXACTLY the types of the actual parameters 
being passed (and I also tried it with the tablename.columnname%TYPE 
notation), and yet this function is slow.  However, if I replace the 
"$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function 
is very fast.  Note that ALL of the column names in the function below 
are indexed, so this function should be very fast (and is, with the CASTs).

CREATE OR REPLACE FUNCTION      "Functions".prior_call( CHAR( 10 ), 
CHAR( 9 ), DATE)  RETURNS BOOLEAN   STABLE  RETURNS NULL ON NULL INPUT  LANGUAGE SQL AS $SQL$       SELECT  COALESCE(
   (SELECT TRUE                                   FROM                lic_hd
NATURALJOIN    lic_en                                       NATURAL JOIN    lic_am
WHERE       $1 = licensee_id                                     AND       $2                      
 
IN( callsign, prev_callsign )                                     AND       $3                      > grant_date
                          LIMIT 1),                               (SELECT TRUE                                   FROM
            _preuls                                   WHERE        $1 = licensee_id
AND      $2                      
 
IN( callsign, prev_callsign )                                   LIMIT 1),                               FALSE )
$SQL$;

So, I think you can see why it would be nice if the 
tablename.columnname%TYPE notation could be used in the function body.

I'm not asking for that as an enhancement; rather, I'm trying to 
understand what the tablename.columnname%TYPE notation accomplishes, 
since specifying it in the function prototype doesn't appear to 
accomplish anything (at least for me) over just specifying "TEXT".


Re: FUNCTIONs and CASTs

From
Richard Huxton
Date:
Dean Gibson (DB Administrator) wrote:
> On 2008-02-14 15:19, Tom Lane wrote:
>> It's not exactly clear what you checked, but it works as expected for 
>> me.  See test case below, proving that indexscan works just fine with 
>> a parameter declared using %type.
> 
> Consider:
> 
> CREATE TABLE zzz( aaa CHAR( 10 ) );
> 
> CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE
>    LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) = 
> aaa';
> 
> The notation "zzz.aaa%TYPE" fails when specified in the cast;  it's fine 
> in the function prototype.  However, specifying it in the function 
> prototype doesn't appear to help the performance issue:

I get the same result: "works here".

richardh=> CREATE TABLE zzz( aaa CHAR( 10 ) );
CREATE TABLE

richardh=> INSERT INTO zzz SELECT generate_series(1,100000)::text;
INSERT 0 100000

richardh=> CREATE INDEX zzz_aaa_idx ON zzz (aaa);
CREATE INDEX

richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS 
zzz.aaa%TYPE
richardh->    LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$;
NOTICE:  type reference zzz.aaa%TYPE converted to character
NOTICE:  type reference zzz.aaa%TYPE converted to character
CREATE FUNCTION
Time: 15.268 ms
richardh=> SELECT dummy2('99999');   dummy2
------------ 99999
(1 row)

Time: 1.962 ms
richardh=> DROP INDEX zzz_aaa_idx;
DROP INDEX

richardh=> SELECT dummy2('99999');   dummy2
------------ 99999
(1 row)

Time: 45.418 ms


What does this do on your machine?

> Here is the actual function that caused be heartburn.  The types in the 
> function prototype match EXACTLY the types of the actual parameters 
> being passed (and I also tried it with the tablename.columnname%TYPE 
> notation), and yet this function is slow.  However, if I replace the 
> "$1" in the function body with "CAST( $1 AS CHAR( 10 ) )", the function 
> is very fast.  Note that ALL of the column names in the function below 
> are indexed, so this function should be very fast (and is, with the CASTs).

Hang on though - this function isn't using %TYPE, it's using explicit 
type definitions. If this function is slow, how can it be anything do 
with %TYPE ?

> CREATE OR REPLACE FUNCTION      "Functions".prior_call( CHAR( 10 ), 
> CHAR( 9 ), DATE)  RETURNS BOOLEAN
>    STABLE  RETURNS NULL ON NULL INPUT  LANGUAGE SQL AS $SQL$
>        SELECT  COALESCE(       (SELECT TRUE
>                                    FROM                lic_hd
>                                        NATURAL JOIN    lic_en
>                                        NATURAL JOIN    lic_am
>                                    WHERE        $1 = licensee_id
>                                      AND       $2                      
> IN( callsign, prev_callsign )
>                                      AND       $3                      > 
> grant_date
>                                    LIMIT 1),
>                                (SELECT TRUE
>                                    FROM                _preuls
>                                    WHERE        $1 = licensee_id
>                                      AND       $2                      
> IN( callsign, prev_callsign )
>                                    LIMIT 1),
>                                FALSE )
>    $SQL$;
> 
> So, I think you can see why it would be nice if the 
> tablename.columnname%TYPE notation could be used in the function body.

Shouldn't be necessary (see above).

> I'm not asking for that as an enhancement; rather, I'm trying to 
> understand what the tablename.columnname%TYPE notation accomplishes, 
> since specifying it in the function prototype doesn't appear to 
> accomplish anything (at least for me) over just specifying "TEXT".

It specifies the type of the variable (or parameter) in question. The 
reason you can't use %TYPE directly in your SQL is because afaik it's 
not SQL - it's a PostgreSQL extension designed to specify variable types 
in functions. SQL constructs tend to expect a literal type name.

I'm not sure what your problem is, but it's not the %TYPE operator, 
that's clear.

Can you try reproducing the function as a prepared query? That way you 
can run EXPLAIN ANALYSE on it and see what's actually happening here.

--   Richard Huxton  Archonet Ltd


Re: FUNCTIONs and CASTs

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-02-15 01:38, Richard Huxton wrote:
> Dean Gibson (DB Administrator) wrote:
>> On 2008-02-14 15:19, Tom Lane wrote:
>>> It's not exactly clear what you checked, but it works as expected 
>>> for me.  See test case below, proving that indexscan works just fine 
>>> with a parameter declared using %type.
>>
>> Consider:
>>
>> CREATE TABLE zzz( aaa CHAR( 10 ) );
>>
>> CREATE FUNCTION dummy( zzz.aaa%TYPE ) RETURNS zzz.aaa%TYPE
>>    LANGUAGE SQL AS 'SELECT * FROM zzz WHERE CAST( $1 AS zzz.aaa%TYPE) 
>> = aaa';
>>
>> The notation "zzz.aaa%TYPE" fails when specified in the cast;  it's 
>> fine in the function prototype.  However, specifying it in the 
>> function prototype doesn't appear to help the performance issue:
>
> I get the same result: "works here".
>
>
>
> richardh=> CREATE OR REPLACE FUNCTION dummy2( zzz.aaa%TYPE ) RETURNS 
> zzz.aaa%TYPE
> richardh->    LANGUAGE SQL AS $$SELECT * FROM zzz WHERE aaa = $1 $$;
> NOTICE:  type reference zzz.aaa%TYPE converted to character
> NOTICE:  type reference zzz.aaa%TYPE converted to character
You REMOVED the CAST from the function definition.  Yes, if you do that, 
it works !!!


> Here is the actual function that caused be heartburn.  The types in 
> the function prototype match EXACTLY the types of the actual 
> parameters being passed (and I also tried it with the 
> tablename.columnname%TYPE notation), and yet this function is slow.  
> However, if I replace the "$1" in the function body with "CAST( $1 AS 
> CHAR( 10 ) )", the function is very fast.  Note that ALL of the column 
> names in the function below are indexed, so this function should be 
> very fast (and is, with the CASTs).
>
> Hang on though - this function isn't using %TYPE, it's using explicit 
> type definitions. If this function is slow, how can it be anything do 
> with %TYPE ?

Again, you are not understanding my point.  My point was that specifying 
tablename.columnname%TYPE notation doesn't help with the performance 
problem;  I have to explicitly cast the parameter in the body of the 
function.  Since I have to do that anyway, why use the 
tablename.columnname%TYPE notation?


>
>
>> I'm not asking for that as an enhancement; rather, I'm trying to 
>> understand what the tablename.columnname%TYPE notation accomplishes, 
>> since specifying it in the function prototype doesn't appear to 
>> accomplish anything (at least for me) over just specifying "TEXT".
>
> It specifies the type of the variable (or parameter) in question.
So?  What does that accomplish, over just using "TEXT"?

> The reason you can't use %TYPE directly in your SQL is because afaik 
> it's not SQL - it's a PostgreSQL extension designed to specify 
> variable types in functions. SQL constructs tend to expect a literal 
> type name.
>
> I'm not sure what your problem is, but it's not the %TYPE operator, 
> that's clear.

As I said, I don't have a problem with the function;  I modified it to 
work.  My point was, why use the tablename.columnname%TYPE notation when 
"TEXT" works just as well (for anything that converts to it)???


-- 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.



Re: FUNCTIONs and CASTs

From
Tom Lane
Date:
"Dean Gibson (DB Administrator)" <postgresql@ultimeth.com> writes:
> Again, you are not understanding my point.  My point was that specifying 
> tablename.columnname%TYPE notation doesn't help with the performance 
> problem;  I have to explicitly cast the parameter in the body of the 
> function.

The reason for the lack of communication is that no one else believes
that premise.  Casting a value to the same type it already has is
demonstrably a no-op.
        regards, tom lane


Re: FUNCTIONs and CASTs

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-02-15 14:32, Tom Lane wrote: <blockquote cite="mid:655.1203114745@sss.pgh.pa.us" type="cite"><pre
wrap="">"DeanGibson (DB Administrator)" <a class="moz-txt-link-rfc2396E"
href="mailto:postgresql@ultimeth.com"><postgresql@ultimeth.com></a>writes: </pre><blockquote type="cite"><pre
wrap="">Again,you are not understanding my point.  My point was that specifying 
 
tablename.columnname%TYPE notation doesn't help with the performance 
problem;  I have to explicitly cast the parameter in the body of the 
function.   </pre></blockquote><pre wrap="">
The reason for the lack of communication is that no one else believes
that premise.  Casting a value to the same type it already has is
demonstrably a no-op. </pre></blockquote> Casing a TEXT item to a CHAR( 9 ) item isn't a no-op.  I've seen this before
in"EXPLAIN ..." output, where a search on an indexed column will be sequential because the planner treats the search
valueas TEXT rather than CHAR( 9 ).<br /><br /> Are you saying that no one believes there is a performance difference? 
Amazing...<br /><br /> Tom, I've privately eMailed you access instructions to one of my DB servers, so you can see for
yourself.<br/><br /><pre class="moz-signature" cols="72">-- 
 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>

Re: FUNCTIONs and CASTs

From
"Dean Gibson (DB Administrator)"
Date:
On 2008-02-15 15:03, Dean Gibson (DB Administrator) wrote: <blockquote cite="mid:47B61A26.4060806@ultimeth.com"
type="cite"></blockquote>On 2008-02-15 14:32, Tom Lane wrote:<br /><br /> Casing a TEXT item to a CHAR( 9 ) item isn't
ano-op.  I've seen this before in "EXPLAIN ..." output, where a search on an indexed column will be sequential because
theplanner treats the search value as TEXT rather than CHAR( 9 ).<br /><br /> Are you saying that no one believes there
isa performance difference?  Amazing ...<br /><br /><strike>Tom, I've privately eMailed you access instructions to one
ofmy DB servers, so you can see for yourself.</strike><br /><br /><br /> OK, it must have been late at 2am when I last
ranthe tests, as it now seems to work.  By "work", I mean that the casting in the function body is (in the particular
caseI was having an issue with) apparently unnecessary if the types are proper (which includes the table.column%TYPE
notation).<br/><br /> I'm happy to find that out, since now I can use the table.column%TYPE notation to advantage.<br
/><br/> What helped confuse me is that the following function apparently DOES need an internal cast:<br /><br /> CREATE
ORREPLACE FUNCTION zzz( aaa CHAR(1) ) RETURNS CHAR(1) LANGUAGE SQL AS 'SELECT $1';<br /><br /> SELECT zzz( 'abc' );<br
/><br/> returns "abc", not "a".  Apparently declarations of CHAR(n) are treated as BPCHAR in function prototypes???<br
/><br/> -- Dean<br /><pre class="moz-signature" cols="72">-- 
 
Mail to my list address MUST be sent via the mailing list.
All other mail to my list address will bounce.</pre>