At Mesosphere/D2iQ we used psycopg2-backed sqlalchemy to interact with CockroachDB.
Daniele, one point that I'd like to get your opinion on, and that's maybe worth considering for API developent around psychopg3: we found it difficult to implement timeout control for a transaction context. Consider application code waiting for a transaction to complete. The calling thread is in a blocking recv() system call, waiting for the DB to return some bytes over TCP. My claim is that it should be easy to error out from here after a given amount of time if the database (for whichever reason) does not respond in a timely fashion, or never at all (a scenario we sometimes ran into with early versions of CockroachDB). Certainly, ideally the database always responds timely or has its internal timeout mechanisms working well. But for building robust systems, I believe it would be quite advantageous for the database client API to expose TCP recv() timeout control. I think when we looked at the details back then we found that it's libpq itself which didn't quite expose the socket configuration aspects we needed, but it's been a while.
On the topic of doing "async I/O" against a database, I would love to share Mike Bayer's article "Asynchronous Python and Databases" from 2015: https://techspot.zzzeek.org/2015/02/15/asynchronous-python-a... -- I think it's still highly relevant (not just to the Python ecosystem) and I think it's pure gold. Thanks, Mike!
Hi! Much of the difficulty comes from using blocking libpq calls in psycopg2. I'm thinking to avoid them altogether in psycopg3 and only use async calls with Python in charge of blocking.
Note that you can obtain a similar result in psycopg2 by going in green mode and using select as wait callback (see https://www.psycopg.org/docs/extras.html#psycopg2.extras.wai...). This trick enables for instance stopping long-running queries using ctrl-c.
You can also register a timeout in the server to require to terminate a query after a timeout. I guess they are two complementary approaches. In the first case you don't know the state of the connection anymore: maybe it should be cancelled or discarded, we should work out what to do with it. A server timeout is easier to recover from: just rollback and off you go again.
Daniele, one point that I'd like to get your opinion on, and that's maybe worth considering for API developent around psychopg3: we found it difficult to implement timeout control for a transaction context. Consider application code waiting for a transaction to complete. The calling thread is in a blocking recv() system call, waiting for the DB to return some bytes over TCP. My claim is that it should be easy to error out from here after a given amount of time if the database (for whichever reason) does not respond in a timely fashion, or never at all (a scenario we sometimes ran into with early versions of CockroachDB). Certainly, ideally the database always responds timely or has its internal timeout mechanisms working well. But for building robust systems, I believe it would be quite advantageous for the database client API to expose TCP recv() timeout control. I think when we looked at the details back then we found that it's libpq itself which didn't quite expose the socket configuration aspects we needed, but it's been a while.
On the topic of doing "async I/O" against a database, I would love to share Mike Bayer's article "Asynchronous Python and Databases" from 2015: https://techspot.zzzeek.org/2015/02/15/asynchronous-python-a... -- I think it's still highly relevant (not just to the Python ecosystem) and I think it's pure gold. Thanks, Mike!