SQL API reference

Every GeoGenius operation is a SQL function or view in the installed schema, called through the host's own Ecto Repo (MyApp.Repo.query/2 for a write, or GeoGenius's own read functions for a resolution or traversal read - see reading.md for the calling convention, including why a geometry/geography argument binds as a Geo struct rather than WKT/EWKT text). This guide lists every function a host calls directly, grouped by what it does, with its full parameter list.

A handful of implementation-only functions are not listed here because a host never calls them directly: assert_extensions (run once, at install, by the migration itself), classify_relation and relation_lock_key (helpers rebuild_relations calls internally), partition_lock_key (the key open_release, create_release_partitions, and drop_release_partitions serialize on; see Concurrent partition DDL below), publication_lock_key (the key publish_release, rollback_publication, and retire_releases serialize on, so two callers cannot interleave a swap of one collection's publication pointer), publication_release_is_publishable (a constraint trigger function), drop_release_partitions (called by retire_releases, not on its own), assert_release_mutable (the guard every write function below calls to reject a write against a completed or retired release), assert_area_in_collection (the guard the release-membership and geometry writes call to reject an area from a different collection), assert_seed_keys (the guard every plural read calls to reject a null seed array or a null element inside one), assert_write_arrays and assert_resolved (the two guards every set write calls, to reject arrays that disagree in length or carry a null where the column is required, and to refuse a key that resolves to nothing), area_lock_key and lock_areas (the single definition of the lock one area serializes on, and the loop that takes a batch's locks in one order -- see One lock order for the whole write path), and area_codes_json (the helper every function returning area_match calls to build the codes field; see below).

Every function is SECURITY INVOKER with a pinned search_path; read functions are STABLE PARALLEL SAFE and write functions are VOLATILE. Five carry no parallel marking: release_at, staging_table_name, and the three internal guards assert_extensions, assert_release_mutable, and assert_area_in_collection. Every function returning rows (all of Resolution and Traversal below) returns SETOF area_match, except the four set-keyed reads, which return SETOF seeded_area_match. Most Lifecycle and Import functions return a scalar or void; prepare_import and retry_failed return one candidate-decision row, and claim_import_execution returns a closed text result.

Across Resolution and Traversal, four parameters repeat with the same meaning and the same defaults everywhere they appear: collections and types (text[] DEFAULT NULL, no filter), target_release_id (uuid DEFAULT NULL, meaning the collection's currently published release), and include_retired (boolean DEFAULT false, active areas only). max_depth (on children_of/ancestors_of) defaults to 1; result_limit (on areas_near/search_areas) defaults to 50, and an explicit NULL returns every row instead -- which is how a caller that narrows the result afterwards asks for the whole ranked set rather than naming a number that stands in for one.

Every area_match row's codes field is a jsonb object mapping each code type to a JSON array of the values an area holds under that type, for example {"postal": ["30309", "30310"]}, an area with two postal codes keeps both. It is built by area_codes_json, grouping and aggregating every area_code row for the area rather than picking one.

Time-scoped release lookup

The read functions above take a target_release_id, not a timestamp. release_at bridges the two:

release_at("us_counties", "2026-01-15T00:00:00Z") turns "what did we serve on January 15?" into a target_release_id argument for any Resolution or Traversal function above. It returns NULL when the collection had published nothing yet as of as_of.

Resolution

Spatial and non-spatial lookups against the currently published release of a collection (or an explicit target_release_id).

A code is unique only within a parent, so areas_by_code's parent_area_key scopes the lookup to that area's descendants and parent_max_depth controls how far down it reaches, defaulting to a direct parent relation.

resolve's input is a single jsonb object: lon/lat (together) selects containment then proximity, code_type/code_value selects code lookup, name (optionally with parent_area_key to scope the search to that area's children) selects name search. strategies (text[] DEFAULT NULL) restricts or reorders which of containment, code, name, proximity are attempted; the default order is exactly that sequence, and the first strategy whose required input is present and whose query returns at least one row wins.

Traversal

Hierarchy walks over relation rows, measured (rebuild_relations) or asserted (put_relation) alike — see Measured versus asserted relations.

classifications (text[] DEFAULT NULL) filters by relation_type (contains/mostly_contains/overlaps). Raising max_depth past 1 is cheap on an ordinary ranked hierarchy and combinatorially more expensive on a densely mutually-related one — see Traversal depth on dense graphs for measured numbers before raising it on a collection where many areas relate to many others of similar rank.

Set-keyed reads

Four reads above answer for exactly one seed, so answering for a list of seeds costs one call per element. Each has a plural sibling taking an array where the singular takes a scalar:

Every parameter after the seed array keeps the singular's meaning and default.

They return SETOF seeded_area_match, which is seed_key text followed by area_match's sixteen columns in area_match's order. A plural result mixes rows from every seed, so the seed has to travel with each row; a composite type cannot be declared as "area_match plus one column", and nesting area_match inside a result column would force every client to decode a nested composite, so the columns are repeated and test/pgtap/schema/test_install.sql pins the two lists against each other. The column is seed_key rather than seed_area_key because three of the four are seeded by an area key and areas_by_code_many is seeded by a code value.

Semantics, all four:

• The result for ARRAY[a, b] is the singular result for a followed by the singular result for b, in the array's order -- not the seeds' sort order.
• A seed that matched nothing contributes no rows, rather than one row of NULLs.
• An empty array returns zero rows.
• A NULL array raises SQLSTATE 22004, as a NULL singular seed does, and so does a NULL element inside the array: a skipped element would lose one seed out of thousands with no error to show for it.

Each delegates to the singular read once per seed rather than reimplementing it, the way resolve delegates to each strategy. What that removes is the round trip, not the per-seed lookup. Measured over a 4,000-area release with 1,000 seeds: 1,000 children_of calls from a client on loopback took 445ms against 52ms for one children_of_many call, while the same work run entirely inside the database took 51ms against 37ms. A caller that needs to join or aggregate against catalog areas wants GeoGenius.Query's composable, view-backed path instead: these are SETOF plpgsql functions and the planner cannot push a predicate into either shape.

Write

Load catalog identity, then release membership and geometry.

Set writes

Six of the writes above come in a plural form taking one array per column, with element n of every array describing row n. The scalar form is the plural form called with one-element arrays, so there is one implementation of what each write means, and a caller choosing between them is choosing only how many round trips to spend.

They exist because an import writes in bulk and the round trips dominate. A source that denormalises a hierarchy describes the same county in every city row of that county, so a page of staged rows names far fewer distinct areas than it has rows, and one statement per area per column spends a round trip on every repeat. Measured on the US SimpleMaps import (150,622 staged rows, 153,917 areas, 466,262 area writes): the normalizing phase costs 2,449 statements and 39.6 s written as sets, against 1,755,328 statements and 994.8 s written one area at a time. put_boundaries validates and repairs the accepted geometries, replaces each area's boundary and subdivided parts, and recomputes its centroid from what it stored. The Elixir put_boundary/5 API is a one-element plural call when its simplify_tolerance is zero or omitted. A nonzero tolerance retains the original singular SQL path and its display-geometry simplification behavior. The plural SQL takes an explicit display_tier and source_properties per boundary instead.

Before touching a boundary row, both boundary calls acquire the collection's publication lock and then recheck release mutability. Publication acquires that same lock before it verifies a release. A writer that waited for publication therefore sees the completed release and fails, while a publisher that waited for a writer verifies the writer's committed result instead of an earlier snapshot. The plural call additionally upserts every accepted release_area row in area-id order, so overlapping batches take membership locks deterministically before either path locks boundary or boundary_part. Canonical repaired geometry is materialized once and reused for centroid, boundary, and subdivision writes.

Four rules govern what a batch means, and all four are things a naive batching would get wrong:

• Arrays must agree in length, and may not carry a null where the column is required. unnest pads a short array with nulls rather than failing, so a batch assembled from two sources of different lengths would write rows the caller never described. Mismatched lengths raise 22023; a null in a required column raises 22004. The optional columns are put_area_name_many's locales, and put_area_in_release_many's centroids and data.
• A key that resolves to nothing raises, with SQLSTATE P0002 and a message naming the key. The plural forms resolve their keys by joining, and a join alone would drop the unknown key's row and report success.
• Repeats within one batch are deduplicated on the constraint key. ON CONFLICT DO UPDATE raises 21000 when one statement presents the same conflict key twice, and a batch legitimately repeats areas.
• Where a write is last-write-wins, the last occurrence in the arrays is the one that wins. That is release membership (put_area_in_release_many overwrites centroid and data), boundaries (put_boundaries keeps the last row for an area_key), and relations (put_relation_many overwrites relation_type), matching what a loop of scalar calls in the same order would leave. Names and codes accumulate instead, so their deduplication changes nothing but the statement's legality.

One lock order for the whole write path

A plural write locks many rows per statement where the scalar beside it locks one, so two of them running at once can only avoid a cycle by agreeing on an order -- and the agreement has to hold between functions, not just inside each one. Two functions that each sorted their own way would deadlock over any pair of rows they share, which is reachable in ordinary use: import leases are scoped to a release, so two releases of one collection normalize at the same time, and the normalizing phase issues an area upsert and a name write for every page.

Two things enforce it, and each covers the other:

• lock_areas takes one pg_advisory_xact_lock per distinct area a batch is about to touch, ascending by lock key, before the batch touches any row. The key comes from area_lock_key(collection_id, composed_key), the single definition of what an area serializes on, so a batch and a scalar upsert_area of the same area take the same lock. Both upsert_area_many and put_area_name_many go through it. Because every lock is taken before any row is written, two batches sharing an area can never both reach their row writes.
• Every insert walks its own rows in constraint-key order, and where two functions write the same table they use the same key. upsert_area_many inserts in area_key order and put_area_name_many pre-locks the area rows in area_key order, so the two agree even for a writer that skips the advisory lock.

put_area_name_many pre-locks at all because writing names fires the area_name statement trigger, which updates area.official_name for every area the insert touched: holding those rows first also stops the trigger computing a name from a set another batch has not finished writing.

One consequence worth knowing: a batch holds one advisory lock per distinct area for the length of its statement, so the batch size a caller chooses is what bounds its share of the lock table (max_locks_per_transaction).

put_area_code_many, put_area_in_release_many and put_relation_many take no advisory lock. They write area_code, release_area and relation, and reach area only through their foreign keys, which take FOR KEY SHARE -- compatible with the FOR NO KEY UPDATE the two functions above take, so no cycle can form against them. Each still orders its own insert on its own conflict key, so two batches of the same kind agree.

None of this holds a lock across two calls: every function here is one statement, and a caller that wraps several of them in one transaction is choosing its own lock ordering.

Concurrent partition DDL

open_release, create_release_partitions, and drop_release_partitions all take a transaction-level advisory lock on partition_lock_key() before they touch anything, so only one of them builds or drops a release's partitions at a time. This costs nothing that partition DDL was not already going to cost, because creating or dropping a partition takes an AccessExclusiveLock on the parent and those callers were never going to run concurrently anyway. What it buys is that they no longer deadlock trying: creating a partition clones the parent's foreign key to release, which needs a ShareRowExclusiveLock on release, while the caller that just inserted that release row holds a RowExclusiveLock on it, and two callers doing this at once each hold what the other needs.

open_release takes the lock before its own insert, which is what makes it safe. A caller that inserts a release row itself and then calls create_release_partitions does not get that protection: its insert took a RowExclusiveLock on release outside the lock, so two such callers running at once can still deadlock with SQLSTATE 40P01. Take pg_advisory_xact_lock(partition_lock_key()) before the insert if you build release rows by hand, or go through open_release, which does it for you.

upsert_area and upsert_area_type serialize on their own identity for a related reason: area and area_type each carry two unique constraints, ON CONFLICT can name only one of them as its arbiter, and a concurrent speculative insert that blocks on the other index surfaces as a bare 23505 instead of taking the DO UPDATE path. Two callers upserting different area type keys at the same rank do not serialize and still get the rank violation, which is the constraint doing its job.

area_key is unique across the whole catalog, not within a collection. It is composed as <authority_key>:<area_type_key>:<code>, so two collections that draw on the same authority, name their area types alike, and carry the same code compose the same key, and the second upsert_area fails with a bare 23505 on area_area_key_uq naming no remedy. Global uniqueness is the binding model: an area_key is meant to identify one real place no matter which collection reached it first. Give the two collections distinct authority keys when they are genuinely describing different places.

boundary, boundary_part, relation, and release_area are all partitioned by release_id. open_release creates a release's partitions as part of opening it, so a release created through open_release is always ready for put_area_in_release, put_boundary, put_boundaries, and put_relation to write to. create_release_partitions stays available directly for a release row created some other way; omitting it before writing to such a release fails with a no partition of relation ... found for row error, not a silent no-op. Both boundary write forms ensure release membership, so a polygon-first caller never needs to call both.

A direct catalog/bootstrap caller may use open_release and create_release_partitions, but ingestion does not write by release id. prepare_import creates the release and partitions and returns the run_id every ingestion mutation above accepts. Each mutation proves that the run is the latest attempt, still owns the release lease, and is in its permitted phase (normalizing or relating) before deriving the release id.

A published release is immutable. The run-fenced writers and attach_source_release call assert_release_mutable and raise SQLSTATE 55000 when the derived or explicit release has status completed or a non-null retired_at. attach_source_release is in that list because provenance is part of what a release publishes. Build a new release and publish it to change what clients see; there is no way to edit a published release in place.

Every write that targets an area also calls assert_area_in_collection, which raises SQLSTATE 23503 when the area's collection_id does not match the release's. The boundary writes additionally require that each source release id be one the release has already declared through release_source (see Declaring provenance below); an undeclared source release also raises SQLSTATE 23503. Both boundary writes reject a geometry whose coordinates fall outside the SRID 4326 domain (longitude beyond ±180, latitude beyond ±90) with SQLSTATE 22023.

Declaring provenance

The boundary writes' source release ids are not free-form: the release must declare them. prepare_import records the source composition from the exact manifest; a direct catalog caller can use attach_source_release, which links a release to a source_release it draws data from. attach_source_release raises SQLSTATE 23503 if either id does not exist, or if the source release belongs to a different collection than the release. verify_release also fails a release that declares no source releases at all, so attaching at least one is mandatory before publishing, even for a release with no boundaries. The source and source_release rows themselves come from upsert_source and upsert_source_release. put_artifact records what a source release's manifest declares: the logical name, format, expected sha256, and expected byte count, all of them required. record_artifact_observation records what one run actually fetched and is the call that compares the two, raising SQLSTATE 23514 when the observed digest or byte count differs from the declared one. A second identical observation is idempotent; a different observation for the same run and artifact raises SQLSTATE 55000. A retry gets a new run and new observation rows rather than rewriting the failed attempt's evidence.

Both boundary calls also follow each accepted source release through source_release to source and independently check its collection. This is intentionally redundant with attach_source_release: release_source is directly writable, and its foreign keys cannot express that the release and source belong to the same collection.

Lifecycle

Move a release from staged to published, or roll one back.

published_release is the pointer every read resolves through when it is given no target_release_id, exposed as a function so a caller can name the same release across several calls and know they all read one release rather than racing a publication that lands between them. It returns NULL for a collection that has published nothing and for a collection key the catalog does not carry, for the same reason release_at does: from the caller's side there is no release to pin either way.

Publishing the release that is already published is a no-op: it appends no publication_event row and leaves previous_release_id untouched, so rollback_publication still reaches the last release that was actually swapped away from, not the release that is currently published.

publish_import is the ingestion completion path. The run must be the latest leased attempt in publishing; the function marks it completed, removes its lease, calls the same verified pointer swap, and commits those effects atomically. A lost reply can therefore be resolved by re-reading the run without risking a published release paired with a failed run.

complete_import is the corresponding non-publishing path. The run must be the latest leased attempt in verifying; it re-checks required artifact observations and release invariants, merges the verifying phase's final metrics, marks the release and run completed, and removes the lease in one transaction without creating or changing a publication pointer. A later operator publication still goes through publish_release and re-verifies the completed release.

publish_release remains available for deliberate operator work. It refuses a release with an active import and refuses one whose latest import is not completed. It never substitutes for publish_import midway through a run.

Publication and boundary writes serialize on the collection's publication advisory lock. publish_release takes that lock before verify_release; each boundary writer takes it before assert_release_mutable. This common order closes both race directions: neither a write can commit after publication nor a publisher can accept verification performed before a waiting writer committed.

retire_releases drops a retired release's partitions (boundary, boundary_part, relation, release_area) to reclaim storage, and sets release.retired_at, but keeps the release row itself - publication_event and import_run reference it, and deleting the row would cascade both away, tearing holes in the sequenced event log hosts poll and erasing import history. Re-running retire_releases does not re-retire a release that already has retired_at set, and it never retires the currently published release.

Import

A durable, exact-attempt state machine for a long-running import. Preparation, retry, executor claim, every mutation, and publication serialize on the candidate's release key.

manifest and claim are JSON objects. The claim requires owner and runner_backend and accepts stale_after_seconds, a finite positive number defaulting to 900. Both preparation functions return (decision, reason, release_id, run_id, attempt). prepare_import returns enqueue/registered for a new candidate, enqueue/same_owner for a safe redelivery, existing/completed for a completed unpublished candidate, or error with a closed reason such as live_import, stale_import, failed, manifest_changed, or protected. An identical failed candidate returns error/failed with its failed run id; a changed failed candidate returns error/manifest_changed. It never reclaims a stale lease, rewrites a terminal attempt, or creates a replacement for a failed attempt.

retry_failed is the only replacement path. It accepts only the latest failed attempt for the same unprotected candidate, transactionally resets the candidate to the supplied exact manifest, and creates a new run id and attempt number. The failed row, its manifest snapshot, artifact selection and observations, metrics, and error remain immutable. Candidate mismatch, an older attempt, a non-failed run, and a published or retired release return an error decision without changing the catalog.

claim_import_execution returns one of claimed, occupied, completed, failed, missing, or superseded. Only a pending latest attempt with an unclaimed lease can return claimed; the first executor id is latched with its start time. A duplicate delivery receives occupied and does no work. A stale heartbeat is evidence for an operator to fail and retry the attempt, not permission to replace the executor in place.

Every phase and ingestion writer calls the same attempt fence: the run must be latest, leased, mutable, and in the operation's permitted status. advance_import accepts only the next edge in the fixed sequence from pending through publishing; it cannot skip a phase or create either terminal status. fail_import is the sole path to failed, while publish_import is the sole publishing path to completed; complete_import is the explicit non-publishing completion path. fail_import refuses a completed run and is idempotent for an already failed run, preserving the original error object rather than overwriting failure evidence.

Staging and analysis

A provider's parsed rows land in a table of their own, one per run, before anything reaches the catalog. The table is UNLOGGED: its content is rebuilt from a checksummed artifact after any restart, and skipping WAL is most of the reason staging is a separate phase at all.

The name is derived from the run's uuid rather than supplied, so it is [0-9a-f_]+ by construction and no caller-supplied text ever reaches an identifier. The staged table carries artifact, payload (jsonb), and geom. Both drop forms are idempotent, but their authority differs: a live executor supplies its identity, while the one-argument operator form refuses an active run and is reserved for terminal or orphaned tables.

create_staging keeps whatever rows are already in the table -- it is CREATE TABLE IF NOT EXISTS and nothing more. A live executor calls its fenced drop_staging form first, so the pass starts from an empty table rather than appending to an interrupted attempt's rows. GeoGenius.Staging.reset/3 is that pair, and what GeoGenius.Pipeline calls.

analyze_release runs against the release's own partitions rather than the whole table. The pipeline uses analyze_import, which applies the indexing-phase attempt fence before delegating to that operator primitive.

Views

Every view here is an ordinary view a host can SELECT from and join against; none takes an argument. Two of them assemble a join the import side would otherwise repeat at every call site, and are read through GeoGenius.Catalog.

import_run_status joins import_run to release and collection and left-joins the lease, projecting run_id, release_id, collection_key, release_key, attempt, status, owner, runner_backend, started_at, heartbeat_at, completed_at, error, stage_metrics, progress, executor_id, execution_started_at, and manifest. A terminal run has no lease row, so heartbeat_at falls back to the run's own column and progress reads as {} rather than NULL. This is what GeoGenius.status/2 reads, and what %GeoGenius.ImportRun{}'s fields are named for.

run_artifacts joins one run's immutable artifact selection to its observations. It is the forensic view for failed and successful attempts alike and projects run_id before the same source, artifact, expectation, observation, and metadata columns described below.

release_artifacts joins the release's current composition through source_release and source to artifact, projecting release_id, source_release_id, source_key, source_release_key, collection_key, artifact_id, logical_name, url, operator_supplied, format, expected_sha256, expected_bytes, observed_sha256, observed_bytes, validated_at, and metadata. Its observation comes only from the latest completed attempt; it never reaches backward into a failed run to make a release appear validated. Before any attempt completes, the observation columns are null.

Published read views

The read views come in pairs. Each release_* base carries every release, published or not, and stamps release_id on every row. Each published_* view is that base joined to the publication pointer and nothing else, so none of them takes a release argument and a pointer swap changes what all of them show at once, under MVCC, with no refresh step. The Resolution and Traversal functions above query the bases, so a non-null target_release_id reaches a release that is not the published one; GeoGenius.Published does the same with its :release_id option.

A pair projects identical columns in identical order, so the same query shape works either side of publication.

published_areas does not hide retired areas; retired_at is projected so a caller decides. It is the only view that projects it at all, because retirement is a property of an area rather than of a code, a name, or an edge between two areas. A caller filtering retired areas out of codes, names, or relations joins the matching areas view on area_id and release_id and filters there.

Codes and names hang off the area rather than off a release, and an area belongs to as many releases as carry it, so release_area_codes and release_area_names yield one row per release carrying the area. release_id is what tells those rows apart, and it is the second half of any join back to an areas view; joining on area_id alone would pair a code of one release with an area of another. parent_coverage and child_coverage on published_area_relations are ratios from 0 to 1, not the 0-to-100 percentages area_match reports.

Choosing a read API

Three ways to read the same catalog, and the choice is not a matter of taste:

A plpgsql SETOF function is an optimizer barrier: PostgreSQL cannot push a predicate, a join qualifier, or a LIMIT inside one, so the whole set materialises before the caller's own WHERE runs. Both function-backed paths pay that, and both project five of area_match's sixteen columns. Counting host rows per area through GeoGenius.Query.children_of/2 and through GeoGenius.Published.children_of/2 returns the same rows, but only the second plans as an ordinary join, and only the second can join a host projection table keyed (release_id, area_key) -- release_id never reaches the function-backed result at all.

GeoGenius.Published exposes every column of published_areas, published_area_codes, published_area_names and published_area_relations as a read-only Ecto schema, takes [String.t()] where the function-backed API takes one String.t(), and names every source with an Ecto binding (:area, :relation, :code, :name) so a caller composes further without counting positions. Its :release_id option swaps every source in the query onto the matching release_* base, so an unpublished release reads through it too. It has no view-backed equivalent for spatial resolution, name search, or a multi-hop walk; those stay with the functions above. See reading.md for worked examples.

Indexing your own attribute keys

release_area.data -- attributes on the release_areas and published_areas views and on %GeoGenius.AreaMatch{} -- holds whatever a provider put there. GeoGenius ships three indexes on release_area: the (release_id, area_id) primary key, (area_id, release_id) for the reads that arrive from an area with no release predicate, and a gist on centroid. It ships none on data, and that is deliberate rather than an oversight: test/pgtap/catalog/test_partition_index_propagation.sql asserts the shipped index set so one cannot be added by accident.

No installed function issues a containment, key-existence, or jsonpath predicate against data, and none orders by a value extracted from it -- every read carries the column through as a projection. An index here would be write cost on every import with no read to pay for it. The keys are also vendor-defined: a library that stays country- and vendor-agnostic cannot know which of them a host ranks or filters on.

A host that does filter or rank on one adds its own, on the partitioned parent:

-- Containment and key existence: attributes @> '{"lsad": "county"}', jsonpath.
CREATE INDEX my_app_release_area_attributes_idx
  ON geo_genius.release_area USING gin (data jsonb_path_ops);

-- Ranking on one extracted scalar: ORDER BY (data->>'population')::numeric DESC NULLS LAST.
CREATE INDEX my_app_release_area_population_idx
  ON geo_genius.release_area (((data->>'population')::numeric) DESC NULLS LAST);

The two are not interchangeable. GIN serves containment and key existence and cannot serve an ordered read at all: over a 31,000-row partition, a top-50 ranking query with only the GIN index in place still read all 31,000 rows through a sequential scan and a top-N sort (567 buffers, 5.2ms), and dropped to an index scan of 52 buffers and 0.2ms once the btree expression index existed. In the other direction the selective containment count went from 1,134 buffers and 5.0ms to 14 buffers and 0.1ms once the GIN index existed, which the btree cannot do.

The btree's ordering has to match the query's. A default (ascending, nulls last) btree does not serve ORDER BY expr DESC NULLS LAST when any row is missing the key -- measured at 371 buffers and 5.1ms with a plain index against 51 buffers and 0.09ms with a DESC NULLS LAST one. Declare the index the way the ORDER BY reads.

Neither index is free on the write side: over a 31,000-row bulk insert the GIN index cost roughly 20% more time, and it occupied 1.8MB against an 11MB partition. Add one because a query needs it, not in case one might.

Reaching partitions from later imports

release_area is partitioned by release_id, and create_release_partitions builds each release's partition with CREATE TABLE ... PARTITION OF. PostgreSQL propagates an index on a partitioned parent to every partition created under it afterwards, so a host indexes release_area once, on the parent, and every future release inherits it -- including a partition dropped by retire_releases and rebuilt by a later import. test/pgtap/catalog/test_partition_index_propagation.sql pins this end to end against create_release_partitions itself, for both index shapes above.

Index the parent, never an individual partition: a per-partition index covers exactly one release and the next import's partition would have none.

CREATE INDEX CONCURRENTLY is rejected on a partitioned table (cannot create index on partitioned table "release_area" concurrently), so the plain form above takes an AccessExclusiveLock while it builds every existing partition. On a catalog small enough that this is a moment, that is the whole procedure. On a large one, build it in the supported three steps -- and note that a partition created between step one and step three picks the index up automatically, so an import running alongside does not defeat it:

CREATE INDEX my_app_release_area_population_idx
  ON ONLY geo_genius.release_area (((data->>'population')::numeric) DESC NULLS LAST);

-- Per existing partition, one at a time:
CREATE INDEX CONCURRENTLY release_area_<suffix>_population_idx
  ON geo_genius.release_area_<suffix> (((data->>'population')::numeric) DESC NULLS LAST);
ALTER INDEX my_app_release_area_population_idx
  ATTACH PARTITION release_area_<suffix>_population_idx;

The parent index reads as invalid in pg_index until every existing partition has an index attached, and becomes valid on the last ATTACH.

Error contracts

A write that must reference a row reports a missing row in one of three ways, and which one depends on how the function looks the row up. Most raise SQLSTATE 23503, whether the row is absent or present but belonging to a different collection; those two shapes read differently and mean different things, so they are listed apart. SELECT ... INTO STRICT lookups use the separate P0002 contract documented below.

A row the call names does not exist:

The row exists but belongs somewhere else:

An unresolved key raises PL/pgSQL's own P0002 (no_data_found), from one of two places. A lookup written as SELECT ... INTO STRICT raises the bare message query returned no rows, naming neither the function nor the value that was not found. A plural write resolves its keys by joining instead, and reports an unresolved one through assert_resolved, which raises the same SQLSTATE with the same wording and appends the key -- query returned no rows for area key acme:city:portland. The five scalar writes delegate to their plural forms, so they carry the named message too. Every site, and what makes it fire:

verify_release and publish_release each hold one INTO STRICT lookup as well, but both check the release exists first and raise 23503, so neither reaches P0002 for a release id that names nothing.

A provider emitting an unknown authority_key is the reachable ingestion path into this family, and it is one of the sites that names the key.

Neither 23503 shape is expressible as a foreign key. An area and a release each reference their own collection independently, and a source release does the same, so nothing in the schema can say "these two must agree"; the write API says it instead, and verify_release re-checks the membership rule at publication time rather than trusting the writer.

23503 is not the only code these functions raise. 55000 covers an immutable release, an illegal run phase, a superseded or unleased attempt, an attempt-level artifact observation that another value already fixed, and a terminal status a caller tried to leave. Candidate contention is returned as an error decision from prepare_import rather than raised as 55006; executor contention is the occupied result from claim_import_execution.

23514 has four producers, and the two most reachable are not the artifact check: publish_release raises it for a release that fails verification, rollback_publication for a collection with no previous release to roll back to, record_artifact_observation for an artifact whose observed bytes do not match what the manifest expected, and the publication trigger for a publication row pointed at a release that is not a completed release of that collection.

22004 is a required argument left null, asserted for every guarded argument of every guarded function in test/pgtap/invariants/test_required_arguments.sql. The one-argument drop_staging(NULL) reaches staging_table_name, which raises 22004 on its behalf. Its two-argument sibling and create_staging use the shared attempt fence, including its required-argument checks.

22023 is an argument outside its domain. It covers a geometry that is not SRID 4326, not a polygon type, or empty; a manifest or a resolve input that is not a JSON object; an unknown relation type; an unknown import phase; a non-positive or non-finite stale_after_seconds; a keep below one; a max_depth below one; a longitude or latitude out of range; a non-positive radius; and lon supplied without lat.

See installation.md for prefixes, upgrades, and the four notes on retired areas, measured versus asserted relations, traversal depth, and reacting to publication. The published_* views above are the read-side contract most hosts join directly rather than calling a resolution function; GeoGenius.Published is the Elixir surface over them.