Sure. I have two roles: database driver/client maintainer and database based application developer and maintainer.
As a database Driver/Client developer:
* Having two protocols/data types that two the same thing (text / binary) isn't the end of the world, but it just adds confusion. Also it adds complexity for a different server/proxy/pool implementation. Recommendation: better document binary and add an intent to remove text data types.
* Not having an inline cancellation (in same TCP/IP connection) means cancellation isn't supported by many drivers, and even when it is, there are many edge cases were it stops working. Each client implementation has to work around this.
As an application developer, I typically see three patterns emerge:
1. Put SQL on SQL platform: stored functions / procs. Application only calls stored functions / procs.
2. Put SQL on Application platform: simple stored SQL text, query builders.
3. Make Application dump and use simple CRUD queries and put logic fully in application.
I typically find myself in category (2), though I have no beef with (1). Benefits of (2) include: (a) easier to create dynamic search criteria and (b) use a single source of schema truth for application and database.
* Multiple Result Sets:
- (A) The screens I make often have a dynamic column set. To accomplish this I may return three result sets: row list, column list, and field list. This works for my screens and print XLSX sheets that can auto-pivot the data in, while all the data transfer columns can be statically known. and pre-declared. This allows me to edit a pivoted table because each cell knows the origin row.
- (B) Any reasonable amount of work in SQL may be long-ish (300-1000+) lines of SQL. There are often many intermediate steps and temp tables. Without Multiple Result Sets, it is difficult to efficiently return the data when there are often multiple arities and columns sets that are all relevant for analysis or work. So if I take a fully application centric view (more like application mindset (3)), you just call the database alot. But if you are more database server centric (1) or (2), this can pose a real problem as complexity of the business problem increases. (I'm aware of escape hatches, but I'm talking about strait forward development without resorting to alternative coding.)
- (C) Named parameters are extremely useful for building up query parameters in the application (development model (2)). You can specify a query where-clause snippet, the parameter name and the have the system compose it for you that is impossible with ordinal queries. A driver or shim can indeed use text replacement, but that (a) adds implementation cost and (b) server computation / allocation cost, (c) mental overhead when you see trace the query on the server. Further more it composes poorly with stored functions and procs on the server (in my opinion). It is again not insurmountable, but it is another thing that adds friction. Lastly, when you have a query that takes over 30 or 50 parameters, you must use named parameters; ordinal positioning is too error prone at scale.
- (D) Protocol level language selection. PostgreSQL always starts execution in plain SQL context. If you always execute functions as PL/pgSQL it is just extra overhead. In addition, running ad-hoc PL/pgSQL with named parameters isn't the most easy thing. It is possible, just not easy. This feature plus named parameters so by the time I'm writing my query, I know (a) that I have all my sent parameters available to me bound to names and (b) the first character I type is in the language I want.
The combination of these features would make applications developed in model (2) go from rather hard to extremely easy. It would also make other development modes easier I would contend as well.