WATCH Statement (Experimental)

Important

This is an experimental feature that may change in backwards-incompatible ways in the future releases.
Enable live views and WATCH query using set allow_experimental_live_view = 1.

  1. WATCH [db.]live_view
  2. [EVENTS]
  3. [LIMIT n]
  4. [FORMAT format]

The WATCH query performs continuous data retrieval from a live view table. Unless the LIMIT clause is specified it provides an infinite stream of query results from a live view.

  1. WATCH [db.]live_view

The virtual _version column in the query result indicates the current result version.

Example:

  1. CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
  2. WATCH lv
  1. ┌───────────────now()─┬─_version─┐
  2. │ 2021-02-21 09:17:21 │        1 │
  3. └─────────────────────┴──────────┘
  4. ┌───────────────now()─┬─_version─┐
  5. │ 2021-02-21 09:17:26 │        2 │
  6. └─────────────────────┴──────────┘
  7. ┌───────────────now()─┬─_version─┐
  8. │ 2021-02-21 09:17:31 │        3 │
  9. └─────────────────────┴──────────┘
  10. ...

By default, the requested data is returned to the client, while in conjunction with INSERT INTO it can be forwarded to a different table.

  1. INSERT INTO [db.]table WATCH [db.]live_view ...

EVENTS Clause

The EVENTS clause can be used to obtain a short form of the WATCH query where instead of the query result you will just get the latest query result version.

  1. WATCH [db.]live_view EVENTS

Example:

  1. CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
  2. WATCH lv EVENTS
  1. ┌─version─┐
  2. │       1 │
  3. └─────────┘
  4. ┌─version─┐
  5. │       2 │
  6. └─────────┘
  7. ...

LIMIT Clause

The LIMIT n clause species the number of updates the WATCH query should wait for before terminating. By default there is no limit on the number of updates and therefore the query will not terminate. The value of 0 indicates that the WATCH query should not wait for any new query results and therefore will return immediately once query is evaluated.

  1. WATCH [db.]live_view LIMIT 1

Example:

  1. CREATE LIVE VIEW lv WITH REFRESH 5 AS SELECT now();
  2. WATCH lv EVENTS LIMIT 1
  1. ┌─version─┐
  2. │       1 │
  3. └─────────┘

FORMAT Clause

The FORMAT clause works the same way as for the SELECT.

Note

The JSONEachRowWithProgress format should be used when watching live view tables over the HTTP interface. The progress messages will be added to the output to keep the long-lived HTTP connection alive until the query result changes. The interval between progress messages is controlled using the live_view_heartbeat_interval setting.