[Format][FlightRPC] Flight SQL evolution

[lidavidm]

Describe the enhancement requested

From https://github.com/apache/arrow-rs/issues/5731#issuecomment-2133104504

Originally Flight RPC was implemented as a framework wrapping gRPC. This was especially expedient for the C++ implementation. By now it's mostly a weight dragging down Flight users, especially Flight SQL.

If we have the chance to evolve Flight SQL and/or Flight RPC, some changes may include:

• Use a proper gRPC service definition, instead of opaque bytes payloads

Component(s)

FlightRPC, Format

[lidavidm]

Other potential things

• There's a mix of stateful and stateless components, e.g. transactions use explicit handles but sessions are ambient (some of this stems from trying to accommodate JDBC/ODBC more directly)
• The metadata methods are incomplete (e.g there's no procedures, types, etc)
• There's no support for multiple result sets (this is also a limitation of using IPC streams in Flight, this has been complained about before too)
• The underlying IPC stream could use enhancements (e.g. the small result proposal from Micah, which would embed trivial result sets into the initial response)
• Duplication between GetFlightInfo/PollFlightInfo due to evolution over time (though you could argue it makes sense to have both anyways)