Request more documentation for incompatibility of parallelism and plpgsql exec_run_select

From: Mark Dilger <hornschnorter(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Request more documentation for incompatibility of parallelism and plpgsql exec_run_select
Date: 2017-06-30 03:55:45
Message-ID: BC5AD216-B989-46C7-B678-58110229B197@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hackers,

In src/pl/plpgsql/src/pl_exec.c: exec_run_select intentionally does not
allow a parallel plan if a portal will be returned. This has the practical
consequence that a common coding practice (at least for me) of doing
something like:

create function myfunc(arg1 text, arg2 text) returns setof myfunctype as $$
declare
sql text;
result myfunctype;
begin
-- unsafe interpolation, but this is just a code example
sql := 'select foo from bar where a = ' || arg1 || ' and b = ' || arg2;
for result in execute sql loop
return next result;
end loop;
return;
end;
$$ language plpgsql volatile;

can't run the generated 'sql' in parallel. I think this is understandable, but
the documentation of this limitation in the sgml docs is thin. Perhaps
someone who understands this limitation better than I do can document it?
Changing myfunc to create a temporary table, to execute the sql to populate
that temporary table, and to then loop through the temporary table's rows
fixes the problem. For the real-world example where I hit this, that single
change decreases the runtime from 13.5 seconds to 2.5 seconds. This
makes sense to me, because doing it that way means pl_exec knows that
it won't be returning a portal for the executed sql, so the parallel plan is
still allowed.

Sorry to be a nag. I'm only trying to help the next person who might
otherwise trip over this limitation. Perhaps this belongs in section 15.3.4
or 42.5.4.

mark

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2017-06-30 04:14:19 Re: Request more documentation for incompatibility of parallelism and plpgsql exec_run_select
Previous Message Noah Misch 2017-06-30 03:45:47 Re: Code quality issues in ICU patch