go / declarative views
One of my apps filters companies and people on about a hundred fields.
A filter reads a wide cache table, cache_companies or cache_people,
with one row for each record, so a query needs no joins. A plain view,
cache_companies_source or cache_people_source, defines each cache.
A job copies each view into its table every minute.
A list of field definitions in Go drives the filter UI. Go code also generates each view's SQL from that list, so the list must stay in Go. Before, a command wrote the generated SQL into a migration. Each migration restated the whole view to move one column, and a reviewer read all of it to find the change.
Now the Go definition is the only definition. The migrate command compares it with the view in the database. When the two differ in meaning, the command rebuilds the view and its table.
The sync reads only the views that Go generates. A migration still writes a hand-written view, such as a search view that no Go list generates.
The definition
Each field names its column, its type, and the CTE that computes it.
A function builds one SELECT from the list: the parent table, a
LEFT JOIN for each CTE on parent_id, and one column for each
field.
type cacheView struct {
Kind string
Schema []FieldSchema
Ctes map[string]string
ParentTable string
}
func (v cacheView) Table() string { return "cache_" + v.Kind }
func (v cacheView) Source() string { return v.Table() + "_source" }
func (v cacheView) SQL() string { return MakeViewSQL(v.Schema, v.Ctes, v.ParentTable) }A field change is a Go diff of a few lines.
Sync on migrate
The migrate command runs the pending migrations first, then syncs each view:
func SyncCacheViews(ctx context.Context, db *pgdb.DB) ([]string, error) {
var rebuilt []string
for _, v := range cacheViews() {
changed, err := syncCacheView(ctx, db, v)
if err != nil {
return rebuilt, err
}
if changed {
rebuilt = append(rebuilt, v.Kind)
}
}
return rebuilt, nil
}The deploy already runs the migrate command before it starts the new release. So a field change reaches production with no new step, and the migrate command is the only way a view changes.
Each sync does three things. It checks the generated SQL with
EXPLAIN. It compares the SQL with the installed view. When they
differ, it rebuilds the view and the table. The three steps share one
transaction, because the comparison uses temporary views that last
only for the session, and the swap is atomic only inside a
transaction:
func syncCacheView(ctx context.Context, db *pgdb.DB, v cacheView) (bool, error) {
sql := v.SQL()
changed := false
err := db.Transaction(ctx, func(tx *pgdb.DB) error {
if err := tx.Exec(ctx, "EXPLAIN "+sql); err != nil {
return fmt.Errorf("the generated %s view SQL is invalid against the current schema: %w", v.Kind, err)
}
current, err := cacheViewMatches(ctx, tx, v, sql)
if err != nil {
return err
}
if current {
return nil
}
changed = true
return RebuildTable(ctx, tx, v.Table(), sql)
})
return changed, err
}EXPLAIN plans the generated SQL against the live schema without
running it. A Go field that reads a column no migration added fails
there, before the rebuild starts.
Compare parse trees
cacheViewMatches cannot compare text. Postgres stores a view as a
parse tree and renders it back with pg_get_viewdef. The rendering
changes the indentation and the letter case. It rewrites
x IN ('a', 'b') as x = ANY (ARRAY['a'::text, 'b'::text]).
So I put both sides through the parser. The Go SQL goes into a
temporary view, and so does the installed definition. I read each one
back with pg_get_viewdef, and compare the two renderings:
func settleViewDef(ctx context.Context, tx *pgdb.DB, name, sql string) (string, error) {
defer tx.Exec(ctx, "DROP VIEW IF EXISTS pg_temp."+name)
for range 10 {
if err := tx.Exec(ctx, "CREATE OR REPLACE TEMP VIEW "+name+" AS "+sql); err != nil {
return "", fmt.Errorf("reparse %s: %w", name, err)
}
var next string
if err := tx.QueryRow(ctx,
"SELECT pg_get_viewdef(to_regclass($1), TRUE)", "pg_temp."+name).Scan(&next); err != nil {
return "", err
}
if next == sql {
return next, nil
}
sql = next
}
return "", fmt.Errorf("%s did not settle after 10 parser passes", name)
}One pass can leave a difference. A view read out of the database has been through the parser more times than SQL built in Go. On the next parse, Postgres moves an array's cast into each element, so the same meaning renders two ways. The loop parses until the rendering stops changing. Today that takes one or two passes. The bound of ten catches a rendering that cycles, so the command reports it and does not loop.
Before, a reviewer saw each view change in a migration. Now no SQL file shows the change, so a test does that check. The test runs the same comparison for each view against the test database. It fails when someone changes the Go definition and does not run the migrate command.
Build beside, then swap
The refresh job does not update the live table in place. It builds a new table beside the live one, fills it, indexes it, and swaps it in. A rebuild for a definition change uses the same steps. It also replaces the view, and it takes its columns from the new view:
func RebuildTable(ctx context.Context, tx *pgdb.DB, name, sql string) error {
build, source, buildSource := name+"_bld", name+"_source", name+"_source_bld"
// The refresh job takes the same lock, so the two run one at a time.
if err := tx.Exec(ctx, "SELECT pg_advisory_xact_lock(hashtextextended($1::text, 0))", name); err != nil {
return err
}
indexes, err := tableIndexes(ctx, tx, name)
if err != nil {
return err
}
// Build the new pair beside the live one. Readers keep the live table.
tx.Exec(ctx, fmt.Sprintf("CREATE VIEW %s AS %s", buildSource, sql))
tx.Exec(ctx, fmt.Sprintf("CREATE TABLE %s AS SELECT * FROM %s", build, buildSource))
// ... create each index on the build table, then ANALYZE it
// The swap.
tx.Exec(ctx, "SELECT set_config('lock_timeout', '10s', true)")
tx.Exec(ctx, "DROP VIEW IF EXISTS "+source)
tx.Exec(ctx, fmt.Sprintf("ALTER VIEW %s RENAME TO %s", buildSource, source))
tx.Exec(ctx, "DROP TABLE "+name)
tx.Exec(ctx, fmt.Sprintf("ALTER TABLE %s RENAME TO %s", build, name))
// ... rename each build index to its live name
return nil
}The real code checks each error. I left those checks out here.
The advisory lock stops a race with the refresh job. Without it, a refresh could copy the old view while the rebuild runs. After the rebuild commits, the refresh would swap in its table, which has the old columns, and drop the table that the rebuild built. With the lock, a refresh that waits for the rebuild copies the new view.
Readers keep the live table until the DROP TABLE. The swap waits at
most 10 seconds for its lock, the same lock_timeout the migration
runner sets. The refresh job waits one second and tries again a
minute later. A rebuild has no later run, so a timeout fails the
deploy, and the next deploy runs it again.
The rebuild copies each live index onto the build table, because a migration owns the cache table's indexes. The Go definition owns only its columns.
Two-step changes
Some changes need a migration and a Go change. Both can ship in one deploy, because the migrate command runs the migrations before the sync. The Go change alone fails the sync.
A column rename: a migration adds the new column and backfills it, and
the Go definition reads the new column. Without the migration,
EXPLAIN fails the sync. A later deploy drops the old column, because
the old release reads it until the new release is live.
A drop of an indexed field: a migration drops the index, and the Go definition drops the field. Without the migration, the rebuild fails with a message such as:
index_cache_companies_on_city reads a column the new definition of cache_companies does not project.
Dropping an indexed field takes two steps: drop the index in a migration,
then drop the field from the Go definition.The rebuild does not drop the index quietly. A missing index shows up later as a slow query, far from the change that caused it.