TAIL streams updates from a source, table, or view as they occur.
TAIL statement is a more general form of a
statement. While a
SELECT statement computes a relation at a moment in time, a
tail operation computes how a relation changes over time.
TAIL produces a sequence of updates. An update describes either
the insertion or deletion of a row to the relation at a specific time. Taken
together, the updates describes the complete set of changes to a relation, in
order, while the
TAIL is active.
Clients can use
- Power event processors that react to every change to a relation.
- Replicate the complete history of a relation while the
|object_name||The name of the source, table, or view that you want to tail.|
|timestamp_expression||The logical time at which the
WITH option values:
|Option name||Value type||Default||Describes|
||Whether to emit a snapshot of the current state of the relation at the start of the operation. See
||Whether to include detailed progress information. See
TAIL emits a sequence of updates. Each row contains all of the columns of
the tailed relation, prepended with several additional columns that describe
the nature of the update:
Materialize's internal logical timestamp. This will never be less than any
timestamp previously emitted by the same
This column is only present if the
The change in frequency of the row. A positive number indicates that
|Column 1||Varies||The columns from the tailed relation, each as its own column, representing the data that was inserted into or deleted from the relation.|
TAIL will continue to run until cancelled, or until all updates the tailed
item could undergo have been presented. The latter case typically occurs when
tailing constant views (e.g.
CREATE VIEW v AS SELECT 1) or
file sources that were created in non-tailing
tail = false).
Changed in v0.5.1: The timestamp and diff information moved to leading, well-typed columns.
Previously the timestamp and diff information was encoded in a human-readable
string in a trailing
Changed in v0.5.2:
TAIL is now guaranteed to send timestamps in non-decreasing order.
Changed in v0.5.2: Syntax has changed.
WITH SNAPSHOT is now
WITHOUT SNAPSHOT is now
WITH (SNAPSHOT = false).
Changed in v0.5.2: The
PROGRESS option has been added.
AS OF clause specifies the time at which a
TAIL operation begins.
SNAPSHOT below for details on what this means.
If you don’t specify
AS OF explicitly, Materialize will pick a timestamp
- If the tailed relation is materialized, Materialize picks the latest time for which results are computed.
- If the tailed relation is not materialized, Materialize picks time
A given timestamp will be rejected if data it would report has already been
compacted by Materialize. See the
--logical-compaction-window command-line option for
details on Materialize’s compaction policy.
By default, a
TAIL begins by emitting a snapshot of the tailed relation, which
consists of a series of updates describing the contents of the relation at its
AS OF timestamp. After the snapshot,
TAIL emits further updates as
For updates in the snapshot, the
timestamp field will be fast-forwarded to the
AS OF timestamp. For example,
TAIL ... AS OF 21 would present an insert that
occured at time 15 as if it occurred at time 21.
To see only updates after the
AS OF timestamp, specify
WITH (SNAPSHOT = false).
PROGRESS option is specified via
WITH (PROGRESS), an additional
progressed column appears in the output.
false if there may be more rows with the same timestamp.
true if no more timestamps will appear that are strictly less than the
All further columns after
progressed will be
NULL in the
Intuitively, progress messages communicate that no updates have occurred in a given time window. Without explicit progress messages, it is impossible to distinguish between a stall in Materialize and a legimate period of no updates.
Not all timestamps that appear will have a corresponding
For example, the following is a valid sequence of updates:
||1||even more data|
Notice how Materialize did not emit explicit progress messages for timestamps
3. The receipt of the update at timestamp
2 implies that there
are no more updates for timestamp
1, because timestamps are always presented
in non-decreasing order. The receipt of the explicit progress message at
4 implies that there are no more updates for either timestamp
3—but that there may be more data arriving at timestamp
Tailing via the
psql command-line client
In this example, we’ll assume
some_materialized_view has one
COPY (TAIL some_materialized_view) TO STDOUT
1580000000000 1 insert_key 1580000000001 1 will_delete 1580000000003 -1 will_delete 1580000000005 1 will_update_old 1580000000007 -1 will_update_old 1580000000007 1 will_update_new
- Inserting and then deleting
will_update_old, and then updating it to
If you want to see the updates that had occurred in the last 30 seconds, you could run:
TAIL some_materialized_view AS OF now() - '30s'::INTERVAL
If you want timestamp completion messages:
COPY (TAIL some_materialized_view WITH (PROGRESS)) TO STDOUT
1580000000000 f 1 insert_key 1580000000001 t \N \N 1580000000001 f 1 will_delete 1580000000003 f -1 will_delete 1580000000005 t \N \N 1580000000005 f 1 will_update_old 1580000000006 t \N \N 1580000000007 t \N \N 1580000000007 f -1 will_update_old 1580000000007 f 1 will_update_new 1580000000009 t \N \N
Tailing through a driver
TAIL produces rows similar to a
SELECT statement, except that
TAIL may never complete.
Many drivers buffer all results until a query is complete, and so will never return.
COPY TO which is unbuffered by drivers and so is suitable for streaming.
As long as your driver lets you send your own
COPY statement to the running Materialize node, you can
TAIL updates from Materialize anywhere you’d like.
#!/usr/bin/env python3 import sys import psycopg2 def main(): dsn = 'postgresql://localhost:6875/materialize?sslmode=disable' conn = psycopg2.connect(dsn) with conn.cursor() as cursor: cursor.copy_expert("COPY (TAIL some_materialized_view) TO STDOUT", sys.stdout) if __name__ == '__main__': main()
This will then stream the same output we saw above to
stdout, though you could
obviously do whatever you like with the output from this point.
If your driver does support unbuffered result streaming, then there is no need to use