more than just R/W splitting ?

[Laurentiu Badea]

This may a bit off because I haven't looked too hard at the connection handling examples, but this problem doesn't appear to be limited to R/W splitting if the proxy reuses connections across different clients. Any persistent connection, even READ, where state has been changed, is technically limited to the original client and cannot be reused by another.

Keeping state locally can help with R/W splitting where we need to match the same kind of READ and WRITE connections, or to create a new WRITE connection by duplicating the READ settings.

However, if changing state is common practice in the application, that in turn makes connection caching rather useless, as each new connection would not match any of the existing (modified) ones maintained by the proxy. You could get around that with support from the application itself (inserting various comments in the SQL) or maybe deferring the backend connection until an actual SQL query is made, at which point the client has hopefully made any state changes it needed to do (which the proxy has buffered), so it can be compared against existing connections. But I'm not sure if this is ideal or even doable since some commands require feedback from the backend.

I hope this makes sense, it's been a long day.