pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix behavior of stable functions called from a CALL's argument l 6+ messages / 1 participants [nested] [flat]
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/2dc1deaea0354ce8427abd8c6b11f9c004a65f24 Modified Files -------------- doc/src/sgml/spi.sgml | 8 +++-- src/backend/executor/spi.c | 36 +++++++++++++------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 4 files changed, 130 insertions(+), 14 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ REL_16_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0d18b8eb409ce5cbc14c61e4883240d4668df655 Modified Files -------------- doc/src/sgml/spi.sgml | 8 +++-- src/backend/executor/spi.c | 36 +++++++++++++------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 4 files changed, 130 insertions(+), 14 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ REL_15_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/a160e92779afb1146474585a943454eab8432ea0 Modified Files -------------- doc/src/sgml/spi.sgml | 8 +++-- src/backend/executor/spi.c | 36 +++++++++++++------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 4 files changed, 130 insertions(+), 14 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0f7d1338c832f00645fe1be880529bac1cd1ff9c Modified Files -------------- doc/src/sgml/spi.sgml | 8 +++-- src/backend/executor/spi.c | 36 +++++++++++++------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 4 files changed, 130 insertions(+), 14 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/1d4ea1376bf0772150903713a51931a225a4c935 Modified Files -------------- src/backend/executor/spi.c | 37 ++++++++++++-------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 3 files changed, 124 insertions(+), 13 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
* pgsql: Fix behavior of stable functions called from a CALL's argument l @ 2024-06-07 17:27 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 6+ messages in thread From: Tom Lane @ 2024-06-07 17:27 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix behavior of stable functions called from a CALL's argument list. If the CALL is within an atomic context (e.g. there's an outer transaction block), _SPI_execute_plan should acquire a fresh snapshot to execute any such functions with. We failed to do that and instead passed them the Portal snapshot, which had been acquired at the start of the current SQL command. This'd lead to seeing stale values of rows modified since the start of the command. This is arguably a bug in 84f5c2908: I failed to see that "are we in non-atomic mode" needs to be defined the same way as it is further down in _SPI_execute_plan, i.e. check !_SPI_current->atomic not just options->allow_nonatomic. Alternatively the blame could be laid on plpgsql, which is unconditionally passing allow_nonatomic = true for CALL/DO even when it knows it's in an atomic context. However, fixing it in spi.c seems like a better idea since that will also fix the problem for any extensions that may have copied plpgsql's coding pattern. While here, update an obsolete comment about _SPI_execute_plan's snapshot management. Per report from Victor Yegorov. Back-patch to all supported versions. Discussion: https://postgr.es/m/CAGnEboiRe+fG2QxuBO2390F7P8e2MQ6UyBjZSL_w1Cej+E4=Vw@mail.gmail.com Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/0be81dd71c854eaea506100765a0a52b9e54b461 Modified Files -------------- src/backend/executor/spi.c | 37 ++++++++++++-------- src/pl/plpgsql/src/expected/plpgsql_call.out | 50 ++++++++++++++++++++++++++++ src/pl/plpgsql/src/sql/plpgsql_call.sql | 50 ++++++++++++++++++++++++++++ 3 files changed, 124 insertions(+), 13 deletions(-) ^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-07 17:27 UTC | newest] Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l Tom Lane <tgl@sss.pgh.pa.us> 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l Tom Lane <tgl@sss.pgh.pa.us> 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l Tom Lane <tgl@sss.pgh.pa.us> 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l Tom Lane <tgl@sss.pgh.pa.us> 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l Tom Lane <tgl@sss.pgh.pa.us> 2024-06-07 17:27 pgsql: Fix behavior of stable functions called from a CALL's argument l 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