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