pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMystery function error
10+ messages / 4 participants
[nested] [flat]
* Mystery function error
@ 2003-09-28 03:39 Richard Sydney-Smith <richard@ibisaustralia.com>
0 siblings, 3 replies; 10+ messages in thread
From: Richard Sydney-Smith @ 2003-09-28 03:39 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 05:21 Joe Conway <mail@joeconway.com>
parent: Richard Sydney-Smith <richard@ibisaustralia.com>
2 siblings, 0 replies; 10+ messages in thread
From: Joe Conway @ 2003-09-28 05:21 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 05:28 Josh Berkus <josh@agliodbs.com>
parent: Richard Sydney-Smith <richard@ibisaustralia.com>
2 siblings, 0 replies; 10+ messages in thread
From: Josh Berkus @ 2003-09-28 05:28 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 05:29 Josh Berkus <josh@agliodbs.com>
parent: Richard Sydney-Smith <richard@ibisaustralia.com>
2 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2003-09-28 05:29 UTC (permalink / raw)
To: Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 16:06 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-09-28 16:06 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 16:21 Joe Conway <mail@joeconway.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Joe Conway @ 2003-09-28 16:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Josh Berkus <josh@agliodbs.com>; Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 16:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-09-28 16:48 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 18:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2003-09-28 18:29 UTC (permalink / raw)
To: Joe Conway <mail@joeconway.com>; +Cc: Josh Berkus <josh@agliodbs.com>; Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 18:47 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Josh Berkus @ 2003-09-28 18:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Joe Conway <mail@joeconway.com>; +Cc: Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: Mystery function error
@ 2003-09-28 19:04 Tom Lane <tgl@sss.pgh.pa.us>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 10+ messages in thread
From: Tom Lane @ 2003-09-28 19:04 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Joe Conway <mail@joeconway.com>; Richard Sydney-Smith <richard@ibisaustralia.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2003-09-28 19:04 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-09-28 03:39 Mystery function error Richard Sydney-Smith <richard@ibisaustralia.com>
2003-09-28 05:21 ` Joe Conway <mail@joeconway.com>
2003-09-28 05:28 ` Josh Berkus <josh@agliodbs.com>
2003-09-28 05:29 ` Josh Berkus <josh@agliodbs.com>
2003-09-28 16:06 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-28 16:21 ` Joe Conway <mail@joeconway.com>
2003-09-28 16:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-28 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-28 18:47 ` Josh Berkus <josh@agliodbs.com>
2003-09-28 19:04 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox