README
¶
Derived values: four Postgres techniques, and where sqlb's ceiling is
A project has a due date and two task counters. A list page wants six things the
row does not literally contain: totalTasks, completedTasks, openTasks,
isOverdue, progress %, and isStarred.
Those six are not one problem. They are four, and picking the wrong technique for one of them is the difference between an index scan and a subquery per row.
Run it:
go test ./example/computed/
Nothing here needs a database — every test asserts the compiled SQL, which is
what SQL() exists for.
Pick by where the value comes from
| The value depends on | Technique | Filterable | Sortable | Indexable |
|---|---|---|---|---|
| the same row, immutably | GENERATED ALWAYS AS … STORED |
yes | yes | yes |
| other rows | a trigger-maintained counter | yes | yes | yes |
the same row + now() |
a projected expression | yes¹ | yes¹ | no |
| who is asking | a projected expression with a bind | yes¹ | yes¹ | no |
¹ through hand-written sqlb.Raw, not through the filter grammar — see the
ceiling below.
The first two produce ordinary columns. sqlb needs to be told nothing about
them: they are Filterable, Sortable, and reachable from the REST filter
grammar, the TypeScript client and the CLI exactly like a column somebody typed
in by hand. If a value can be one of these, make it one of these. The rest of
this example is about the two rows that cannot.
1 · Postgres computes it on write
open_tasks int GENERATED ALWAYS AS (total_tasks - completed_tasks) STORED
openTasks is arithmetic over two columns of the same row, so Postgres can keep
it. It is then a real column: CREATE INDEX ... (org_id, open_tasks) works, and
ORDER BY open_tasks DESC over a million rows is an index scan rather than a
sort of the whole table.
The restriction is the reason isOverdue cannot join it: a generated column's
expression must be IMMUTABLE, and isOverdue reads current_date.
There is no schema.Generated() yet, so this is a hand-written migration and the
column is declared to sqlb as an ordinary one — which is all it is, from above.
2 · A trigger keeps a counter
totalTasks and completedTasks count rows in another table. Recomputing them
per read is a correlated subquery on every request; a trigger on tasks pays the
cost once per write instead, and the columns are again ordinary columns.
This is the technique most often skipped in favour of something cleverer, and it
is usually the right answer. The trade is explicit: writes to tasks get slower
and can drift if the trigger has a bug — which is not hypothetical, and the
counter needs a backfill when it happens.
3 · Project an expression
sqlb.RawSel("(due_date IS NOT NULL AND due_date < current_date AND open_tasks > 0)").
As("is_overdue")
sqlb.RawSel("EXISTS (SELECT 1 FROM project_stars s "+
"WHERE s.project_id = projects.id AND s.member_id = ?)", viewer).
As("is_starred")
isOverdue and progress are volatile or cheap enough to evaluate per read.
isStarred has no other option at all: whether a project is starred depends on
who is asking, so no column and no view can hold it — there is no the answer.
The ? is the point. RawSel's placeholders are renumbered into $N along
with every other bind in the statement, so the viewer arrives as a parameter and
cannot become SQL. It also means the fragment composes: the projection's bind,
a predicate's bind and a ?search term are written at three call sites that
cannot see each other, and only the compiler knows what position each lands in.
ProjectView embeds Project and adds the three fields, and sqlb.Collect
scans into it exactly — a field no result column filled is an error naming the
field, not a silent zero.
4 · A view
Not shown in code, because it needs no code: CREATE VIEW the whole thing, and
Describe[T]().Table("project_rows"). The entire generated read path — filter
grammar, cursor paging, TypeScript, CLI — works over it, because sqlb never
asked whether the relation was a table.
Reach for it when the derived set is large enough that the projection stops being
readable. The cost is that writes go somewhere else, so rest exposes reads
only, and the view becomes a second place a schema change has to land.
The ceiling, stated plainly
Here is what the example actually compiles for a starred-and-overdue list:
SELECT "id", …, "open_tasks",
(due_date IS NOT NULL AND due_date < current_date AND open_tasks > 0) AS "is_overdue",
(completed_tasks * 100 / NULLIF(total_tasks, 0)) AS "progress",
EXISTS (SELECT 1 FROM project_stars s
WHERE s.project_id = projects.id AND s.member_id = $1) AS "is_starred"
FROM "projects"
WHERE EXISTS (SELECT 1 FROM project_stars s
WHERE s.project_id = projects.id AND s.member_id = $2)
ORDER BY (due_date IS NOT NULL AND due_date < current_date AND open_tasks > 0) DESC,
"due_date" ASC
Correct, parameterised, and one round trip. Two things are wrong with it anyway:
The expression is written three times — once to project it, once to filter,
once to sort — and the viewer is bound twice, as $1 and $2. Nothing keeps the
three copies agreeing.
sqlb can already bind one value once across all three positions: that is what
sqlb.Near does for a query vector, which is twenty kilobytes and would
otherwise be sent three times per search. The sharedValue behind it is
unexported and reachable only through Nearness, so RawSel cannot ask for it.
Nothing above Go knows the field exists. is_overdue is not a declared
column, so it is not in the filter grammar, not in the generated TypeScript
type, not in the CLI's flags, and not in the OpenAPI document. A REST client
cannot ask for ?filter=isOverdue.eq.true no matter what the SQL can do. That is
not a gap in what sqlb can express — the SQL above is proof it can — it is a
gap in what sqlb can declare, and the declaration is what the emitters read.
That gap is the whole argument of
ADR-0041 and
issue #17: a schema.Computed slot
that writes the expression once, puts the field in every emitted artefact, and —
for the isStarred case — obliges a hook to supply the bind, so a per-viewer
field that nobody wired up fails at mount instead of returning false forever.
Techniques 1, 2 and 4 survive that ADR unchanged. Only technique 3 is the placeholder.
Documentation
¶
Overview ¶
Package computed shows how to get a derived value — one the row does not store — out of Postgres through sqlb.
There is no `schema.Computed` yet; ADR-0041 designs one and says why. This package is what the four techniques look like without it, and three of the four are not going away when it lands: a column Postgres maintains is cheaper than an expression evaluated per read, and it is the right answer often enough that a declaration slot should not be allowed to hide it.
The four, in the order to reach for them:
- A STORED generated column. Postgres computes it on write, it is a real column, and it can be indexed. Restricted to IMMUTABLE expressions over the same row, so nothing involving now() or another table qualifies.
- A trigger-maintained counter. For values that come from other rows — "how many tasks does this project have" — where recomputing per read would be a correlated subquery on every request.
- A projected expression, via sqlb.RawSel and sqlb.Collect. Costs nothing on write, evaluated per read, and it is the only one of the four that can take a bind parameter — which is what a per-viewer field like "did *I* star this" requires.
- A database VIEW, described with Table(). One hand-written object, and the whole generated read path works over it.
(1) and (2) produce ordinary columns, so sqlb needs to be told nothing: they are Filterable, Sortable and indexable like any other, and the only sqlb-side decision is ReadOnly, which keeps them out of the generated request bodies. (3) is where the ceiling is, and where ADR-0041 aims.
Index ¶
Constants ¶
const Schema = `` /* 1089-byte string literal not displayed */
Schema is the DDL the techniques below assume. It is written out rather than declared through the schema package because two of the three objects — the generated column and the trigger — have no spelling in the DSL today, so a project using them writes this migration by hand and declares the resulting columns as ordinary ones.
Variables ¶
This section is empty.
Functions ¶
func Derived ¶
func Derived(viewer int64) []sqlb.Selectable
Derived is the projection ProjectView expects: every column of the table, then the three expressions.
Select appends to the projection but replaces the default one, so the table's own columns have to be named once something else is added. columnsOf does that from the model rather than by hand, so a column added to Project reaches the view without a second edit.
func OverdueFirst ¶
OverdueFirst orders by a derived value without projecting it.
This is the half of technique (3) that has no ceiling problem: an expression in ORDER BY or WHERE is just SQL, and sqlb has never stopped anyone writing it. What it costs is the guarantee — `sqlb.Raw` is not checked against the schema, so a column renamed under it fails at the database rather than at generate time, and a REST client cannot reach it at all because the filter grammar only admits declared columns.
That gap is the argument for ADR-0041: not that this cannot be written, but that writing it here leaves the TypeScript client, the CLI and the OpenAPI document not knowing the field exists.
func StarredBy ¶
StarredBy filters on the per-viewer fact, which is the same EXISTS as the projection and the same reason it needs a bind.
func View ¶
View builds the read. It is Query[Project] rather than Query[ProjectView] because the table is the same table — ProjectView describes the *result*, and Collect is what pairs the two.
Note what did not have to change: hooks registered on Project still run, so the tenant predicate applies to this query as much as to a plain list. The derived values ride along on a query the domain still constrains.
Types ¶
type Project ¶
type Project struct {
ID int64 `db:"id" sqlb:"pk,default"`
OrgID int64 `db:"org_id" sqlb:"filter,scope,readonly"`
Name string `db:"name" sqlb:"filter,sort,search"`
DueDate *time.Time `db:"due_date" sqlb:"filter,sort"`
TotalTasks int `db:"total_tasks" sqlb:"filter,sort,readonly,default"`
CompletedTasks int `db:"completed_tasks" sqlb:"filter,sort,readonly,default"`
OpenTasks int `db:"open_tasks" sqlb:"filter,sort,readonly,default"`
}
Project is the row. The three derived columns are declared exactly like the stored ones, because to everything above Postgres that is what they are.
ReadOnly is the load-bearing word. Without it the generated create and update bodies would accept a total_tasks, and a request could make the counter disagree with the tasks it counts. Postgres would reject a write to open_tasks on its own — it is GENERATED ALWAYS — but as a 500 naming a constraint, where ReadOnly is a 400 naming the field.
type ProjectView ¶
type ProjectView struct {
Project
// Evaluated per read. IsOverdue reads current_date, so it can change
// without the row changing — which is exactly why it cannot be technique
// (1): Postgres requires a generated column's expression to be IMMUTABLE.
IsOverdue bool `db:"is_overdue"`
// NULL when the project has no tasks, which is why this is a pointer.
// NULLIF is doing that: a zero denominator would otherwise be a division
// error taking the whole request with it, not a zero.
Progress *int `db:"progress"`
// Depends on who is asking, not on the row. No column and no view can hold
// this, because there is no "the" answer — it is a function of the request.
IsStarred bool `db:"is_starred"`
}
ProjectView is the row plus the values no column holds. Embedding Project keeps one definition of the shared columns — an untagged embedded struct contributes its own fields, so the view maps every column the table has and then some.
It is a separate type from Project on purpose. Query[Project] stays the plain table read that INSERT ... RETURNING and every hook already work against; asking for the derived values is a different, more expensive query, and the type is what says so at the call site.
func (ProjectView) TableName ¶
func (ProjectView) TableName() string
Source Files