Metadata formats¶

psycodict keeps its bookkeeping in a handful of metadata tables — meta_tables, meta_indexes, meta_constraints, and their _hist counterparts. The layout of those tables is versioned by a single integer, the metadata format. This document specifies how the format is recorded, what happens when a psycodict meets a database using a different format, and the checklist to follow when changing the format.

The goal of the design is that a format change never strands anyone: a newer psycodict keeps working against a not-yet-migrated database (with the new features unavailable), an older psycodict keeps working against a migrated database, read-only mirrors need no action at all, and migration is a single deliberate step rather than a side effect of connecting.

Format history¶

format	psycodict	change
0	0.x	Baseline: `meta_tables`, `meta_indexes`, `meta_constraints` and their `_hist` counterparts, with no format stamp.
1	1.0	`meta_indexes` / `meta_indexes_hist` gain a nullable `whereclause` column, holding the predicate of a partial index (NULL for an ordinary index). Compatible with format 0: everything keeps working against a format-0 database except creating partial indexes.

The format number is aligned with psycodict’s major version: format N is introduced by psycodict N.0, and plans to change the format should be scheduled for a major release.

The stamp¶

A database records its format in the single-row meta_format table:

column	meaning
`version`	the metadata format of this database’s layout
`min_compat`	the oldest `META_FORMAT` a psycodict may implement and still safely use this database

A database that has meta tables but no meta_format table is a format-0 database (psycodict 0.x never stamped). A meta_format table that exists but is empty is an error: the format is unknowable, and psycodict refuses to guess. The stamp is written when a fresh database is bootstrapped (PostgresDatabase(create=True)) and updated by each migration step.

min_compat is what lets an older psycodict decide whether it can use a newer database, a situation the older code cannot reason about on its own. Additive changes (new columns) keep min_compat low; a breaking change (renaming, retyping or repurposing something that older code reads or writes) must raise it.

Connecting across formats¶

Every connection compares the database’s stamp with the format it implements (META_FORMAT in psycodict/base.py):

Same format — connects silently.
Database older — if every intervening migration is marked compatible (see the registry below), psycodict connects with a warning and operates at the database’s format: everything present in that format works, and features introduced by newer formats raise an informative error (for example, create_index(where=...) against a format-0 database). If some intervening migration is not compatible, the connection is refused with instructions to migrate or to use a matching psycodict release.
Database newer — if the stamped min_compat admits this psycodict, it connects with a warning and simply does not touch the columns it does not know about (this is why new columns must be appended; see the checklist). Otherwise the connection is refused: upgrade psycodict.

The format a connection actually operates at is exposed as db.meta_format, which downstream code can use to gate features.

Read-only databases (replicas such as the LMFDB devmirror, or users without write grants) can never migrate themselves, which is precisely why an older compatible format warns instead of refusing. A replica inherits the migration when the primary is migrated.

Pre-1.0 psycodict cannot be repelled¶

This negotiation only binds clients that implement the meta_format protocol — that is, psycodict 1.0 and later. A pre-1.0 psycodict knows nothing of meta_format; it will happily connect to a format-1 database and, because its restore_index and metadata reload/revert paths predate the whereclause column, it can silently rebuild a partial index as a full one and drop the predicate. meta_format and min_compat cannot stop it, since it never reads them.

Do not run a pre-1.0 psycodict against a database that a 1.0+ psycodict has created or migrated (format ≥ 1). If it has already happened, what is recoverable depends on which paths the pre-1.0 client ran. Its restore_index only rebuilds the physical index, leaving the predicate recorded in meta_indexes: drop the wrongly-full index – with plain SQL (DROP INDEX) or drop_index(name, permanent=False), not the default drop_index(name), whose permanent=True deletes the metadata row, predicate and all, and leaves nothing for the rebuild – then run restore_index from a 1.0 client to rebuild it correctly. (Restoring without dropping first keeps the bad index behind under a _depN name – and a unique index kept that way still over-constrains writes.) The pre-1.0 metadata reload and revert paths, however, rewrite the meta_indexes rows with the six pre-1.0 columns, destroying the recorded predicate – after that the partial index cannot be reconstructed from the database itself, only from a meta_indexes backup or copy_to_indexes export taken after the partial index was created. (The pre-migration backup of Rolling back below cannot help here: it predates the whereclause column, so it never contained the predicate.) This matters only during a mixed-version rollout; once every process has been upgraded to 1.0+ there is nothing to watch for. (The alternative — leaving a tombstone in the old meta_version table so pre-1.0 clients refuse — was declined in favor of the single-stamp design; the numbering aligns with the major version instead.)

Migrating¶

Migration is deliberate, never automatic: connecting does not alter a database. To migrate, an administrator runs

db.upgrade_metadata()          # on a live connection
# or, in one step at connection time:
db = PostgresDatabase(config=config, upgrade=True)

which applies each registered step in order and re-stamps the format as it goes (each step’s DDL and stamp commit together). Steps are idempotent, so re-running a migration — or running it on a database that grew a column out of band — is harmless. Downgrades are not supported.

Rolling back¶

Because downgrades are not supported, take a backup before migrating, in the custom format that pg_restore consumes (the default plain-SQL format cannot be given to pg_restore):

pg_dump -F c -f pre_migration.dump $DBNAME

To roll back, recreate the database from the dump — restoring over the existing database does not work: pg_restore hits existing-object errors (and by default keeps going past them, leaving a half-restored mixture) with the new meta_format stamp still in place:

pg_restore -d postgres --clean --create --exit-on-error pre_migration.dump
# equivalently: dropdb $DBNAME && createdb $DBNAME
#               && pg_restore -d $DBNAME pre_migration.dump

run as a role allowed to drop and recreate the database. Dropping a database requires that no one is connected to it — stop every client first (psycodict connections are long-lived); dropdb --force terminates lingering sessions, while pg_restore --clean has no equivalent, so with that form close them beforehand. This restores the whole database to the moment of the dump — application data included — so quiesce writes for the migration window and roll back immediately or not at all.

If committed writes cannot be lost, the surgical variant is to back up only the six meta tables:

pg_dump -F c -f pre_migration_meta.dump \
    -t meta_tables -t meta_tables_hist -t meta_indexes \
    -t meta_indexes_hist -t meta_constraints -t meta_constraints_hist \
    $DBNAME

and to roll back by replacing them, leaving the application tables (and any committed writes since the dump) untouched. This still needs a brief pause of every psycodict client: the tables are dropped and restored in separate commands, and a client active in between sees missing relations. The difference from the whole-database restore is what survives the pause – here every write made since the dump – not that clients can stay connected:

psql -c 'DROP TABLE meta_tables, meta_tables_hist, meta_indexes,
                    meta_indexes_hist, meta_constraints, meta_constraints_hist;
         DROP TABLE IF EXISTS meta_format' $DBNAME
pg_restore -d $DBNAME --exit-on-error pre_migration_meta.dump

The metadata still rewinds to the dump: an index or constraint created after it becomes an unlisted orphan (drop it by hand), a column added after it disappears from psycodict’s view, and count() on the empty query answers from a cached total in meta_tables that will be stale if rows were written in between. So the surgical variant, too, is best used immediately after migrating, before other schema activity.

Both procedures have been rehearsed against a live format-0 database built with an actual pre-1.0 psycodict: after either restore, the pre-1.0 client operates exactly as before the migration, and a 1.0 client connects with the usual format-0 warning and degrades, as if the migration had never run.

Exported metadata files¶

copy_to_indexes and friends write the columns the database actually has, and the files carry no header, so the format of a file is recovered from its width when it is reloaded. Because format bumps only append columns, a file exported at an older format keeps loading after the database migrates (the missing trailing columns load as NULL), and a file exported at a newer format is rejected with instructions rather than half-loaded.

Checklist: changing the metadata format¶

Suppose the layout of the meta tables must change. Then:

Append, never mutate. New columns go at the end of the relevant _meta_*_cols tuple in psycodict/base.py, and are recorded in _meta_col_formats with the format that introduced them. The cross-format guarantees (older clients, older files) rest on the columns of an older format being a prefix of the newer ones. Renaming, retyping, reordering or repurposing an existing column is a breaking change; avoid it if at all possible, and see step 4 if not.
Bump META_FORMAT in psycodict/base.py, add a line to its History comment and to the table at the top of this file. Schedule the bump for the next major release, so the format number and the major version stay aligned.
Register the migration in META_MIGRATIONS in psycodict/database.py: a one-line description, the compatible flag, the min_compat to stamp, and the name of a PostgresDatabase method performing the DDL. The method does only the DDL, idempotently (IF NOT EXISTS / IF EXISTS); stamping is handled by upgrade_metadata.
Choose the compatibility flags honestly.
- A purely additive change: compatible=True, and min_compat stays what it was (0 today).
- A breaking change: compatible=False (older-format databases are refused rather than warned) and min_compat raised to the new format (older psycodicts are refused by newer databases). Say so loudly in the CHANGELOG.
Gate the feature. Code that reads or writes the new columns must consult self._db._meta_format and either degrade (reads: the column is absent, so treat it as its default) or raise with instructions (writes that would lose information). Follow the whereclause sites in psycodict/table.py — list_indexes, restore_index, create_index — as the model. Generic metadata I/O (_copy_to_meta, _reload_meta, _revert_meta, _meta_creator) is already format-aware through _meta_cols_types_jsonb_idx(meta_name, fmt) and needs no per-change work.
Test the seam. Extend the format tests (tests/test_meta_formats.py) with: connecting to the previous format warns and operates degraded; the new feature raises its informative error there; upgrade_metadata migrates, stamps and is idempotent; files exported at the previous format still reload; and the new format’s stamp is what you registered.
Document it: the CHANGELOG, and (for a new feature) the docstrings of the methods involved.