Temporal: an update or destroy as of an instant doesn't carry into later versions

Open
#915 1 comment 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

@hoanle0126 is already working on this.

Since Nov 3, 2026.

  • #916 by @hoanle0126 — open

Assessment

Difficulty
5/5
Estimated time
Over a week
Newbie friendliness
15/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Active
Tech stack
postgresql
Domain
databases

Research direction

Start with the temporal write paths in lib/data_layer.ex (update, bulk update, destroy) and lib/temporal.ex (upsert and the split logic). Reproduce with the Subscription test schema and the three-version timeline in the issue, then confirm that later versions revert after a write as of an instant. Done means the carry-forward semantics from ash-project/ash#3104 hold across update, upsert and destroy, under the concurrency constraints the issue describes.

Written by the indexing model from the issue text.

Description

Problem

This is the Postgres side of ash-project/ash#3104: a temporal update, matched upsert or destroy as of an instant only changes the version valid at that instant, so later versions undo it.

The write is scoped to that one version with valid_at @> as_of (update, bulk update, destroy, upsert). AshPostgres.Temporal then splits only that row. Its moduledoc describes the portion as [as_of, ∞) over the matched versions, but only one version is ever matched.

Reproduction (ash 3.37.1, ash_sql 0.8.4, ash_postgres main @ eb1ffa6, PostgreSQL 18.6)

Uses AshPostgres.Test.Temporal.Subscription from the test suite. The record has three versions; tier is basic in all of them and only seats changes.

Test
defmodule AshPostgres.CarryForwardReproTest do
  use AshPostgres.RepoCase, async: false
  @moduletag :temporal
  @moduletag :postgres_18
  require Ash.Query
  alias AshPostgres.TemporalTestRepo
  alias AshPostgres.Test.Temporal.Subscription

  @jan15 ~U[2026-01-15 00:00:00.000000Z]

  setup do
    TemporalTestRepo.query!("""
    INSERT INTO subscription (id, tier, seats, valid_at) VALUES
      (1, 'basic', 1, tstzrange('2026-01-01','2026-02-01','[)')),
      (1, 'basic', 2, tstzrange('2026-02-01','2026-03-01','[)')),
      (1, 'basic', 3, tstzrange('2026-03-01', NULL,       '[)'))
    """)

    :ok
  end

  defp timeline(label) do
    IO.puts("\n== #{label}")

    for [tier, seats, lo, hi] <-
          TemporalTestRepo.query!(
            "SELECT tier, seats, lower(valid_at)::date::text, upper(valid_at)::date::text FROM subscription WHERE id = 1 ORDER BY lower(valid_at)"
          ).rows do
      IO.puts("  [#{lo}, #{hi || "∞"})  tier=#{tier} seats=#{seats}")
    end
  end

  defp at_jan15,
    do: Subscription |> Ash.Query.filter(id == 1) |> Ash.Query.as_of(@jan15) |> Ash.read_one!()

  test "atomic update" do
    at_jan15()
    |> Ash.Changeset.for_update(:change_tier, %{tier: "pro"})
    |> Ash.Changeset.as_of(@jan15)
    |> Ash.update!()

    timeline("change_tier (atomic) to pro at 2026-01-15")
  end

  test "non-atomic update" do
    at_jan15()
    |> Ash.Changeset.for_update(:change_tier_nonatomic, %{tier: "pro"})
    |> Ash.Changeset.as_of(@jan15)
    |> Ash.update!()

    timeline("change_tier_nonatomic to pro at 2026-01-15")
  end

  test "bulk atomic update" do
    Subscription
    |> Ash.Query.filter(id == 1)
    |> Ash.bulk_update!(:change_tier, %{tier: "pro"}, as_of: @jan15, strategy: :atomic)

    timeline("bulk change_tier to pro at 2026-01-15")
  end

  test "upsert" do
    Subscription
    |> Ash.Changeset.for_create(:create, %{id: 1, tier: "pro", seats: 1})
    |> Ash.Changeset.as_of(@jan15)
    |> Ash.create!(upsert?: true, upsert_fields: [:tier])

    timeline("upsert tier=pro at 2026-01-15")
  end

  test "destroy" do
    at_jan15() |> Ash.Changeset.for_destroy(:expire) |> Ash.Changeset.as_of(@jan15) |> Ash.destroy!()
    timeline("destroy at 2026-01-15")
  end

  test "update now, with a future-dated version" do
    TemporalTestRepo.query!("DELETE FROM subscription WHERE id = 1")

    TemporalTestRepo.query!("""
    INSERT INTO subscription (id, tier, seats, valid_at) VALUES
      (1, 'basic', 1, tstzrange(now() - interval '30 days', now() + interval '30 days', '[)')),
      (1, 'basic', 5, tstzrange(now() + interval '30 days', NULL, '[)'))
    """)

    Subscription
    |> Ash.Query.filter(id == 1)
    |> Ash.read_one!()
    |> Ash.Changeset.for_update(:change_tier, %{tier: "pro"})
    |> Ash.update!()

    timeline("change_tier to pro now, with a version scheduled 30 days out")
  end
end

Output:

== change_tier (atomic) to pro at 2026-01-15
  [2026-01-01, 2026-01-15)  tier=basic seats=1
  [2026-01-15, 2026-02-01)  tier=pro seats=1
  [2026-02-01, 2026-03-01)  tier=basic seats=2     <- back to basic
  [2026-03-01, ∞)  tier=basic seats=3              <- back to basic

== change_tier_nonatomic to pro at 2026-01-15      (same)
== bulk change_tier to pro at 2026-01-15           (same)
== upsert tier=pro at 2026-01-15                   (same)

== destroy at 2026-01-15
  [2026-01-01, 2026-01-15)  tier=basic seats=1
  [2026-02-01, 2026-03-01)  tier=basic seats=2     <- record is back
  [2026-03-01, ∞)  tier=basic seats=3

== change_tier to pro now, with a version scheduled 30 days out
  [2026-10-03, 2026-11-02)  tier=basic seats=1
  [2026-11-02, 2026-12-02)  tier=pro seats=1
  [2026-12-02, ∞)  tier=basic seats=5              <- back to basic

Expected

The semantics are in ash-project/ash#3104. For this data: tier=pro from Jan 15 onward in every update and upsert case, with seats untouched; no rows from Jan 15 on after the destroy; and tier=pro in the scheduled version too.

Suggested solution

Extend the existing split statement rather than adding round trips, so it stays one statement and keeps the lock-first gate that makes it safe under READ COMMITTED. I prototyped this as hand-written SQL in the shape split_portion/5 emits (PostgreSQL 18.6). It gives the expected result for: a column nobody changed later, x → y → x, a gap, two columns with different stops, atomic seats + 1, and destroy with and without a gap.

Only for an instant portion; a range portion keeps today's behaviour:

  1. __keys: the primary keys of the rows the original WHERE matches (joins and filters included).
  2. Widen the gate. __cand and __locked cover the version at t and every later version of those records: valid_at && tstzrange($1, NULL), which the GiST primary key index can use. Lock with ORDER BY <pk>, lower(valid_at) FOR UPDATE, so every writer takes its locks in the same order and lock sets spanning several rows per record can't deadlock.
  3. __after / __stops: a window over those versions, as the snapshot saw them before the write. It flags, per column, each version whose value differs from the previous version's, plus gaps. For each record and column, the stop is the first flagged version after t, or infinity.
  4. __fpo: unchanged, plus new AS "__new" in RETURNING.
  5. __carry: UPDATE the later versions, each column set to the new value while lower(valid_at) is before that column's stop, filtered to rows at least one column reaches. For a destroy it's a DELETE of the later versions before the first gap.
"__keys"   AS (SELECT DISTINCT s0.id <matched>),
"__cand"   AS (SELECT x.ctid FROM t x WHERE x.id IN (SELECT id FROM "__keys")
                 AND x.valid_at && tstzrange($1, NULL)),
"__locked" AS (SELECT x.ctid FROM t x WHERE x.id IN (SELECT id FROM "__keys")
                 AND x.valid_at && tstzrange($1, NULL)
               ORDER BY x.id, lower(x.valid_at) FOR UPDATE OF x),
"__conflict" AS (... unchanged ...),
"__after"  AS (SELECT x.id, lower(x.valid_at) AS lo,
                 lower(x.valid_at) IS DISTINCT FROM lag(upper(x.valid_at)) OVER w AS gap,
                 x.tier IS DISTINCT FROM lag(x.tier) OVER w AS tier_moved    -- one per column
               FROM t x WHERE x.id IN (SELECT id FROM "__keys")
                 AND x.valid_at && tstzrange($1, NULL)
               WINDOW w AS (PARTITION BY x.id ORDER BY lower(x.valid_at))),
"__stops"  AS (SELECT id, coalesce(min(lo) FILTER (WHERE lo > $1 AND (gap OR tier_moved)),
                                   'infinity') AS tier_stop             -- one per column
               FROM "__after" GROUP BY id),
"__fpo"    AS (... unchanged ... RETURNING ..., old AS "__old", new AS "__new"),
"__leftovers" AS (... unchanged ...),
"__carry"  AS (UPDATE t x SET
                 tier = CASE WHEN (f."__new").tier IS DISTINCT FROM (f."__old").tier
                              AND lower(x.valid_at) < st.tier_stop
                             THEN (f."__new").tier ELSE x.tier END,     -- one per column
                 recorded_at = (f."__new").recorded_at
               FROM "__fpo" f JOIN "__stops" st ON st.id = (f."__new").id
               WHERE x.id = (f."__new").id AND lower(x.valid_at) > $1
                 AND (((f."__new").tier IS DISTINCT FROM (f."__old").tier
                       AND lower(x.valid_at) < st.tier_stop) OR ...))

Why it stays safe. The gate's rule becomes "lock every version the statement reads to decide, or writes". Any concurrent change to a later version updates or deletes a row in the locked set (leftovers always come with one), so the ctid comparison catches it and the existing retry reruns the statement. An insert into a gap isn't visible to the lock set, but the carry already stops at that gap in our snapshot, and the inserting upsert doesn't read our values, so the result is the same as running ours first. In the prototype, a session holding an uncommitted update on a later version made the carry statement report conflict and write nothing; the retry saw the commit and stopped the carry at that version.

Details:

  • Which columns. Candidates are the stored columns minus the primary key, the period, generated columns and recorded_at, all known when the statement is built. Which ones actually carry is decided per row by __new IS DISTINCT FROM __old, so nothing has to parse the SET clause. Values come from __new, so atomic expressions and joins (exists, relationships) aren't re-run against later versions.
  • recorded_at on rewritten versions. The sketch sets it to the write's recorded_at, since those versions were rewritten. Leaving it alone is defensible too.
  • Types without an equality operator (json, xml, point) need a cast for IS DISTINCT FROM, chosen from the attribute's storage type, so the build doesn't need an extra query.
  • upsert_group/9: the matched upd CTE needs the same treatment. Key __keys on the primary key, not the upsert keys, since an identity's value can differ across versions.
  • Cost. Every temporal update now scans and locks the record's future versions. With none, __after is one row and __carry matches nothing.
  • New failures. A destroy in the past can now trip PERIOD foreign keys from rows that reference the later versions it removes. A carried value can break an identity's exclusion constraint in a later period. Both should surface as field errors rather than raw constraint errors.
  • Bulk across records. The current __locked has no ORDER BY. Bulk writes across several records may already be able to deadlock today, so the ordered lock would help there too.
Dominant language
Elixir
Stars
191
Forks
186
Avg merge
16h 4m
Merged PRs (30d)
17

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from ash-project/ash_postgres

All issues in ash-project/ash_postgres

Similar issues

More Elixir issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.