pg.ddx.io pgsql-committers@postgresql.org mailing list archivehelp / color / mirror / Atom feed
pgsql: Fix some anomalies with NO SCROLL cursors. 5+ messages / 1 participants [nested] [flat]
* pgsql: Fix some anomalies with NO SCROLL cursors. @ 2021-09-10 17:18 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2021-09-10 17:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix some anomalies with NO SCROLL cursors. We have long forbidden fetching backwards from a NO SCROLL cursor, but the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. I think the reason is that this logic was mainly meant to protect plan nodes that can't be run in the reverse direction. However, re-reading the query output is problematic if the query is volatile (which includes SELECT FOR UPDATE, not just queries with volatile functions): the re-read can produce different results, which confuses the cursor navigation logic completely. Another reason for disliking this approach is that some code paths will either fetch backwards or rewind-and-fetch-forwards depending on the distance to the target row; so that seemingly identical use-cases may or may not draw the "cursor can only scan forward" error. Hence, let's clean things up by disallowing rewind as well as fetch-backwards in a NO SCROLL cursor. Ordinarily we'd only make such a definitional change in HEAD, but there is a third reason to consider this change now. Commit ba2c6d6ce created some new user-visible anomalies for non-scrollable cursors WITH HOLD, in that navigation in the cursor result got confused if the cursor had been partially read before committing. The only good way to resolve those anomalies is to forbid rewinding such a cursor, which allows removal of the incorrect cursor state manipulations that ba2c6d6ce added to PersistHoldablePortal. To minimize the behavioral change in the back branches (including v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore, ie has been held over from a previous transaction due to WITH HOLD. This should avoid breaking most applications that have been sloppy about whether to declare cursors as scrollable. We'll enforce the prohibition across-the-board beginning in v15. Back-patch to v11, as ba2c6d6ce was. Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us Branch ------ master Details ------- https://git.postgresql.org/pg/commitdiff/c1b7a6c2731241cf5af4c08de54a64fc8999d727 Modified Files -------------- src/backend/commands/portalcmds.c | 28 +++++++++++----- src/backend/tcop/pquery.c | 28 ++++++++++++++-- src/test/regress/expected/portals.out | 55 +++++++++++++++++++++++++++++++ src/test/regress/expected/tablesample.out | 2 +- src/test/regress/sql/portals.sql | 32 ++++++++++++++++++ src/test/regress/sql/tablesample.sql | 2 +- 6 files changed, 134 insertions(+), 13 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix some anomalies with NO SCROLL cursors. @ 2021-09-10 17:18 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2021-09-10 17:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix some anomalies with NO SCROLL cursors. We have long forbidden fetching backwards from a NO SCROLL cursor, but the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. I think the reason is that this logic was mainly meant to protect plan nodes that can't be run in the reverse direction. However, re-reading the query output is problematic if the query is volatile (which includes SELECT FOR UPDATE, not just queries with volatile functions): the re-read can produce different results, which confuses the cursor navigation logic completely. Another reason for disliking this approach is that some code paths will either fetch backwards or rewind-and-fetch-forwards depending on the distance to the target row; so that seemingly identical use-cases may or may not draw the "cursor can only scan forward" error. Hence, let's clean things up by disallowing rewind as well as fetch-backwards in a NO SCROLL cursor. Ordinarily we'd only make such a definitional change in HEAD, but there is a third reason to consider this change now. Commit ba2c6d6ce created some new user-visible anomalies for non-scrollable cursors WITH HOLD, in that navigation in the cursor result got confused if the cursor had been partially read before committing. The only good way to resolve those anomalies is to forbid rewinding such a cursor, which allows removal of the incorrect cursor state manipulations that ba2c6d6ce added to PersistHoldablePortal. To minimize the behavioral change in the back branches (including v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore, ie has been held over from a previous transaction due to WITH HOLD. This should avoid breaking most applications that have been sloppy about whether to declare cursors as scrollable. We'll enforce the prohibition across-the-board beginning in v15. Back-patch to v11, as ba2c6d6ce was. Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us Branch ------ REL_14_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/d844cd75a6764a1dd2d8b3410144df9ebf8a0f04 Modified Files -------------- src/backend/commands/portalcmds.c | 28 ++++++++++++++++++-------- src/backend/tcop/pquery.c | 33 ++++++++++++++++++++++++++++--- src/test/regress/expected/portals.out | 37 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/portals.sql | 20 +++++++++++++++++++ 4 files changed, 107 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix some anomalies with NO SCROLL cursors. @ 2021-09-10 17:18 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2021-09-10 17:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix some anomalies with NO SCROLL cursors. We have long forbidden fetching backwards from a NO SCROLL cursor, but the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. I think the reason is that this logic was mainly meant to protect plan nodes that can't be run in the reverse direction. However, re-reading the query output is problematic if the query is volatile (which includes SELECT FOR UPDATE, not just queries with volatile functions): the re-read can produce different results, which confuses the cursor navigation logic completely. Another reason for disliking this approach is that some code paths will either fetch backwards or rewind-and-fetch-forwards depending on the distance to the target row; so that seemingly identical use-cases may or may not draw the "cursor can only scan forward" error. Hence, let's clean things up by disallowing rewind as well as fetch-backwards in a NO SCROLL cursor. Ordinarily we'd only make such a definitional change in HEAD, but there is a third reason to consider this change now. Commit ba2c6d6ce created some new user-visible anomalies for non-scrollable cursors WITH HOLD, in that navigation in the cursor result got confused if the cursor had been partially read before committing. The only good way to resolve those anomalies is to forbid rewinding such a cursor, which allows removal of the incorrect cursor state manipulations that ba2c6d6ce added to PersistHoldablePortal. To minimize the behavioral change in the back branches (including v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore, ie has been held over from a previous transaction due to WITH HOLD. This should avoid breaking most applications that have been sloppy about whether to declare cursors as scrollable. We'll enforce the prohibition across-the-board beginning in v15. Back-patch to v11, as ba2c6d6ce was. Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us Branch ------ REL_13_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/fa5d0415f10fac3c2aa4c301840fde1780b91b79 Modified Files -------------- src/backend/commands/portalcmds.c | 28 ++++++++++++++++++-------- src/backend/tcop/pquery.c | 33 ++++++++++++++++++++++++++++--- src/test/regress/expected/portals.out | 37 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/portals.sql | 20 +++++++++++++++++++ 4 files changed, 107 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix some anomalies with NO SCROLL cursors. @ 2021-09-10 17:18 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2021-09-10 17:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix some anomalies with NO SCROLL cursors. We have long forbidden fetching backwards from a NO SCROLL cursor, but the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. I think the reason is that this logic was mainly meant to protect plan nodes that can't be run in the reverse direction. However, re-reading the query output is problematic if the query is volatile (which includes SELECT FOR UPDATE, not just queries with volatile functions): the re-read can produce different results, which confuses the cursor navigation logic completely. Another reason for disliking this approach is that some code paths will either fetch backwards or rewind-and-fetch-forwards depending on the distance to the target row; so that seemingly identical use-cases may or may not draw the "cursor can only scan forward" error. Hence, let's clean things up by disallowing rewind as well as fetch-backwards in a NO SCROLL cursor. Ordinarily we'd only make such a definitional change in HEAD, but there is a third reason to consider this change now. Commit ba2c6d6ce created some new user-visible anomalies for non-scrollable cursors WITH HOLD, in that navigation in the cursor result got confused if the cursor had been partially read before committing. The only good way to resolve those anomalies is to forbid rewinding such a cursor, which allows removal of the incorrect cursor state manipulations that ba2c6d6ce added to PersistHoldablePortal. To minimize the behavioral change in the back branches (including v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore, ie has been held over from a previous transaction due to WITH HOLD. This should avoid breaking most applications that have been sloppy about whether to declare cursors as scrollable. We'll enforce the prohibition across-the-board beginning in v15. Back-patch to v11, as ba2c6d6ce was. Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us Branch ------ REL_12_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/ba408fc960b6be83c8ac0d74f64c02116cd8bd4c Modified Files -------------- src/backend/commands/portalcmds.c | 28 ++++++++++++++++++-------- src/backend/tcop/pquery.c | 33 ++++++++++++++++++++++++++++--- src/test/regress/expected/portals.out | 37 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/portals.sql | 20 +++++++++++++++++++ 4 files changed, 107 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
* pgsql: Fix some anomalies with NO SCROLL cursors. @ 2021-09-10 17:18 Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2021-09-10 17:18 UTC (permalink / raw) To: pgsql-committers@lists.postgresql.org Fix some anomalies with NO SCROLL cursors. We have long forbidden fetching backwards from a NO SCROLL cursor, but the prohibition didn't extend to cases in which we rewind the query altogether and then re-fetch forwards. I think the reason is that this logic was mainly meant to protect plan nodes that can't be run in the reverse direction. However, re-reading the query output is problematic if the query is volatile (which includes SELECT FOR UPDATE, not just queries with volatile functions): the re-read can produce different results, which confuses the cursor navigation logic completely. Another reason for disliking this approach is that some code paths will either fetch backwards or rewind-and-fetch-forwards depending on the distance to the target row; so that seemingly identical use-cases may or may not draw the "cursor can only scan forward" error. Hence, let's clean things up by disallowing rewind as well as fetch-backwards in a NO SCROLL cursor. Ordinarily we'd only make such a definitional change in HEAD, but there is a third reason to consider this change now. Commit ba2c6d6ce created some new user-visible anomalies for non-scrollable cursors WITH HOLD, in that navigation in the cursor result got confused if the cursor had been partially read before committing. The only good way to resolve those anomalies is to forbid rewinding such a cursor, which allows removal of the incorrect cursor state manipulations that ba2c6d6ce added to PersistHoldablePortal. To minimize the behavioral change in the back branches (including v14), refuse to rewind a NO SCROLL cursor only when it has a holdStore, ie has been held over from a previous transaction due to WITH HOLD. This should avoid breaking most applications that have been sloppy about whether to declare cursors as scrollable. We'll enforce the prohibition across-the-board beginning in v15. Back-patch to v11, as ba2c6d6ce was. Discussion: https://postgr.es/m/3712911.1631207435@sss.pgh.pa.us Branch ------ REL_11_STABLE Details ------- https://git.postgresql.org/pg/commitdiff/9ea8d3d24a9fb2bbe99cb17f6ee08747f662aabd Modified Files -------------- src/backend/commands/portalcmds.c | 28 ++++++++++++++++++-------- src/backend/tcop/pquery.c | 33 ++++++++++++++++++++++++++++--- src/test/regress/expected/portals.out | 37 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/portals.sql | 20 +++++++++++++++++++ 4 files changed, 107 insertions(+), 11 deletions(-) ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-09-10 17:18 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2021-09-10 17:18 pgsql: Fix some anomalies with NO SCROLL cursors. Tom Lane <tgl@sss.pgh.pa.us> 2021-09-10 17:18 pgsql: Fix some anomalies with NO SCROLL cursors. Tom Lane <tgl@sss.pgh.pa.us> 2021-09-10 17:18 pgsql: Fix some anomalies with NO SCROLL cursors. Tom Lane <tgl@sss.pgh.pa.us> 2021-09-10 17:18 pgsql: Fix some anomalies with NO SCROLL cursors. Tom Lane <tgl@sss.pgh.pa.us> 2021-09-10 17:18 pgsql: Fix some anomalies with NO SCROLL cursors. 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