Temporal: an update or destroy as of an instant doesn't carry into later versions
Maintainers usually reply within 1 day
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:
__keys: the primary keys of the rows the originalWHEREmatches (joins and filters included).- Widen the gate.
__candand__lockedcover the version attand every later version of those records:valid_at && tstzrange($1, NULL), which the GiST primary key index can use. Lock withORDER 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. __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 aftert, or infinity.__fpo: unchanged, plusnew AS "__new"inRETURNING.__carry:UPDATEthe later versions, each column set to the new value whilelower(valid_at)is before that column's stop, filtered to rows at least one column reaches. For a destroy it's aDELETEof 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 theSETclause. Values come from__new, so atomic expressions and joins (exists, relationships) aren't re-run against later versions. recorded_aton rewritten versions. The sketch sets it to the write'srecorded_at, since those versions were rewritten. Leaving it alone is defensible too.- Types without an equality operator (
json,xml,point) need a cast forIS DISTINCT FROM, chosen from the attribute's storage type, so the build doesn't need an extra query. upsert_group/9: the matchedupdCTE needs the same treatment. Key__keyson 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,
__afteris one row and__carrymatches 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
__lockedhas noORDER 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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from ash-project/ash_postgres
-
has_create_action?/1 excludes manual creates, making the #49 nullability fix inert on AshEvents resourcesMay be free again @Mckay-Hinds claimed this 18 days ago, and no pull request is open. Open
Difficulty 3/5 1-2 days Newbie friendliness 76/100
ash-project/ash_postgres#851 · 9 comments ·
Maintainers usually reply within 1 day
-
enhancement
Difficulty 4/5 3-5 days Newbie friendliness 35/100
ash-project/ash_postgres#650 · 6 comments ·
Maintainers usually reply within 1 day
-
bug
Difficulty 4/5 3-5 days Newbie friendliness 35/100
ash-project/ash_postgres#642 · 5 comments ·
Maintainers usually reply within 1 day
-
enhancement
Difficulty 5/5 Over a week Newbie friendliness 40/100
ash-project/ash_postgres#628 · 1 comment ·
Maintainers usually reply within 1 day
-
enhancement good first issue
Difficulty 4/5 3-5 days Newbie friendliness 48/100
ash-project/ash_postgres#538 · 1 comment ·
Maintainers usually reply within 1 day
All issues in ash-project/ash_postgres
Similar issues
-
@swmansion/popcorn: published .d.ts files lack extensions on relative imports, breaking moduleResolution node16/nodenextPossibly taken @Huyvux12 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
software-mansion/popcorn#929 · 1 comment ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 85/100
ash-project/ash#3127 ·
Maintainers usually reply within 1 day
-
Bug: Table layout and date column are cut off on mobile screens on the Tournaments -> History pageOpenbug
Difficulty 2/5 1-3 hours Newbie friendliness 74/100
hexlet-codebattle/codebattle#2375 ·
-
Bug
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
wintermeyer/vutuv#2388 ·
Maintainers usually reply within 1 day
-
Difficulty 2/5 1-3 hours Newbie friendliness 74/100