Comment by mikewarot
3 hours ago
My ask is 15 years old[1], a live SQL extension. Allow a query to be a subscription to a database, so any updates get streamed as deltas to a listening client. There were a ton of times in my time using SQL where the same query is run over and over, just to get/handle that delta.
Wouldn't it be a lot more efficient to just work that way in the first place?
[1] http://livesql.org/ <--- just a few paragraphs of text from 2011
BTW, Oracle supports this under the name "continuous query notification".
https://docs.oracle.com/en/database/oracle/oracle-database/2...
You can get callbacks from the driver as query results change, or have notifications be sent to stored procedures, or posted to a message queue (and from there turned into web hooks etc). The notification comes with info about the deltas.
The main issue with it is that the queries it can monitor live are a subset of all queries. It's really more like using SQL to select database cells to watch, than propagating changes through arbitrary query plans. For example, it can't handle a SELECT COUNT(*) FROM statement. Obviously you can use it as a trigger for re-running more advanced queries though.
Sounds like you want to read the log file to be honest.
Snowflake has STREAM which can be crated on a view. It also has Dynamic Tables, from which you read a delta using STREAM or using row timestamp.
SQL server has Query Notification.
You can also read from debezium or other cdc, but thats more like table change than query result change.
pg logical replication is close to this. but ideally you want incremental query updates, which I believe Materialize provides.
but yes, I agree this is quite often what one wants, and would remove a lot of grot from the client