Extended Query: Prevent premature commit by deferring SYNC until pipeline end - PostgreSQL#5119
Conversation
Previously, each extended-query block was terminated with a SYNC, which caused implicit transactions to commit prematurely. As a result, earlier write operations (INSERT/UPDATE/DELETE) could not be rolled back if a later statement in the same sequence failed. This change switches to libpq pipeline mode and replaces intermediate SYNC messages with FLUSH, ensuring that all client query frames execute as part of the same implicit transaction. A final SYNC is still issued to resynchronize the connection and make it safe for reuse in the pool.
There was a problem hiding this comment.
Summary of Changes
Hello @rahim-kanji, I'm Gemini Code Assist1! I'm currently reviewing this pull request and will post my feedback shortly. In the meantime, here's a summary to help you and other reviewers quickly get up to speed!
This pull request significantly refactors how PostgreSQL extended queries are handled to prevent premature transaction commits. By leveraging libpq's pipeline mode, it ensures that a series of operations within an extended query block are treated as a single implicit transaction, allowing for proper error handling and rollbacks. This change improves the robustness and reliability of PostgreSQL interactions, particularly in scenarios involving multiple write operations.
Highlights
- PostgreSQL Extended Query Transaction Handling: The previous behavior of terminating each extended-query block with a
SYNCmessage caused implicit transactions to commit prematurely, preventing rollbacks of earlier write operations if a later statement failed. This pull request addresses this by switching tolibpqpipeline mode. - Pipeline Mode Implementation: Intermediate
SYNCmessages within an extended query sequence are now replaced withFLUSHmessages. A finalSYNCis issued only at the end of the pipeline to resynchronize the connection, ensuring all client query frames execute as part of the same implicit transaction. - Connection Resynchronization: New asynchronous states and methods (
ASYNC_RESYNC_START,ASYNC_RESYNC_CONT,async_perform_resync) have been introduced to handle explicit connection resynchronization when errors occur within a pipeline, ensuring the connection returns to aReadyForQuerystate. - Connection Pooling Logic Update: The connection pooling mechanism has been updated to check if a PostgreSQL connection is in an active pipeline state (
is_pipeline_active()) before returning it to the pool. If a pipeline is active, a new session is created and the connection is reset to prevent state contamination. - New Extended Query Flags: Two new flags,
PGSQL_EXTENDED_QUERY_FLAG_SYNCandPGSQL_EXTENDED_QUERY_FLAG_IMPLICIT_PREPARE, have been added to control theSYNCandFLUSHbehavior within extended query processing, allowing for more granular control over transaction boundaries.
Using Gemini Code Assist
The full guide for Gemini Code Assist can be found on our documentation page, here are some quick tips.
Invoking Gemini
You can request assistance from Gemini at any point by creating a comment using either /gemini <command> or @gemini-code-assist <command>. Below is a summary of the supported commands on the current page.
| Feature | Command | Description |
|---|---|---|
| Code Review | /gemini review |
Performs a code review for the current pull request in its current state. |
| Pull Request Summary | /gemini summary |
Provides a summary of the current pull request in its current state. |
| Comment | @gemini-code-assist | Responds in comments when explicitly tagged, both in pull request comments and review comments. |
| Help | /gemini help |
Displays a list of available commands. |
Customization
To customize Gemini Code Assist for GitHub experience, repository maintainers can create a configuration file and/or provide a custom code review style guide (such as PEP-8 for Python) by creating and adding files to a .gemini/ folder in the base of the repository. Detailed instructions can be found here.
Limitations & Feedback
Gemini Code Assist may make mistakes. Please leave feedback on any instances where its feedback is incorrect or counter productive. You can react with 👍 and 👎 on @gemini-code-assist comments. If you're interested in giving your feedback about your experience with Gemini Code Assist for Github and other Google products, sign up here.
You can also get AI-powered code generation, chat, as well as code reviews directly in the IDE at no cost with the Gemini Code Assist IDE Extension.
Footnotes
-
Review the Privacy Notices, Generative AI Prohibited Use Policy, Terms of Service, and learn how to configure Gemini Code Assist in GitHub here. Gemini can make mistakes, so double check it and use code with caution. ↩
There was a problem hiding this comment.
Code Review
This pull request introduces a significant and correct change to handle PostgreSQL extended queries by using libpq's pipeline mode. This defers the SYNC message to the end of a query sequence, preventing premature commits within implicit transactions and ensuring atomicity. The implementation adds new states to the async state machine for resynchronization and correctly handles session resets for pipelined connections. The code is well-structured, and the changes are consistent across different files. I've found a few minor issues related to code style and a potential bug in an edge case, which I've commented on. Overall, this is a great improvement.
|
Summary
Previously, each extended-query block was terminated with a
SYNC, which caused implicit transactions to commit prematurely. As a result, earlier write operations (INSERT/UPDATE/DELETE) could not be rolled back if a later statement in the same sequence failed.This change switches to libpq pipeline mode and replaces intermediate
SYNCmessages withFLUSH, ensuring that all client query frames execute as part of the same implicit transaction. A finalSYNCis still issued to resynchronize the connection and make it safe for reuse in the pool.Closes#5118