Relate given transaction to affected documents

Users
1

Is there a way to relate a given transaction to documents affected by said transaction (e.g. by transaction ID)?

2

Hey @stef you can use CURRENT_TIMESTAMP within a transaction, which will exactly match the system time of that transaction, e.g. see XTDB Play

You can then use that system timestamp to join across as many tables as needed to find all affected documents (rows) based on the xt$system_from column. You can enumerate the tables using INFORMATION_SCHEMA, e.g. SELECT * FROM INFORMATION_SCHEMA.TABLES

1 Like
3

Thanks!

Figured the transaction timestamp might be one way.

1 Like
4

A conversation on the Clojurians Slack just prompted me to write up what the SQL might look like (once you’ve already enumerated the necessary tables):

WITH txs_filtered AS (
    SELECT _system_from
    FROM xt.txs
    WHERE _id = 0
)
SELECT
    NEST_MANY(SELECT * FROM docs WHERE docs._system_from IN (SELECT _system_from FROM txs_filtered)) AS rows_changed,
    'docs' AS table_name
UNION ALL
SELECT
    NEST_MANY(SELECT * FROM docs2 WHERE docs2._system_from IN (SELECT _system_from FROM txs_filtered)) AS rows_changed,
    'docs2' AS table_name
UNION ALL
SELECT
    NEST_MANY(SELECT * FROM docs3 WHERE docs3._system_from IN (SELECT _system_from FROM txs_filtered)) AS rows_changed,
    'docs3' AS table_name;

play

1 Like