BUG #14059: BUG? function in select clause called more times when use offset
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?
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
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
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thanks
--
公益是一辈子的事,I'm Digoal,Just Do It.
At 2016-04-02 22:49:26, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:
Show quoted text
digoal@126.com writes:
postgres=# create or replace function f() returns void as $$
declare
begin
raise notice 'called';
end;
$$ language plpgsql strict volatile;
CREATE FUNCTIONpostgres=# 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