Обсуждение: Retrieve results in PostgreSQL stored procedure allowing query parallelism
I want to execute an SQL query and process its results inside a stored procedure without preventing query parallelism. Since I don't want to prevent query parallelism, cursors can't be used, and I would like to avoid creating a temporal table. Is this possible? If so, what is the best way to execute the query, retrieve all results in memory, and process them inside the stored procedure? -- Joan Pujol
Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism
От
"David G. Johnston"
Дата:
On Tue, Nov 21, 2023, 11:10 Joan Pujol <joanpujol@gmail.com> wrote:
I want to execute an SQL query and process its results inside a stored
procedure without preventing query parallelism. Since I don't want to
prevent query parallelism, cursors can't be used, and I would like to
avoid creating a temporal table.
Is this possible? If so, what is the best way to execute the query,
retrieve all results in memory, and process them inside the stored
procedure?
You must use create table as if you want a result that is both accessible to subsequent statements and uses parallelism to be produced. There is no saving results into memory - you either save them explicitly or iterate over them and the later prevents parallelism as you've noted.
David J.
Thanks, David. If I try to do something like: EXECUTE SELECT INTO ARRAY_AGG(t.*) INTO result_records Would internally use cursors too and have the same restrictions? Cheers, On Tue, 21 Nov 2023 at 19:22, David G. Johnston <david.g.johnston@gmail.com> wrote: > > On Tue, Nov 21, 2023, 11:10 Joan Pujol <joanpujol@gmail.com> wrote: >> >> I want to execute an SQL query and process its results inside a stored >> procedure without preventing query parallelism. Since I don't want to >> prevent query parallelism, cursors can't be used, and I would like to >> avoid creating a temporal table. >> >> Is this possible? If so, what is the best way to execute the query, >> retrieve all results in memory, and process them inside the stored >> procedure? > > > You must use create table as if you want a result that is both accessible to subsequent statements and uses parallelismto be produced. There is no saving results into memory - you either save them explicitly or iterate over themand the later prevents parallelism as you've noted. > > David J. -- Joan Jesús Pujol Espinar http://www.joanpujol.cat
Re: Retrieve results in PostgreSQL stored procedure allowing query parallelism
От
"David G. Johnston"
Дата:
On Tue, Nov 21, 2023 at 11:31 AM Joan Pujol <joanpujol@gmail.com> wrote:
If I try to do something like:
EXECUTE SELECT INTO ARRAY_AGG(t.*) INTO result_records
Would internally use cursors too and have the same restrictions?
Producing a single row single column result and then storing it into a variable should leverage parallelism if the query is amenable to it.
David J.