On 03/16/2016 07:58 AM, Dang Minh Huong wrote:
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
Are you sure. The query is double quoted which returns an error when run on my machine.
What do have logging set to?
----- 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
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
