PostgreSQL Connection Reset via DISCARD ALL Reverts to Startup Parameters, Not Config Defaults

[rahim-kanji]

Description:

When using ProxySQL with PostgreSQL, backend connections are optimized using the options= field to pass session parameters during connection startup. These parameters become startup parameters in PostgreSQL.

However, when ProxySQL resets a connection using DISCARD ALL, PostgreSQL restores session parameters to their startup values, not the server’s default configuration values. ProxySQL assumes the connection has been fully reset to default config values, leading to session state mismatches and potential incorrect behavior for subsequent clients reusing that connection.

Consider this scenario:

1. Client A connects to ProxySQL.
2. Client A executes:
   SET client_min_messages TO 'error';
SET bytea_output TO 'hex';
SELECT 1;
3. During backend connection creation, ProxySQL includes:
   options='-c client_min_messages=error -c bytea_output=hex'
   These become PostgreSQL startup parameters.
4. Later, Client A executes:
   SET test TO 'dummy'
   This causes the session be locked on hostgroup, triggering DISCARD ALL on connection release.
5. When Client A disconnects, ProxySQL runs DISCARD ALL to reset the session.
6. However, DISCARD ALL resets parameters to startup values, not PostgreSQL configuration defaults.
   client_min_messages is reset to 'error' (from options, not config default).
   bytea_output is reset to 'hex' (from options, not config default).
7. Internally, ProxySQL incorrectly assumes the connection has been fully reset to server defaults. It considers the connection clean and returns it to the pool.

This leads to incorrect behavior for the next client reusing this connection, because the session-level environment is not what ProxySQL believes it to be.

Expected Behavior:

• Respect PostgreSQL’s DISCARD ALL behavior:
  ProxySQL should acknowledge that DISCARD ALL resets parameters to startup values, not server-configured defaults. Connection reset logic should reflect this behavior accurately.

Suggested Solution

• Minimize use of options= for session parameters:
  Instead of passing all session-level configuration through the options field (which sets startup parameters), ProxySQL should:
• Only use options= for essential or critical parameters required at startup.
• Set other session parameters after connection establishment using explicit SET commands. This separation ensures that when DISCARD ALL is executed:
  • Critical parameters revert to their startup values (as expected).
  • Non-critical parameters revert to PostgreSQL’s server-configured defaults.