Hi all,

We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).

CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;


So I made this test:

test=# select setval('test_truc',1);
setval
--------
1
(1 row)

test=# select currval('test_truc') ;
currval
---------
1
(1 row)

test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
-----------+-------------
5001 | 5002
(1 row)

test=# select currval('test_truc') ;
currval
---------
5002
(1 row)


This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.

But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.

Is there any work around ?


Thanks
--
REYNAUD Jean-Samuel <reynaud@elma.fr>
Elma


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Have you tried

SELECT *, test_func(idkeyword)
FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;

?

This should probably have been on -general, btw.

On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
> Hi all,
>
> We need to find a solution for a strange problem.
> We have a plpgsql FUNCTION which performs an heavy job (named
> test_func).
>
> CREATE or replace function test_func(z int) returns integer as $$
> declare
> tst integer;
> begin
> --
> -- Large jobs with z
> --
> tst := nextval('test_truc');
> return tst;
> end;
> $$ LANGUAGE plpgsql;
>
>
> So I made this test:
>
> test=# select setval('test_truc',1);
> setval
> --------
> 1
> (1 row)
>
> test=# select currval('test_truc') ;
> currval
> ---------
> 1
> (1 row)
>
> test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
> idkeyword | test_func
> -----------+-------------
> 5001 | 5002
> (1 row)
>
> test=# select currval('test_truc') ;
> currval
> ---------
> 5002
> (1 row)
>
>
> This demonstrates that the function is called 5001 times though only one
> row is returned. Problem is that this heavy job is performed much, much
> more than needed.
>
> But, If I do:
> test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
> My function is called only once.
>
> Is there any work around ?
>
>
> Thanks
> --
> REYNAUD Jean-Samuel <reynaud@elma.fr>
> Elma
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Hi

I've just tried it, and it works. So it's a good work-around.

Though, is it a wanted feature to have a function being performed on
each row before the offset ?


Le mercredi 21 décembre 2005 à 13:41 -0600, Jim C. Nasby a écrit :
> Have you tried
>
> SELECT *, test_func(idkeyword)
> FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
> ;
>
> ?
>
> This should probably have been on -general, btw.
>
> On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
> > Hi all,
> >
> > We need to find a solution for a strange problem.
> > We have a plpgsql FUNCTION which performs an heavy job (named
> > test_func).
> >
> > CREATE or replace function test_func(z int) returns integer as $$
> > declare
> > tst integer;
> > begin
> > --
> > -- Large jobs with z
> > --
> > tst := nextval('test_truc');
> > return tst;
> > end;
> > $$ LANGUAGE plpgsql;
> >
> >
> > So I made this test:
> >
> > test=# select setval('test_truc',1);
> > setval
> > --------
> > 1
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 1
> > (1 row)
> >
> > test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
> > idkeyword | test_func
> > -----------+-------------
> > 5001 | 5002
> > (1 row)
> >
> > test=# select currval('test_truc') ;
> > currval
> > ---------
> > 5002
> > (1 row)
> >
> >
> > This demonstrates that the function is called 5001 times though only one
> > row is returned. Problem is that this heavy job is performed much, much
> > more than needed.
> >
> > But, If I do:
> > test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
> > My function is called only once.
> >
> > Is there any work around ?
> >
> >
> > Thanks
> > --
> > REYNAUD Jean-Samuel <reynaud@elma.fr>
> > Elma
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 1: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
>
--
REYNAUD Jean-Samuel <reynaud@elma.fr>
Elma


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote:
> Hi
>
> I've just tried it, and it works. So it's a good work-around.
>
> Though, is it a wanted feature to have a function being performed on
> each row before the offset ?

Well, saying offset 5000 pretty much means to calculate the first 5000
rows and throw away the result. To calculate that it needs to execute
the function each time. What happens if the function has side-effects
like in your case? What if you had a WHERE clause that depended on the
result of that function?

If the function has no side-effects, like say pow() then the backend
could skip but that should be transparent to the user. SQL allows you
specify the way you want it and PostgreSQL is simply executing what you
wrote down...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.6 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQFDqn1mIB7bNG8LQkwRAot/AJ46n9r8KjC7unjbCYXHe27ka4sHtwCePIc3
BdCUNdcdk4B3hk0pH+1Mkv4=
=diH4
-----END PGP SIGNATURE-----

On Thu, Dec 22, 2005 at 11:18:22AM +0100, Martijn van Oosterhout wrote:
> On Thu, Dec 22, 2005 at 10:52:58AM +0100, REYNAUD Jean-Samuel wrote:
> > Hi
> >
> > I've just tried it, and it works. So it's a good work-around.
> >
> > Though, is it a wanted feature to have a function being performed on
> > each row before the offset ?
>
> Well, saying offset 5000 pretty much means to calculate the first 5000
> rows and throw away the result. To calculate that it needs to execute
> the function each time. What happens if the function has side-effects
> like in your case? What if you had a WHERE clause that depended on the
> result of that function?
>
> If the function has no side-effects, like say pow() then the backend
> could skip but that should be transparent to the user. SQL allows you
> specify the way you want it and PostgreSQL is simply executing what you
> wrote down...

Well, it would be a good optimization to make if the function is
immutable and isn't otherwise referenced (ie: by WHERE or ORDER BY),
there's no reason I can think of to execute it as you read through the
rows. Might be able to do this with STABLE functions as well.

TODO?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend