pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Avoid unnecessary plancache revalidation of utility statements. 7+ messages / 1 participants [nested] [flat]
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ba0d737caa44d322c709c4d64e95de86b126f3e4 Modified Files -------------- src/backend/parser/analyze.c | 52 ++++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 108 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/d8b2fcc9d4b5af9704dadf00d68859229d9868ad Modified Files -------------- src/backend/parser/analyze.c | 52 ++++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 108 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/9b2a41db1cc007df304bf7874813e6ab78b9a830 Modified Files -------------- src/backend/parser/analyze.c | 52 ++++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 108 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/8700851352a89e9e78da9df31fd6d3a75df98470 Modified Files -------------- src/backend/parser/analyze.c | 52 ++++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 108 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_11_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/9c59f3862b182dffa85e7d1ea924187c22ace086 Modified Files -------------- src/backend/parser/analyze.c | 51 +++++++++++++++++--- src/backend/utils/cache/plancache.c | 70 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 +++++++ 5 files changed, 107 insertions(+), 50 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/27566bcf3c875a7a75c4f5eac76324f2fe24edda Modified Files -------------- src/backend/parser/analyze.c | 51 +++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 107 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
* pgsql: Avoid unnecessary plancache revalidation of utility statements. @ 2023-08-24 16:03 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 7+ messages in thread From: Tom Lane @ 2023-08-24 16:03 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Avoid unnecessary plancache revalidation of utility statements. Revalidation of a plancache entry (after a cache invalidation event) requires acquiring a snapshot. Normally that is harmless, but not if the cached statement is one that needs to run without acquiring a snapshot. We were already aware of that for TransactionStmts, but for some reason hadn't extrapolated to the other statements that PlannedStmtRequiresSnapshot() knows mustn't set a snapshot. This can lead to unexpected failures of commands such as SET TRANSACTION ISOLATION LEVEL. We can fix it in the same way, by excluding those command types from revalidation. However, we can do even better than that: there is no need to revalidate for any statement type for which parse analysis, rewrite, and plan steps do nothing interesting, which is nearly all utility commands. To mechanize this, invent a parser function stmt_requires_parse_analysis() that tells whether parse analysis does anything beyond wrapping a CMD_UTILITY Query around the raw parse tree. If that's what it does, then rewrite and plan will just skip the Query, so that it is not possible for the same raw parse tree to produce a different plan tree after cache invalidation. stmt_requires_parse_analysis() is basically equivalent to the existing function analyze_requires_snapshot(), except that for obscure reasons that function omits ReturnStmt and CallStmt. It is unclear whether those were oversights or intentional. I have not been able to demonstrate a bug from not acquiring a snapshot while analyzing these commands, but at best it seems mighty fragile. It seems safer to acquire a snapshot for parse analysis of these commands too, which allows making stmt_requires_parse_analysis and analyze_requires_snapshot equivalent. In passing this fixes a second bug, which is that ResetPlanCache would exclude ReturnStmts and CallStmts from revalidation. That's surely *not* safe, since they contain parsable expressions. Per bug #18059 from Pavel Kulakov. Back-patch to all supported branches. Discussion: https://postgr.es/m/18059-79c692f036b25346@postgresql.org Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/b808dbf905827de5bbbd933677d57b6c4d35c111 Modified Files -------------- src/backend/parser/analyze.c | 51 +++++++++++++++++--- src/backend/utils/cache/plancache.c | 69 +++++++++++----------------- src/include/parser/analyze.h | 1 + src/pl/plpgsql/src/expected/plpgsql_call.out | 17 +++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 18 ++++++++ 5 files changed, 107 insertions(+), 49 deletions(-) ^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-08-24 16:03 UTC | newest] Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. Tom Lane <tgl@sss.pgh.pa.us> 2023-08-24 16:03 pgsql: Avoid unnecessary plancache revalidation of utility statements. 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