4 ms·

Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results b

Yes. I don't see the harm in the PostgreSQL wire protocol becoming a defacto standard for sending SQL statements over a network connection and getting results back. SQL itself is only a vague standard these days, as almost every single database has its own dialect. The only ones that are the same are MariaDB and MySQL... and that doesn't count. [1] And I don't see this situation changing in the medium term, or ever, really. [1] ... because the first is the fork of the second.

unilynx 5y ago
This. I’ll take the pg protocol over OCI and especially ODBC any day.
clhodapp 5y ago
There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.
- ansible 5y ago
  I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
  - clhodapp 5y ago
    It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
- amitlan 5y ago
  If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....
  - clhodapp 5y ago
    Interesting! I was not aware of this, thanks for sharing. I'll have to look into it in detail to determine whether it's actually pipelining or multiplexing because pipelining is still problematic, since it's subject to head-of-line blocking (one expensive request blocks everyone from getting their responses until it's completed).
    - billywhizz 5y ago
      it's not multiplexing afaik. you will still have to process commands in the order they were put on the wire.
- giraffe_lady 5y ago
  You're gonna have some similar thing no matter what gets settled on, unless there's a flawless protocol out there but those are pretty rare in my experience. I think the comparison is more like "is having an ad-hoc semi-standard wire protocol built around the postgres one better than having none at all?" and I think the answer is yes. But it's not like there's an authority making this choice for everyone. If you think something else is better then use it in your tools and push for it. Postgres is ancient, open source, widely used and adapted for many different use cases. I think those all make it hard to beat in just a practical "worse is better" type way, but I also think people saying "no. we can do better" is good too, esp when they demonstrate it.
  - clhodapp 5y ago
    I agree with everything you said! I was reacting to "I don't see the harm" specifically. Ultimately, I think that with a few improvements to the Postgres protocol, settling on it wouldn't even be a "worse is better" outcome.
blacksqr 5y ago
My understanding is that SQLite tries to maintain compatibility with Postgres in the subset of SQL that it supports. So if this program reaches a reasonable level of maturity I would love to be able to use it with pre-existing products that were designed to use Postgres as a backend, to substitute SQLite as a drop-in replacement. One might hope that this could work for products that don't use Postgres's more cutting-edge features.
- lukeschlather 5y ago
  SQLite's lack of type safety makes me hesitate to use it as a drop-in replacement for Postgres.
  - benbjohnson 5y ago
    I wouldn't use SQLite as a drop-in replacement either but, in case you haven't seen it, SQLite did add "strict mode" for better enforcement of type safety: https://sqlite.org/src/wiki?name=StrictMode https://sqlite.org/src/wiki?name=StrictMode
  - simonw 5y ago
    They fixed that a couple of versions ago: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html

unilynx 5y ago

This. I’ll take the pg protocol over OCI and especially ODBC any day.

clhodapp 5y ago

There's a small amount of harm because Postgres doesn't currently support request multiplexing and so its wire protocol doesn't have any facility for it. This pushes other databases to inherit one of Postgres's few true weak points.

ansible 5y ago
I take it that opening multiple database connections to the same server is not usually viable? I realize there are usually limits to open sockets / file descriptors and such, and that multiple connections will consume more memory on each end. Are there problems beyond that?
- clhodapp 5y ago
  It's usually (but not always) viable and it's ultimately what one ends up doing. You're right about the drawbacks. One additional drawback is the setup time for opening new connections (network round-trip, re-authenticating, etc.).
amitlan 5y ago
If I’m not wrong, I think this situation has changed with the latest Postgres release: https://www.postgresql.org/docs/current/libpq-pipeline-mode.html https://www.postgresql.org/docs/current/libpq-pipeline-mode....
- clhodapp 5y ago
  Interesting! I was not aware of this, thanks for sharing. I'll have to look into it in detail to determine whether it's actually pipelining or multiplexing because pipelining is still problematic, since it's subject to head-of-line blocking (one expensive request blocks everyone from getting their responses until it's completed).
  - billywhizz 5y ago
    it's not multiplexing afaik. you will still have to process commands in the order they were put on the wire.
giraffe_lady 5y ago
You're gonna have some similar thing no matter what gets settled on, unless there's a flawless protocol out there but those are pretty rare in my experience. I think the comparison is more like "is having an ad-hoc semi-standard wire protocol built around the postgres one better than having none at all?" and I think the answer is yes. But it's not like there's an authority making this choice for everyone. If you think something else is better then use it in your tools and push for it. Postgres is ancient, open source, widely used and adapted for many different use cases. I think those all make it hard to beat in just a practical "worse is better" type way, but I also think people saying "no. we can do better" is good too, esp when they demonstrate it.
- clhodapp 5y ago
  I agree with everything you said! I was reacting to "I don't see the harm" specifically. Ultimately, I think that with a few improvements to the Postgres protocol, settling on it wouldn't even be a "worse is better" outcome.

blacksqr 5y ago

My understanding is that SQLite tries to maintain compatibility with Postgres in the subset of SQL that it supports. So if this program reaches a reasonable level of maturity I would love to be able to use it with pre-existing products that were designed to use Postgres as a backend, to substitute SQLite as a drop-in replacement. One might hope that this could work for products that don't use Postgres's more cutting-edge features.

lukeschlather 5y ago
SQLite's lack of type safety makes me hesitate to use it as a drop-in replacement for Postgres.
- benbjohnson 5y ago
  I wouldn't use SQLite as a drop-in replacement either but, in case you haven't seen it, SQLite did add "strict mode" for better enforcement of type safety: https://sqlite.org/src/wiki?name=StrictMode https://sqlite.org/src/wiki?name=StrictMode
- simonw 5y ago
  They fixed that a couple of versions ago: https://www.sqlite.org/stricttables.html https://www.sqlite.org/stricttables.html