Thread: Mystery function error

Richard Sydney-Smith wrote:
>  CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS
> int4 AS ' -- search for the position of $2 in $1
> 
> declare srcstr alias for $1; searchstr alias for $2;
> 
> begin return position(searchstr in srcstr); ' LANGUAGE 'plpgsql'
> VOLATILE; 

You are missing the "end" keyword in there. Also, I'd think this 
function is IMMUTABLE not VOLATILE.

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar)
RETURNS int4 AS '  -- search for the position of $2 in $1  declare    srcstr alias for $1;    searchstr alias for $2;
begin   return position(searchstr in srcstr);  end;
 
' LANGUAGE 'plpgsql' IMMUTABLE;

This could also be done as:

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar)
RETURNS int4 AS '  select position($2 in $1)
' LANGUAGE 'sql';


HTH,

Joe

Richard,

>--------------------------- CREATE OR REPLACE FUNCTION public.locate(bpchar,
> bpchar)
>   RETURNS int4 AS
> '
>   -- search for the position of $2 in $1
>
>   declare
>     srcstr alias for $1;
>     searchstr alias for $2;
>
> begin
> return position(searchstr in srcstr);

You're missing "END;".

> '
>   LANGUAGE 'plpgsql' VOLATILE;

Also, the function is not VOLATILE.  It's IMMUTABLE, and STRICT as well.


-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Richard,

> The goal is to have
>
> locate( stra, strb) = position(strb in stra)

Also, this will run faster if you do it as a SQL function:

CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> Also, this will run faster if you do it as a SQL function:

> CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
> SELECT POSITION($2, $1);
> ' LANGUAGE SQL IMMUTABLE STRICT;

This is definitely the solution I'd recommend for 7.4 (because 7.4 would
inline the SQL function definition, resulting in zero runtime overhead).
In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
comparable.  Has anyone done any head-to-head performance comparisons
on such simple functions?
        regards, tom lane

Tom Lane wrote:
> Josh Berkus <josh@agliodbs.com> writes:
>>Also, this will run faster if you do it as a SQL function:
> 
>>CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
>>SELECT POSITION($2, $1);
>>' LANGUAGE SQL IMMUTABLE STRICT;
> 
> This is definitely the solution I'd recommend for 7.4 (because 7.4 would
> inline the SQL function definition, resulting in zero runtime overhead).
> In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
> comparable.  Has anyone done any head-to-head performance comparisons
> on such simple functions?
> 

I did a quick check last night on 7.3.4 and found that plpgsql was faster:

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '  -- search for the position of $2 in $1
declare   srcstr alias for $1;    searchstr alias for $2;  begin    return position(searchstr in srcstr);  end;
 
' LANGUAGE 'plpgsql' IMMUTABLE;

regression=# explain analyze select locate('abc','b');
                                    QUERY PLAN
---------------------------------------------------------------------------------- Result  (cost=0.00..0.01 rows=1
width=0)(actual time=0.01..0.01 
 
rows=1 loops=1) Total runtime: 0.03 msec
(2 rows)

DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '  select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');                                    QUERY PLAN
---------------------------------------------------------------------------------- Result  (cost=0.00..0.01 rows=1
width=0)(actual time=0.24..0.24 
 
rows=1 loops=1) Total runtime: 0.26 msec
(2 rows)


On 7.4 (different hardware), I get this:

plpgsql
--------
regression=# explain analyze select locate('abc','b');                                    QUERY PLAN
---------------------------------------------------------------------------------- Result  (cost=0.00..0.01 rows=1
width=0)(actual time=0.00..0.00 
 
rows=1 loops=1) Total runtime: 0.05 msec
(2 rows)

sql
--------
regression=# explain analyze select locate('abc','b');                                    QUERY PLAN
---------------------------------------------------------------------------------- Result  (cost=0.00..0.01 rows=1
width=0)(actual time=0.01..0.01 
 
rows=1 loops=1) Total runtime: 0.03 msec
(2 rows)


Joe

Joe Conway <mail@joeconway.com> writes:
>> ...  Has anyone done any head-to-head performance comparisons
>> on such simple functions?

> I did a quick check last night on 7.3.4 and found that plpgsql was faster:

> regression=# explain analyze select locate('abc','b');

Er ... I'm not sure you're measuring anything reliable there.  In
particular, since you declared the plpgsql function immutable, the
planner would have reduced this function call to a constant on sight,
and there is no evaluation happening at runtime at all.  The SQL
version shows as faster only because you neglected to mark it as
immutable; else it'd have gotten the same treatment.  In any case, a
single call of a simple function is likely to be swamped by executor
startup/shutdown overhead.

I tried the same function definitions using a test like this
explain analyze select locate(f1,'b') from t1;

where t1 is a 10000-row table with a single text column.  The results I
get are that the SQL function is very marginally faster than the plpgsql
one in 7.3 (1200 vs 1350 msec), and significantly faster in 7.4
(385 vs 1600 msec).  The basic table-scan overhead can be determined
from
explain analyze select f1,'b' from t1;

which shows as about 330 msec in both versions; subtracting that off
tells you how long it actually took to do 10000 function calls.

[hmm, I wonder why plpgsql seems to have gotten slower in 7.4...]
        regards, tom lane

I said:
> [hmm, I wonder why plpgsql seems to have gotten slower in 7.4...]

False alarm --- or at least, it wasn't plpgsql's fault.  I copied Joe's
function definition, which was

CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '

But since position() takes arguments of type TEXT, there are two
bpchar->text coercions involved inside the function.  This was a "free"
binary coercion in 7.3, but 7.4 interprets it as invoking rtrim().
The extra rtrim operations account for most of the slowdown.

Using functions declared like "locate(text, text)" to avoid the unwanted
type coercions, I get these kinds of numbers:
    7.4        7.3

position()    1 usec/call    1 usec/call
SQL func    1 usec/call    90 usec/call
plpgsql func    110 usec/call    100 usec/call
        regards, tom lane

Tom,

> position()    1 usec/call    1 usec/call
> SQL func    1 usec/call    90 usec/call
> plpgsql func    110 usec/call    100 usec/call

Hmmm ... this does still seem to show that plpgsql is 10% slower in 7.4.  Any 
idea why?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus <josh@agliodbs.com> writes:
> Hmmm ... this does still seem to show that plpgsql is 10% slower in
> 7.4.

Yeah, I just did some profiling to check on that, and it seems there is
extra overhead in plpgsql's exec_eval_simple_expr(), to the tune of
another palloc or two down inside CreateExprContext().  This probably
would not show up so heavily in a more complex plpgsql function, but
it's a noticeable percentage in this trivial example.

I had noticed this before but I'm not sure there's any easy solution.
Awhile back I looked at saving the exprcontext across calls instead of
creating and deleting it on each call to exec_eval_simple_expr, but the
idea broke down in situations involving recursive plpgsql functions.
I'll take another look though.
        regards, tom lane