7 ms·

I use PostgreSQL and MS SQL Server. I love Postgres. There are some things that SQL Server does differently that I would love to see supported in Postgres: * P

I use PostgreSQL and MS SQL Server. I love Postgres. There are some things that SQL Server does differently that I would love to see supported in Postgres: * Protocol: (a) no wire level named parameters support; everything must be by index. (b) Binary vs text is is not great, and binary protocol details is mostly "see source" (c) no support for inline cancellation: to cancel a query client can't signal on current TCP connection, the client must open or use a different connection to kill an existing query, this is sometimes okay, but if you are behind a load balancer or pooler this can end up being a huge pain. * Multiple Result Sets from a single query without hacks. While you can union together results with similar columns or use cursors, these are hacks and work only in specific cases. If I have two selects, return two result sets. Please. * Protocol level choosing of language. Yes, you can program in any language, but submitting an arbitrary query in any language is a pain and requires creating an anonymous function, which is a further pain if your ad-hoc query has parameters. I would love a protocol level flag that allows you to specify: "SQL", "PG/plSQL", "cool-lang-1" and have it execute the start of the text as that. I do love Postgres recently added Procs! Yay!

jeffdavis 7y ago
That's an interesting list. Can you give more context about how you would like to use these features?
- kardianos 7y ago
  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.
  - jeffdavis 7y ago
    "better document binary and add an intent to remove text data types" For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest would go through text. That also works better with the extensible type system, where the client driver will never know about all data types the user might want to use. And it works better for things like psql, which need a textual representation. The main problem with binary is that the "optimize a few columns as binary" can't be done entirely in the driver. The driver knows which types it can parse, but it doesn't know what types a given query will return. The application programmer may know what types the query will return, in which case they can specify to return them in binary if they know which ones are supported by the client driver, but that's ugly (and in libpq, it only supports all-binary or all-text). Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it). "Not having an inline cancellation (in same TCP/IP connection)" This is related to another problem, which is that while a query is executing it does not bother to touch the socket at all. That means that the client can disconnect and the query can keep running for a while, which is obviously useless. I tried fixing this at one point but there were a couple problems and I didn't follow through. Detecting client disconnect probably should be done though. Supporting cancellation would be trickier than just looking for a client disconnect, because there's potentially a lot of data on the socket (pipelined queries), so it would need to read all the messages coming in looking for a cancellation, and would need to save it all somewhere in case there is no cancellation. I think this would screw up query pipelining, because new queries could be coming in faster than they are being executed, and that would lead to continuous memory growth (from all the saved socket data). So it looks like out-of-band is the only way cancellation will really work, unless I'm missing something. "Multiple Result Sets... you just call the database alot" Do pipelined queries help at all here? "Named parameters are extremely useful for building up query parameters" +1. No argument there. "Protocol level language selection" I'm still trying to wrap my head around this idea. I think I understand what you are saying and it sounds cool. There are some weird implications I'm sure, but it sounds like it's worth exploring.
egeozcan 7y ago
I'd love to see the MERGE statement from MSSQL in other databases. Wonderful tool for integrations.
- kardianos 7y ago
  I've had to personally avoid MERGE statements due to implementation problems in MSSQLServer. One system just updated to 2019, so maybe it is better now. I also have to lookup the syntax each time, but that might be just me.
  - egeozcan 7y ago
    In some projects the MERGE helped me a lot and in my previous job, all of our integration scripts were merge based. You just learn to avoid the pitfalls, but you're right that it does have some: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... I'm sure if postgres had it, it wouldn't have had most of those problems.
- manigandham 7y ago
  Postgres has UPSERT (INSERT INTO ... ON CONFLICT DO ... ) I wish MSSQL had this.
  - electrotype 7y ago
    But this doesn't deal with DELETEs of a true MERGE statement
    - manigandham 7y ago
      No, it's not a replacement. It's just a very useful feature that I wish all DBs had.
- nickpeterson 7y ago
  Merge statement always comes with a bunch of caveats from MSSQL experts, has that changed recently?
SigmundA 7y ago
Good list. Also true indexed organized table aka real clustered indexes. Oh and real cross connection query plan caching, prepared statements are only for the connection and must be explicitly used. No need to use prepared statements in MSSQL since the 90's
- AaronFriel 7y ago
  Another thing I'd add is a quasi-sorted uuid generator built-in. Real clustered indexes need approximately sorted UUIDs - which could be version 1 UUIDs with an artificial, per-database MAC address, or they could be something more esoteric such as Twitter's snowflake ID generator. Using UUIDs for PKs is fine and dandy but clustered indexes and type 4 UUIDs do not play well. Many MS SQL users discover this the hard way when their toy database suddenly has order of magnitudes more rows.
  - yyyk 7y ago
    That's what NEWSEQUENTIALID() is for in MS SQL.

jeffdavis 7y ago

That's an interesting list. Can you give more context about how you would like to use these features?

kardianos 7y ago
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.
- jeffdavis 7y ago
  "better document binary and add an intent to remove text data types" For all-binary, the client would have to know about all data types and how to represent them in the host language. But that seems clunky. Consider NUMERIC vs. float vs. int4 vs int8: should the client really know how to parse all of those from binary? It makes more sense to optimize a few common data types to be transferred as binary, and the rest would go through text. That also works better with the extensible type system, where the client driver will never know about all data types the user might want to use. And it works better for things like psql, which need a textual representation. The main problem with binary is that the "optimize a few columns as binary" can't be done entirely in the driver. The driver knows which types it can parse, but it doesn't know what types a given query will return. The application programmer may know what types the query will return, in which case they can specify to return them in binary if they know which ones are supported by the client driver, but that's ugly (and in libpq, it only supports all-binary or all-text). Postgres could know the data types, but that means that the driver would need to first prepare the query (which is sometimes a good idea anyway, but other times the round trip isn't worth it). "Not having an inline cancellation (in same TCP/IP connection)" This is related to another problem, which is that while a query is executing it does not bother to touch the socket at all. That means that the client can disconnect and the query can keep running for a while, which is obviously useless. I tried fixing this at one point but there were a couple problems and I didn't follow through. Detecting client disconnect probably should be done though. Supporting cancellation would be trickier than just looking for a client disconnect, because there's potentially a lot of data on the socket (pipelined queries), so it would need to read all the messages coming in looking for a cancellation, and would need to save it all somewhere in case there is no cancellation. I think this would screw up query pipelining, because new queries could be coming in faster than they are being executed, and that would lead to continuous memory growth (from all the saved socket data). So it looks like out-of-band is the only way cancellation will really work, unless I'm missing something. "Multiple Result Sets... you just call the database alot" Do pipelined queries help at all here? "Named parameters are extremely useful for building up query parameters" +1. No argument there. "Protocol level language selection" I'm still trying to wrap my head around this idea. I think I understand what you are saying and it sounds cool. There are some weird implications I'm sure, but it sounds like it's worth exploring.

egeozcan 7y ago

I'd love to see the MERGE statement from MSSQL in other databases. Wonderful tool for integrations.

kardianos 7y ago
I've had to personally avoid MERGE statements due to implementation problems in MSSQLServer. One system just updated to 2019, so maybe it is better now. I also have to lookup the syntax each time, but that might be just me.
- egeozcan 7y ago
  In some projects the MERGE helped me a lot and in my previous job, all of our integration scripts were merge based. You just learn to avoid the pitfalls, but you're right that it does have some: https://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/ https://www.mssqltips.com/sqlservertip/3074/use-caution-with... I'm sure if postgres had it, it wouldn't have had most of those problems.
manigandham 7y ago
Postgres has UPSERT (INSERT INTO ... ON CONFLICT DO ... ) I wish MSSQL had this.
- electrotype 7y ago
  But this doesn't deal with DELETEs of a true MERGE statement
  - manigandham 7y ago
    No, it's not a replacement. It's just a very useful feature that I wish all DBs had.
nickpeterson 7y ago
Merge statement always comes with a bunch of caveats from MSSQL experts, has that changed recently?

SigmundA 7y ago

Good list. Also true indexed organized table aka real clustered indexes. Oh and real cross connection query plan caching, prepared statements are only for the connection and must be explicitly used. No need to use prepared statements in MSSQL since the 90's

AaronFriel 7y ago
Another thing I'd add is a quasi-sorted uuid generator built-in. Real clustered indexes need approximately sorted UUIDs - which could be version 1 UUIDs with an artificial, per-database MAC address, or they could be something more esoteric such as Twitter's snowflake ID generator. Using UUIDs for PKs is fine and dandy but clustered indexes and type 4 UUIDs do not play well. Many MS SQL users discover this the hard way when their toy database suddenly has order of magnitudes more rows.
- yyyk 7y ago
  That's what NEWSEQUENTIALID() is for in MS SQL.