[kodablah]

I often wonder why connections aren't made more lightweight in Postgres, or if there was an option to steal a connection and have a "RESET" command that destroyed all state. In my Postgres library, I have to keep state information too just so I can "reset" a connection. Also maybe the protocol can add a (optionally client supported) PING to check for socket death to know if a connection is stealable.

[koolba]

That already exists via the "DISCARD" command: https://www.postgresql.org/docs/current/static/sql-discard.h...

[kodablah]

I should have clarified, I meant at the protocol level. It's basically a state machine, and I want a "break" to reset the state machine. This would flush remaining query results, close named prepared statements, rollback any in-process transactions, and put the state back at ready-for-query. In the meantime, those with connections pools (this lib, my client-side lib, etc) have to keep this info (often except prepared statements which is the caller's responsibility to close).

[michelpp]

That is what DISCARD ALL does, it discards all session state.

[kodablah]

I am talking about the protocol, not a query. What if the state of the connection is not even at ready-for-query state, how do I issue a query? There is no DISCARD ALL in the protocol [0]. You might have only read 3 rows and still have a thousand to flush, or you might have sent a Parse and are awaiting a ParseComplete. This is the type of thing that a Postgres connection pooler has to keep track of. Here's some of their code: https://github.com/yandex/odyssey/blob/master/sources/reset.... (note how it has to get back to ready-for-query state before rolling back).

0 - https://www.postgresql.org/docs/current/static/protocol-mess...

[jakobegger]

Is there any connection state that is not stored in pg_settings? You can check that table to see what settings were overridden in the session.

[kodablah]

I believe what state the protocol is at and things like whether its still has rows in the buffer to be flushed on a query is not present there. But I'm not exactly sure. I'd love to be wrong.