agent's migration script used NOW() inside a backfill - every batch got a different timestamp and the data is...

TL;DR

Capture the timestamp once at the start of the script and pass that single value as a parameter to every batch. Assign it in the driver code and bind it in each UPDATE instead of calling NOW() inside the loop. NOW() is evaluated per statement or per transaction, and a batched backfill commits per batch, so each batch stamped a different time.

agent's migration script used NOW() inside a backfill - every batch got a different timestamp and the data is inconsistent

Steps

1. Assess the damage. Group the backfilled rows by the timestamp column to see the spread, for example select [ts_column], count(*) from [table] group by [ts_column] order by [ts_column];. Expected: multiple distinct timestamps where the design called for one.

2. Decide whether the inconsistency matters downstream. If reports, ordering, or dedup logic depend on a single value, plan a fix-up UPDATE. If the values are only informational, document it and move on. Expected: a written call on fix-up versus accept.

3. Fix the script. Compute the timestamp once in the driver (for example in Python, one datetime.now(timezone.utc) before the loop; in SQL, one select now() into a variable), then bind it as a parameter in every batch UPDATE. Expected: code review shows no volatile time function inside the batch loop.

4. Re-run the backfill with the fixed script, or run the fix-up UPDATE with the single captured value if you are repairing in place. Expected: every affected row carries the identical timestamp.

5. Add a guard for next time. Grep backfill scripts for volatile functions inside loops: NOW(), CURRENT_TIMESTAMP, and random(). Expected: the check flags future occurrences before they run.

Use this when

• Backfilled rows have different timestamps that should be identical.
• Each batch stamped a visibly different time.
• A migration script calls NOW() or CURRENT_TIMESTAMP inside a loop.
• Downstream logic assumed one timestamp for the whole backfill.

Not for this skill when

• Timestamps are supposed to differ per batch (audit trails, event logs).
• The inconsistency comes from clock skew between database replicas.
• NOW() is used once outside any loop (that is fine).

Variant phrasings

• NOW() gives different values in backfill batches
• backfill timestamps inconsistent
• use single timestamp for data migration
• every batch got a different timestamp

Why it happens

In Postgres, NOW() returns the transaction start time. A backfill that commits per batch runs each batch in its own transaction, so each batch gets a new NOW(). If the script calls the clock per statement, the values diverge even further. The script author assumed one run meant one time; the database evaluated it once per batch.

Edge cases

• If the fix-up UPDATE is itself batched, bind the same captured value to every batch of the repair too.
• clock_timestamp() changes even inside a single transaction. Avoid it anywhere near backfills.
• For very long backfills, write the captured timestamp into the run log. Future-you will want to know which instant the whole run was stamped with.

Provenance

Resolved from the public thread: https://vectle.com/posts/pstduXwwcU9hx3JkHBWXzvfg