Обсуждение: BUG #14059: BUG? function in select clause called more times when use offset
BUG #14059: BUG? function in select clause called more times when use offset
От
digoal@126.com
Дата:
The following bug has been logged on the website: Bug reference: 14059 Logged by: digoal Email address: digoal@126.com PostgreSQL version: 9.5.2 Operating system: CentOS 6.x x64 Description: postgres=# create or replace function f() returns void as $$ declare begin raise notice 'called'; end; $$ language plpgsql strict volatile; CREATE FUNCTION postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset 3 limit 2; NOTICE: called NOTICE: called NOTICE: called NOTICE: called NOTICE: called f | id ---+---- | 4 | 5 (2 rows) offset skip 3 tuple, but function f() called with 5 times, can we tuning this . or this is a bug?
digoal@126.com writes: > postgres=# create or replace function f() returns void as $$ > declare > begin > raise notice 'called'; > end; > $$ language plpgsql strict volatile; > CREATE FUNCTION > postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset > 3 limit 2; > NOTICE: called > NOTICE: called > NOTICE: called > NOTICE: called > NOTICE: called > f | id > ---+---- > | 4 > | 5 > (2 rows) > offset skip 3 tuple, but function f() called with 5 times, can we tuning > this . > or this is a bug? No, it's not a bug. OFFSET only results in the skipped tuples not being delivered to the client; it does not cause them not to be computed. You could probably do something with a two-level select with the OFFSET in the sub-select and the volatile function in the top level. regards, tom lane
<div style="line-height:1.7;color:#000000;font-size:14px;font-family:Arial"><br />Thanks<br /><br /><br /><br /><div style="position:relative;zoom:1">--<br/>公益是一辈子的事,I'm Digoal,Just Do It.<br /><div style="clear:both"></div></div><div id="divNeteaseMailCard"></div><br/><pre><br />At 2016-04-02 22:49:26, "Tom Lane" <tgl@sss.pgh.pa.us> wrote: >digoal@126.com writes: >> postgres=# create or replace function f() returns void as $$ >> declare >> begin >> raise notice 'called'; >> end; >> $$ language plpgsql strict volatile; >> CREATE FUNCTION > >> postgres=# select f(),* from ( values(1),(2),(3),(4),(5),(6) ) t(id) offset >> 3 limit 2; >> NOTICE: called >> NOTICE: called >> NOTICE: called >> NOTICE: called >> NOTICE: called >> f | id >> ---+---- >> | 4 >> | 5 >> (2 rows) > >> offset skip 3 tuple, but function f() called with 5 times, can we tuning >> this . >> or this is a bug? > >No, it's not a bug. OFFSET only results in the skipped tuples not being >delivered to the client; it does not cause them not to be computed. > >You could probably do something with a two-level select with the OFFSET >in the sub-select and the volatile function in the top level. > > regards, tom lane </pre></div>