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

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
-----
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

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@aklaver.com

Hi

2016-03-16 15:58 GMT+01:00 Dang Minh Huong <kakalot49@gmail.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@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
-----
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general