Summary

ProxySQL needs to support PostgreSQL query cancellation and backend termination features to allow clients to cancel long-running queries and terminate connections properly. This includes intercepting PostgreSQL-specific functions and handling cancel requests via the PostgreSQL protocol.

Background

PostgreSQL provides several mechanisms for query cancellation:

• pg_cancel_backend(pid) - Cancels a query running on a specific backend
• pg_terminate_backend(pid) - Terminates a backend connection
• pg_backend_pid() - Returns the process ID of the server process handling the current session
• Cancel Request - A special protocol message that clients can send to cancel a running query

Currently, ProxySQL does not properly handle these PostgreSQL-specific features, which limits its ability to manage long-running queries and connections effectively.

Requirements

1. Intercept pg_backend_pid() Function

• When a client executes SELECT pg_backend_pid(), ProxySQL should intercept this function call
• Return the ProxySQL session thread ID instead of the actual backend PID
• This ensures that clients receive a consistent identifier that maps to ProxySQL's internal session tracking
• The returned value should be usable with pg_cancel_backend() and pg_terminate_backend()

2. Intercept pg_terminate_backend(pid) Function

• Parse the SQL query to extract the PID parameter from pg_terminate_backend(<PID>)
  • Return success (true) to the client immediately (as this is an asynchronous operation in ProxySQL)
• Map the PID to the corresponding ProxySQL session thread ID
• Terminate the entire session associated with that thread ID
• Kill all backend connections associated with that session

3. Intercept pg_cancel_backend(pid) Function

• Parse the SQL query to extract the PID parameter from pg_cancel_backend(<PID>)
  • Return success (true) to the client immediately (as this is an asynchronous operation in ProxySQL)
• Map the PID to the corresponding ProxySQL session thread ID
• Locate the specific session and identify its active backend connection
• Send a cancel request to the backend PostgreSQL server for that connection
• The cancel request should use the backend's actual PID and cancel key

4. Support Cancel Request via Connection

• Implement support for PostgreSQL's Cancel Request protocol message
• When a client opens a new connection and sends a Cancel Request:
  • Extract the PID (ProxySQL session thread ID) and cancel secret key from the request
  • Validate the cancel secret key against the stored key for that session
  • If valid, send a cancel request to the actual backend PostgreSQL server
  • Close the cancel request connection
• The cancel request connection is separate from the main query connection (per PostgreSQL protocol)

5. Return BackendKeyData on Successful Connection

• After successful client authentication to ProxySQL, send a BackendKeyData message to the client
• The BackendKeyData message must contain:
  • PID: ProxySQL session thread ID
  • Secret Key: A unique cancel secret key generated specifically for this session
• Store the cancel secret key for later validation
• When a client sends a Cancel Request on a separate connection, ProxySQL will:
  • Extract PID and secret key from the Cancel Request
  • Locate the corresponding session and cancel the query on the backend

6. Add KILL PGSQL CONNECTION Command to Admin Interface

• Add a new admin command: KILL PGSQL CONNECTION <thread_id>
• This command should terminate a specific PostgreSQL session by its ProxySQL thread ID
• Kill all backend connections associated with that session
• This gives administrators direct control over connection management

Implementation Notes

PostgreSQL Protocol References:

• BackendKeyData Message
• CancelRequest Message

Function References:

• pg_backend_pid() - Documentation
• pg_cancel_backend(pid) - Documentation
• pg_terminate_backend(pid) - Documentation]