Hacker News Reader

Comment by mikewarot | Hacker News Reader

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

View on HN

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.

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.

And Bigquery has continuous queries: https://docs.cloud.google.com/bigquery/docs/continuous-queri...

And many databases have triggers. And with postgres you can combine triggers with NOTIFY to push changes downstream.

But yeah, I feel like most databases are way behind Oracle on this feature and it's so so useful.

Reply on news.ycombinator.com