From: | Dang Minh Huong <kakalot49(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | log temp files are created twice in PL/pgSQL function |
Date: | 2016-03-16 14:58:48 |
Message-ID: | BCEB546D-7FEE-4930-891F-48C993ACD5F5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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(at)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
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2016-03-16 15:09:57 | Re: Confusing deadlock report |
Previous Message | Thomas Kellerer | 2016-03-16 14:37:33 | Re: Confusing deadlock report |