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

Aperta
#915 1 commento 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 1 giorno

@hoanle0126 ci sta già lavorando.

Dal 3/11/2026.

  • #916 di @hoanle0126 — aperta

Valutazione

Difficoltà
5/5
Tempo stimato
Più di una settimana
Idoneità per principianti
15/100
Tipo di issue
Bug
Chiarezza
Abbastanza chiara
Stato di attività
Attiva
Stack tecnologico
postgresql
Ambito
databases

Direzione di ricerca

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.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

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.
Lingua principale
Elixir
Stelle
191
Fork
186
Merge medio
16h 4m
PR unite (30g)
17

Preparare l'ambiente

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di ash-project/ash_postgres

Tutte le issue di ash-project/ash_postgres

Issue simili

Altre issue su Elixir

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.