Re: log temp files are created twice in PL/pgSQL function

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
>