chibenwa commented on PR #2514: URL: https://github.com/apache/james-project/pull/2514#issuecomment-2490290568
(Indeed)  ChatGPT chat if relevant: this *might* be an actuall PG limitation we hit... ``` Is a SQL connection usable to run other queries if I am paging results from the database on a select query? ``` Whether a SQL connection can be used to run other queries while paging results from a database depends on the specifics of how the database and the connection are configured, as well as the SQL client library you're using. Here are the key considerations: ### 1. **Cursor Behavior** - **Forward-Only Cursors**: If you're using a forward-only cursor for paging (common in libraries such as JDBC or Python's `psycopg2`), the connection is typically locked for that query, and running another query might not be allowed until the current one completes or the cursor is closed. - **Scrollable Cursors**: Some databases support scrollable cursors, which might allow other queries on the same connection depending on the database's implementation. ### 2. **Database Locking and Transaction Scope** - If the paging query is running within a transaction, the database may hold locks that prevent concurrent queries on the same connection. - Running multiple queries simultaneously on a single connection could also violate transactional guarantees. ### 3. **Database Support for Concurrent Queries** - Some databases (e.g., PostgreSQL, MySQL) support only one active query per connection at a time. Running another query will result in an error unless the first query is completed or the results are fully fetched. - Databases like SQL Server and Oracle allow multiple active result sets (MARS) or similar mechanisms, enabling simultaneous queries under certain conditions. ### 4. **Library-Specific Considerations** - Some client libraries (like Python's `asyncpg` for PostgreSQL) allow multiple queries to be queued asynchronously on a single connection. - Libraries like `sqlalchemy` in Python often manage connection pooling, enabling multiple queries across multiple connections, even if one connection is busy. ### Best Practices for Paging - **Use Connection Pooling**: Tools like HikariCP (Java) or SQLAlchemy’s connection pool ensure each query can use its own connection, avoiding contention. - **Fetch Small Chunks**: Retrieve only the required number of rows for each page to minimize connection usage. - **Close Cursors**: Ensure cursors are closed promptly after fetching the results. ### Conclusion If you're paging results from a database on a select query, it's generally safer to assume that the connection is not available for other queries until the operation completes. Using connection pooling or multiple connections can mitigate this limitation effectively. ``` Does postgresql support scrollable cursors? Are scrollable cursors implemented with R2DC library? ``` ### PostgreSQL and Scrollable Cursors **1. PostgreSQL Native Support:** PostgreSQL does support scrollable cursors. A scrollable cursor allows you to fetch rows in any order (e.g., forward, backward, relative, absolute positioning). This is particularly useful for applications that need random access to query results. - To create a scrollable cursor in PostgreSQL, you use the SQL `DECLARE` statement with the `SCROLL` keyword: ```sql DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM my_table; ``` - Once the cursor is declared, you can use `FETCH` commands like `FETCH NEXT`, `FETCH PRIOR`, or `FETCH ABSOLUTE` to navigate through the result set. - Scrollable cursors are limited to explicit transactions. That means you need to wrap them within a `BEGIN`/`COMMIT` or `BEGIN`/`ROLLBACK` block. --- **2. R2DBC and Scrollable Cursors:** [R2DBC](https://r2dbc.io/) (Reactive Relational Database Connectivity) is designed for reactive programming and focuses on streaming data asynchronously. - R2DBC itself does not directly expose scrollable cursors in the way traditional JDBC does. Instead, it provides reactive streams for processing results row-by-row in a non-blocking manner. - While PostgreSQL supports scrollable cursors natively, the R2DBC PostgreSQL driver does not provide a direct interface for them. You would need to implement a workaround using PostgreSQL's native SQL commands for cursor operations. --- ### Summary - **PostgreSQL:** Fully supports scrollable cursors with SQL commands. - **R2DBC:** Does not natively implement scrollable cursors. However, its reactive streams can achieve similar functionality by processing and re-querying data as needed for reactive workflows. If scrollable cursors are a strict requirement for your application, traditional JDBC might be a better fit than R2DBC. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: notifications-unsubscr...@james.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: notifications-unsubscr...@james.apache.org For additional commands, e-mail: notifications-h...@james.apache.org
