Thread: Mystery function error

Mystery function error

From
"Richard Sydney-Smith"
Date:
As I am converting from Sybase I wanted to create a function which would replicate the behaviour of the sybase "Locate" command.
 
The goal is to have
 
locate( stra, strb) = position(strb in stra)
 
where "position" is the standard postgres function for the index position of string "A" in string "B"
 
My attempt at a function to do this task returns the error message
 
ERROR: parse error at or near '"'   
 
I can not see why.... and have attached the simple function.
 
------------------------------------------------------------------------------------------------------
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;
-------------------------------------------------------------------------------------------------------------
 
Thanks very much
 
Richard

Re: Mystery function error

From
Joe Conway
Date:
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




Re: Mystery function error

From
Josh Berkus
Date:
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


Re: Mystery function error

From
Josh Berkus
Date:
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


Re: Mystery function error

From
Tom Lane
Date:
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


Re: Mystery function error

From
Joe Conway
Date:
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



Re: Mystery function error

From
Tom Lane
Date:
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


Re: Mystery function error

From
Tom Lane
Date:
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


Re: Mystery function error

From
Josh Berkus
Date:
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


Re: Mystery function error

From
Tom Lane
Date:
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