pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Restore the ability to run pl/pgsql expression queries in parall 7+ messages / 1 participants [nested] [flat]
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/87b05fdc73e84d6b0bf0e03efad95c99c203cd1f Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_17_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a553a2289dbb7f157234ec08122bb3f54a417a24 Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/3bbc1c4a73384bee6af6ec0c28875d2576c0d2ca Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_18_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/440c5ee202000a30c4e7b27cd952edb2ab16cea8 Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/c65c36ab51952059c362807d7c353c9c19355bc0 Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ae693c0bfd9e3aa487658d4d8e22946f7a191da7 Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Restore the ability to run pl/pgsql expression queries in parall @ 2025-07-07 18:34 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2025-07-07 18:34 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Restore the ability to run pl/pgsql expression queries in parallel. pl/pgsql's notion of an "expression" is very broad, encompassing any SQL SELECT query that returns a single column and no more than one row. So there are cases, for example evaluation of an aggregate function, where the query involves significant work and it'd be useful to run it with parallel workers. This used to be possible, but commits 3eea7a0c9 et al unintentionally disabled it. The simplest fix is to make exec_eval_expr() pass maxtuples = 0 rather than 2 to exec_run_select(). This avoids the new rule that we will never use parallelism when a nonzero "count" limit is passed to ExecutorRun(). (Note that the pre-3eea7a0c9 behavior was indeed unsafe, so reverting that rule is not in the cards.) The reason for passing 2 before was that exec_eval_expr() will throw an error if it gets more than one returned row, so we figured that as soon as we have two rows we know that will happen and we might as well stop running the query. That choice was cost-free when it was made; but disabling parallelism is far from cost-free, so now passing 2 amounts to optimizing a failure case at the expense of useful cases. An expression query that can return more than one row is certainly broken. People might now need to wait a bit longer to discover such breakage; but hopefully few will use enormously expensive cases as their first test of new pl/pgsql logic. Author: Dipesh Dhameliya <dipeshdhameliya125@gmail.com> Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us> Discussion: https://postgr.es/m/CABgZEgdfbnq9t6xXJnmXbChNTcWFjeM_6nuig41tm327gYi2ig@mail.gmail.com Backpatch-through: 13 Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/602c91cf2ef7745bdecac2d3a644a343c7c0a17d Modified Files -------------- src/pl/plpgsql/src/pl_exec.c | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2025-07-07 18:34 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us> 2025-07-07 18:34 pgsql: Restore the ability to run pl/pgsql expression queries in parall Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox