Hi,

Why does log temp files are created twice when query is executed in PL/pgSQL 
function?
Would you please explain it to me?

As below test result. Log temp files are created twice when SELECT statement is 
put
 into  a PL/pgSQL function. It led a little of performance degradation.
Is there any way to define PL/pgSQL function to avoid this issue?
# I am using PostgreSQL 9.3.9

my test results
-----
[postgres@test]$ psql -c "select test_tempfiles();" > /dev/null 
LOG:  temporary file: path "base/pgsql_tmp/pgsql_tmp2223.0", size 3244032 
CONTEXT: PL/pgSQL function test_cursor() line 3 at RETURN QUERY 
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp23223.1", size 2828062 
LOG: duration: 421.426 ms statement: select test_tempfiles();

[postgres@test]$ psql -c "select name from testtbl order by id" > /dev/null 
LOG: temporary file: path "base/pgsql_tmp/pgsql_tmp25586.0", size 2850816 
LOG: duration: 389.054 ms statement: select random from testtbl order by random
-----

test_tempfiles() function is defined as below
-----
CREATE OR REPLACE FUNCTION public.test_tempfiles() 
RETURNS TABLE(name text) 
LANGUAGE plpgsql 
AS 
$function$ 
begin 
        return query execute "select name from testtbl order by id "; 
end; 
$function$
-----

Thanks and best regrards,
bocap










-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Reply via email to