Packages
ash_postgres
2.14.2
2.15.1
2.15.0
2.14.5
retired
2.14.4
retired
2.14.3
2.14.2
2.14.1
2.14.0
retired
2.13.1
2.13.0
2.12.0
2.11.0
2.10.0
2.9.1
2.9.0
2.8.0
2.7.0
2.6.32
2.6.31
2.6.30
2.6.29
2.6.28
2.6.27
2.6.26
2.6.25
2.6.24
2.6.23
2.6.22
2.6.21
2.6.20
2.6.19
2.6.18
2.6.17
2.6.16
2.6.15
2.6.14
2.6.13
2.6.12
2.6.11
2.6.10
2.6.9
2.6.8
2.6.7
2.6.6
2.6.5
2.6.4
2.6.3
2.6.2
2.6.1
2.6.0
2.5.22
2.5.21
2.5.20
2.5.19
2.5.18
2.5.17
2.5.16
2.5.15
2.5.14
2.5.13
2.5.12
2.5.11
2.5.10
2.5.9
2.5.8
2.5.7
2.5.6
2.5.5
2.5.4
2.5.3
2.5.2
2.5.1
2.5.0
2.4.21
2.4.20
2.4.19
2.4.18
2.4.17
2.4.16
2.4.15
2.4.14
2.4.13
2.4.12
2.4.11
2.4.10
2.4.9
2.4.8
2.4.7
2.4.6
2.4.5
2.4.4
2.4.3
2.4.2
2.4.1
2.4.0
2.3.1
2.3.0
2.2.5
2.2.4
2.2.3
2.2.2
2.2.1
2.2.0
2.1.19
2.1.18
2.1.17
2.1.15
2.1.14
2.1.13
2.1.12
2.1.11
2.1.10
2.1.9
2.1.8
2.1.6
2.1.5
2.1.4
2.1.3
2.1.2
2.1.1
2.1.0
2.0.12
2.0.11
2.0.10
2.0.9
2.0.8
2.0.7
2.0.6
2.0.5
2.0.4
2.0.3
2.0.2
2.0.1
2.0.0
2.0.0-rc.15
2.0.0-rc.14
2.0.0-rc.13
2.0.0-rc.12
2.0.0-rc.11
2.0.0-rc.10
2.0.0-rc.9
2.0.0-rc.8
2.0.0-rc.7
2.0.0-rc.6
2.0.0-rc.5
2.0.0-rc.4
2.0.0-rc.3
2.0.0-rc.2
2.0.0-rc.1
2.0.0-rc.0
1.5.30
1.5.29
1.5.28
1.5.27
1.5.26
1.5.25
1.5.24
1.5.23
1.5.22
1.5.21
1.5.20
1.5.19
1.5.18
1.5.17
1.5.16
1.5.15
1.5.14
1.5.13
1.5.12
1.5.11
1.5.10
1.5.9
1.5.8
1.5.7
1.5.6
1.5.5
1.5.4
1.5.3
1.5.2
1.5.1
1.5.0
1.4.0
1.3.68
1.3.67
1.3.66
1.3.65
1.3.64
1.3.63
1.3.62
1.3.61
1.3.60
1.3.59
1.3.58
1.3.56
1.3.55
1.3.54
1.3.53
1.3.52
1.3.51
1.3.50
1.3.49
1.3.48
1.3.47
1.3.46
1.3.45
1.3.44
1.3.43
1.3.42
1.3.41
1.3.40
1.3.39
1.3.38
1.3.37
1.3.36
1.3.35
1.3.34
1.3.33
1.3.32
1.3.31
1.3.30
1.3.29
1.3.28
1.3.26
1.3.25
1.3.24
1.3.23
1.3.22
1.3.21
1.3.20
1.3.19
1.3.18
1.3.17
1.3.16
1.3.15
1.3.14
1.3.12
1.3.11
1.3.10
1.3.9
1.3.8
1.3.6
1.3.5
1.3.4
1.3.3
1.3.2
1.3.1
1.3.0
1.3.0-rc.4
1.3.0-rc.3
1.3.0-rc.2
1.3.0-rc.0
1.2.6
1.2.5
1.2.4
1.2.3
1.2.2
1.2.1
1.2.0
1.2.0-rc.1
1.2.0-rc.0
1.1.3
1.1.2
1.1.1
1.1.0
1.0.0
1.0.0-rc.9
1.0.0-rc.8
1.0.0-rc.7
1.0.0-rc.6
1.0.0-rc.5
1.0.0-rc.4
1.0.0-rc.3
1.0.0-rc.2
1.0.0-rc.1
1.0.0-rc.0
1.0.0-pre.3
1.0.0-pre.2
1.0.0-pre.1
1.0.0-pre.0
0.43.0
0.42.0-rc.7
0.42.0-rc.6
0.42.0-rc.5
0.42.0-rc.4
0.42.0-rc.3
0.42.0-rc.1
0.42.0-rc.0
0.41.7
0.41.6
0.41.5
0.41.4
0.41.3
0.41.2
0.41.1
0.41.0-rc0
retired
0.41.0-rc.9
0.41.0-rc.8
0.41.0-rc.7
0.41.0-rc.6
0.41.0-rc.5
0.41.0-rc.4
0.41.0-rc.3
0.41.0-rc.2
0.41.0-rc.1
0.41.0-rc.0
0.40.11
0.40.10
0.40.9
0.40.8
0.40.7
0.40.6
0.40.5
0.40.4
0.40.3
0.40.2
0.40.1
0.40.0-rc5
0.40.0-rc4
0.40.0-rc3
0.40.0-rc2
0.40.0-rc1
0.39.0-rc0
0.38.11
0.38.10
0.38.9
0.38.8
0.38.7
0.38.6
0.38.5
0.38.4
0.38.3
0.38.2
0.38.1
0.38.0
0.37.8
0.37.7
0.37.6
0.37.5
0.37.4
0.37.3
0.37.2
0.37.1
0.37.0
0.36.5
0.36.4
0.36.3
0.36.2
0.36.1
0.36.0
0.35.5
0.35.4
0.35.3
0.35.1
0.34.5
0.34.4
0.34.3
0.34.2
0.34.1
0.34.0
0.33.1
0.33.0
0.32.2
0.32.1
0.32.0
0.31.1
0.30.1
0.29.6
0.29.5
0.29.4
0.29.3
0.29.2
0.29.1
0.28.1
0.28.0
0.27.0
0.26.2
0.26.1
0.26.0
0.25.5
0.25.4
0.25.3
0.25.2
0.25.1
0.25.0
0.24.0
0.23.2
0.23.1
0.23.0
0.22.1
0.22.0
0.21.0
0.20.1
0.20.0
0.19.0
0.18.0
0.17.0
0.16.1
0.15.0
0.14.0
0.13.0
0.12.1
0.12.0
0.11.2
0.10.0
0.9.0
0.8.0
0.7.0
0.6.0
0.5.0
0.4.0
0.3.0
0.2.1
0.2.0
0.1.4
0.1.3
0.1.2
0.1.1
0.1.1-rc.2
0.1.1-rc.1
0.1.1-rc.0
0.1.0
The PostgreSQL data layer for Ash Framework
Current section
Files
Jump to
Current section
Files
documentation/topics/advanced/temporal-resources.md
<!--
SPDX-FileCopyrightText: 2020 Zach Daniel
SPDX-License-Identifier: MIT
-->
# Temporal Resources
A temporal resource remembers everything. Every record is spread across many rows, one for
each period of its history, and you can read it as of any moment. Ash's
[temporal resources guide](https://hexdocs.pm/ash/temporal-resources.html) covers what they are
and how to use them. This page is about what AshPostgres does underneath, and what it needs from
you.
## What you need
PostgreSQL 18 or later. That's where periods arrived in primary keys and foreign keys, which is
what all of this is built on. Your repo has to say it's targeting 18:
```elixir
def min_pg_version do
%Version{major: 18, minor: 0, patch: 0}
end
```
You also need the `btree_gist` extension. A temporal primary key is a GiST exclusion constraint
under the hood, and `btree_gist` is what lets it compare ordinary columns like `id`:
```elixir
def installed_extensions do
["ash-functions", "btree_gist"]
end
```
Forget either one, and your temporal resource won't compile. The error tells you which.
## What ends up in your database
Run `mix ash_postgres.generate_migrations` and you get:
- **A period column**, a `tstzrange` holding the period each row is valid for.
- **`PRIMARY KEY (id, valid_at WITHOUT OVERLAPS)`**, so a record can have as many rows as it
likes, but never two that are valid at the same instant.
- **`PERIOD` foreign keys** for relationships between temporal resources. A subscription can't
point at a plan for a stretch of time when that plan didn't exist.
- **Identities that are unique at every instant**, rather than across all of time. Two members
can't share an email at the same moment, but an email that one member gave up can go to
another.
## How a write splits a row
Say a subscription has been on bronze since January, and you change it to gold as of March 1.
Nothing gets overwritten. The bronze row is cut off at March 1, and a gold row picks up from
there:
```text
before: [Jan 1, ∞) bronze
after: [Jan 1, Mar 1) bronze
[Mar 1, ∞) gold
```
The SQL standard has a statement for exactly this: `UPDATE ... FOR PORTION OF`. Here's a plot
twist, though. It was slated for PostgreSQL 19, and temporal resources were first built on it.
Then we found that PostgreSQL had
[reverted it](https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=a4b26b8f7cd07a3e7f0cba550c30db142329e065)
before release: two writes to the same row at the same time could quietly lose part of one of
them.
So AshPostgres does the split itself. Each write is a single statement that writes the slice it
covers, and puts back the rest of the row as new rows next to it. A destroy works the same way,
except the slice it covers is simply gone. When PostgreSQL ships `FOR PORTION OF` for real,
AshPostgres will switch to it.
## When two writes collide
That lost-update problem doesn't go away just because we're doing the split ourselves, so here's
how AshPostgres handles it.
Every temporal write locks the rows it's about to split, and checks that they're still the rows
it expected to find. Usually they are, and the write goes ahead. But if another transaction got
there first and changed one of them, the write backs off without writing anything, and tries
again. From then on it holds on to every lock it takes, so it doesn't keep losing its place in
line. Two upserts that race to create the same record are sorted out the same way: the one that
loses, retries.
You'll almost never notice. It takes a lot of transactions hammering the same part of the same
record at once to make a write retry more than a couple of times. If one ever loses 25 times in a
row, it gives up with `AshPostgres.Temporal.WriteConflict`. Nothing was written, so it's safe to
just try the action again.
That's all under `READ COMMITTED`, which is what you're using unless you've changed it. Under
`REPEATABLE READ` or `SERIALIZABLE`, PostgreSQL raises a serialization failure for these collisions
itself, and retrying is up to you.
One thing to keep in mind: the locking only happens for writes that go through Ash. If something
else writes to your temporal tables with raw SQL at the same moment, it can still step on Ash's
writes.