From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Dang Minh Huong <kakalot49(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: log temp files are created twice in PL/pgSQL function |
Date: | 2016-03-16 17:46:34 |
Message-ID: | 56E99BFA.2060608@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Ranier VF | 2016-03-16 20:49:31 | Error: insufficient data in the message |
Previous Message | Rakesh Kumar | 2016-03-16 15:53:20 | Re: Confusing deadlock report |