I recently spent a bit of time messing around with the Postgres wire protocol. I’d like to build a library that lets any application accept connections as if it were a Postgres database; many database clients and databases speak the Postgres wire protocol, and it seems like a practical way to expose tabular data over the network.
Implementing the protocol isn’t too bad; the docs aren’t great but the protocol itself is relatively straightforward. Unfortunately, client behaviour seems harder to accommodate. Clients can issue arbitrarily complex SQL queries to the database to inspect its schema, and sometimes they expect accurate answers. For example, Npgsql issues this beast and then disconnects if it gets an invalid response.
So I’m stuck with a question: how far should I go to support legitimate client behaviour outside of the protocol? The easiest approach for now is to test with a few popular clients and hardcode responses for any queries that they need implemented, but that will leave a long tail of unsupported clients. On the other end of the complexity scale, I could try running schema queries on an in-memory SQLite instance with database objects that mimic Postgres (certainly a massive yak shave, but tempting nonetheless).
I think my next step will be to dig through the source of a few Postgres-compatible databases to see how they solve this.