How to implement RETURNING

[refset]

RETURNING is a SQL concept used in Postgres, let's take a look at how it applies to XTDB...

From the Postgres docs:

Sometimes it is useful to obtain data from modified rows while they are being manipulated. The INSERT, UPDATE, DELETE, and MERGE commands all have an optional RETURNING clause that supports this. Use of RETURNING avoids performing an extra database query to collect the data, and is especially valuable when it would otherwise be difficult to identify the modified rows reliably.

Borrowing the RETURNING example from that page:

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99
  RETURNING name, price AS new_price;

Let's try to run it in XT Play - oh - the RETURNING is ignored?!

This is XTDB working as intended, since RETURNING is not possible to implement correctly due to the async nature of transactions. Despite looking like and behaving like Postgres in many ways, 'DML' statements sent to XTDB are never actually interactive.

You also can't run a SELECT at the end of a transaction containing DML (writes), but that also counts as 'interactive'.

So then, how can you return some output of a transaction back to the client? You have a couple of options:

1. Write the data you want to return to the client into the database also, then you can query it as normal data. The main downside is that you will consume precious write throughput and bloat your database with (potentially) worthless data. This option isn't the topic of this post.
2. Use SNAPSHOT_TIME to read the data needed as-of the state of the database immediately after the transaction finished (and thereby ignore changes from subsequent transactions):

UPDATE products SET price = price * 1.10
  WHERE price <= 99.99;

-- (later)
SETTING SNAPSHOT_TIME = TIMESTAMP '2025-05-15Z'
  SELECT * FROM products;

Play

Ah, but how do you know the exact TIMESTAMP of the last transaction you just wrote? For that we have a session variable (aka "run-time parameter") called WATERMARK, which is the latest transaction ID observed by the client.

Currently you need to run a separate query to resolve a timestamp from the watermark ID, before passing it into SNAPSHOT_TIME, such that you have 3 'transactions':

SHOW WATERMARK;

SELECT system_time FROM xt.txs WHERE _id = ?;

SETTING SNAPSHOT_TIME = ?
  SELECT * FROM products;

Play

This will ensure you don't see later transactions.

However, the journey isn't quite complete, because the original RETURNING statement would only return values for the modified entries. Luckily, being an immutable database, we can trivially use _system_from for this:

SETTING SNAPSHOT_TIME = ?
  SELECT name, price AS new_price FROM products WHERE _system_from = ?;

(i.e. repeating the same timestamp argument for both parameter slots)

What do you think?

Bonus Thoughts

1. The SHOW WATERMARK; step could ideally be combined into the timestamp resolution step via current_setting, i.e. SELECT system_time FROM xt.txs WHERE _id = current_setting('WATERMARK'); - but `current_setting is not properly implemented/supported yet.