9 ms·

As cirego mentioned, we (Materialize) have the TAIL operator, which was built to allow users to subscribe to changes: https://materialize.com/docs/sql/tail/ ht

As cirego mentioned, we (Materialize) have the TAIL operator, which was built to allow users to subscribe to changes: https://materialize.com/docs/sql/tail/ https://materialize.com/docs/sql/tail/

jfim 5y ago
Out of curiosity, how does TAIL behave with updates? For example, say that I have a table foo that has a single row, if that row's contents is updated, would I get two updates (a deletion and an insert)?
- benesch 5y ago
  Yes, exactly. Roughly speaking you'd see something like this: (id, text1, text2, timestamp, diff) ---- (42, before, data, Friday April 23 12:27AM, -1) (42, after, data, Friday April 23 12:27AM, +1)
- cirego 5y ago
  You are correct! Updates are a expressed as a retraction and an insert that happen within the same timestamp. An example may not be necessary but it might also help clarify. Assuming you're using the psql client to run "TAIL WITH (PROGRESS)", the logical grouping for a single update will be a set of rows like the following: ... 1608081358001 f -1 ['Lockal', '4590'] 1608081358001 f 1 ['Epidosis', '4595'] 1608081358001 f -1 ['Matlin', '5220'] 1608081358001 f 1 ['Matlin', '5221'] 1608081359001 t \N ['\\N', '\\N'] ... All of these occur at the same timestamp, meaning that they should be applied atomically to maintain consistency of your dataset. In this case, my query is a top-10 query and Epidosis has now entered the top10 while Lockal has dropped out of the top10. Matlin remains in the top10 but their total has gone from 5220 to 5221. The final example record is produced when you run with PROGRESS enabled and serves as an indicator that 1608081359001 is now closed and no further updates will ever happen at timestamp 1608081359001. I find that this stream of rows is very easy to convert to a data structure "{timestamp, inserts[], deletes[]}" and this, in turn, maps naturally onto reactive APIs, such as React or D3. My blog post, linked above, delves into this in more detail. Hope this explanation helps!
  - jfim 5y ago
    That's a great example, thanks a lot!

As cirego mentioned, we (Materialize) have the TAIL operator, which was built to allow users to subscribe to changes: https://materialize.com/docs/sql/tail/ https://materialize.com/docs/sql/tail/

jfim 5y ago

Out of curiosity, how does TAIL behave with updates? For example, say that I have a table foo that has a single row, if that row's contents is updated, would I get two updates (a deletion and an insert)?

benesch 5y ago
Yes, exactly. Roughly speaking you'd see something like this: (id, text1, text2, timestamp, diff) ---- (42, before, data, Friday April 23 12:27AM, -1) (42, after, data, Friday April 23 12:27AM, +1)
cirego 5y ago
You are correct! Updates are a expressed as a retraction and an insert that happen within the same timestamp. An example may not be necessary but it might also help clarify. Assuming you're using the psql client to run "TAIL WITH (PROGRESS)", the logical grouping for a single update will be a set of rows like the following: ... 1608081358001 f -1 ['Lockal', '4590'] 1608081358001 f 1 ['Epidosis', '4595'] 1608081358001 f -1 ['Matlin', '5220'] 1608081358001 f 1 ['Matlin', '5221'] 1608081359001 t \N ['\\N', '\\N'] ... All of these occur at the same timestamp, meaning that they should be applied atomically to maintain consistency of your dataset. In this case, my query is a top-10 query and Epidosis has now entered the top10 while Lockal has dropped out of the top10. Matlin remains in the top10 but their total has gone from 5220 to 5221. The final example record is produced when you run with PROGRESS enabled and serves as an indicator that 1608081359001 is now closed and no further updates will ever happen at timestamp 1608081359001. I find that this stream of rows is very easy to convert to a data structure "{timestamp, inserts[], deletes[]}" and this, in turn, maps naturally onto reactive APIs, such as React or D3. My blog post, linked above, delves into this in more detail. Hope this explanation helps!
- jfim 5y ago
  That's a great example, thanks a lot!