Skip to content

CORE-199 pending index rollout

Native PostgreSQL flush and discard select pending rows only through the partial index idx_nodes_pending_scope_c. Until a valid index of exactly that shape exists, both refuse with CORE-199 pending index missing or invalid. Application startup never creates it. This page is the operator procedure. It is not a deployment record, and it makes no staging latency claim.

Artifact: scripts/migrations/core199_pending_index.sql, which follows the CORE-204 pattern in scripts/migrations/core204_c_scope_index.sql.

Pre-validation

  1. Confirm the target schema, and that the role owns memory_nodes and holds CREATE on the schema and TEMP on the database.
  2. Look for any existing index with the name: SELECT indisvalid, pg_get_indexdef(indexrelid) FROM pg_index WHERE indexrelid = to_regclass('<schema>.idx_nodes_pending_scope_c'); If one exists and is invalid or has a different shape, stop. The script refuses it too and never drops it.
  3. Look for long transactions. CREATE INDEX CONCURRENTLY waits for every snapshot older than the build: SELECT pid, xact_start, state FROM pg_stat_activity WHERE xact_start < now() - interval '5 minutes';
  4. Check that there is disk headroom. The index holds only rows that carry __symfonic_pending__, but the build still reads the whole table.

Run

psql "$POSTGRES_DSN" -X -v scope_schema=public -f scripts/migrations/core199_pending_index.sql

Do not pass -1 or wrap the script in a transaction: CONCURRENTLY refuses to run inside one. ON_ERROR_STOP is set, so the first error ends the run. The script verifies the index shape before and after the build. Running it again is a no-op that keeps the same index.

Post-validation

  • The script exits 0. Its final verifier checks validity, readiness, liveness, the btree access method, the (tenant_id, scope_path) keys with "C" collation and text_ops, and the predicate (properties ? '__symfonic_pending__'::text).
  • Monitor pg_stat_progress_create_index during the build.
  • After ANALYZE memory_nodes, an EXPLAIN of a pending selection names idx_nodes_pending_scope_c and reports no rows removed by filter.

Failure and rollback

  • If the build is interrupted (cancelled, killed, or out of disk), PostgreSQL can leave an invalid index under the name. Selectors refuse it. Investigate the cause, run DROP INDEX CONCURRENTLY <schema>.idx_nodes_pending_scope_c, and re-run the script.
  • To roll back, run DROP INDEX CONCURRENTLY <schema>.idx_nodes_pending_scope_c. Native flush and discard then refuse again, fail closed. Rollback never turns on a scan over committed history.
  • No row data changes during the rollout or the rollback.

Scope

The rollout bounds pending selection only. Public native admission for lifecycle operations stays closed. Retrieve, inventory and get_record are bounded by the 1 s native deadline, not by this index (see the CHANGELOG).