Know when rows are ready for select?

Community
1

Hi, I’m here again…
When I insert some rows and immediately after I do a select, it return the rows without the last insert.
If I wait 1 seconds before doing the select the result is correct.
Is there any way to know when rows are ready for select?
Or force a flush like PostgreSQL’s “ALTER SYSTEM FLUSH”?

The insert performs both insertions and changes to existing rows.

2

Hi @BepTheWolf ,

Transactions are applied asynchronously. When you insert data, it is first committed to WAL. Then a separate job applies the WAL to the table files.

wal_tables() gives you the writerTxn and sequencerTxn numbers for your table.

  • writerTxn is the latest visible txn.
  • sequencerTxn is the latest commited txn.

To guarantee you can read the data, you need ensure the writerTxn number is higher than a sequencerTxn number you check after a successful insert.

In practice, this all happens <100ms, as long as your table is keeping up with the write rate, but this guarantees a ‘minimum read latency’, rather than just delaying reads by 30 seconds and calling it a day.

You can orchestrate this in an async-batch system using queues.

Essentially, one thread performs the polling, and stores the numbers in shared variables. This might happen on 250ms ‘ticks’.

The writer thread inserts data, and then gets the sequencerTxn from the next tick.

The reader thread is given a query and the sequencerTxn number of the data it needs to read. Then it waits until writerTxn exceeds this number, before sending the query and returning the results.

Ideally, we would return the txn number on the insert, and allow you to send it with a query. Then the query is either rejected, or hangs for a timeout period until the txn is visible.

We just haven’t written the public API for it yet. But if you are sufficiently motivated, we do take open source contributions!

1 Like
3

The wal_tables() function seems like a good solution for my tests.

Should the value of writerTnx be greater, or greater/equal to sequencerTnx?

4

For a particular table, it is always the case that writerTxn <= sequencerTxn.

sequencerTxn tells you how many transactions are committed (inserted to database).
writerTxn tells you how many transactions are applied (visible for read).

  • INSERT data A
  • Tick wal_tables() and assign sequencerTxn_a to the insert.
  • SELECT data A query tagged with sequencerTxn_a.
  • Wait until tick has passed.
  • Tick wal_tables() and read writerTxn_b.
  • If writerTxn_b >= sequencerTxn_a then run the query.
  • Otherwise wait for another tick.
  • Repeat.

You can get more granular data from the sys.telemetry_wal table, which has deeper info on when things happened, how long they took. This is the table that feeds the metrics charts on the web console.

But wal_tables() is simplest to start with!

1 Like