From: | Dang Minh Huong <kakalot49(at)gmail(dot)com> |
---|---|
To: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
Cc: | "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 22:16:37 |
Message-ID: | D8A151CD-14C8-4E34-82FA-2B3E93F2D3A9@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi,
Thanks for your prompt response.
Appreciate your help.
Thanks and best regards,
bocap
> Hi
>
> 2016-03-16 15:58 GMT+01:00 Dang Minh Huong <kakalot49(at)gmail(dot)com>:
>> Hi,
>>
>> Why does log temp files are created twice when query is executed in PL/pgSQL function?
>> Would you please explain it to me?
>
> PL/pgSQL materialize result internally.
>
> Usually PostgreSQL operations are executed row by row. But some SRF functions like PLpgSQL functions doesn't support this mode, and returns tuplestore - materialized result.
>
> Using this technique is comfortable, but with some performance risks. Unfortunately, you cannot to change this behave. Not in PL/pgSQL.
>
> You can write C function with same functionality but with row by row returning result mode. It is not possible in PL/pgSQL.
>
> On other hand - you can try to increase work_mem (if your server has enough RAM). Materialization are done when available memory (controlled by work_mem) is too less.
>
> You can try
>
> SET work_mem to '20MB';
> SELECT test_tempfiles();
>
> Regards
>
> Pavel
>
>>
>> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2016-03-16 22:24:09 | Re: How to Qualifying or quantify risk of loss in asynchronous replication |
Previous Message | David G. Johnston | 2016-03-16 21:41:03 | Re: Deleting schema - saving up space - PostgreSQL 9.2 |