feat(datastore): container virtual upstream list and rules reads (S32 plan: 4/19)

What

The two fixed-count reads a container virtual resolution needs.

• The ordered upstream list, joined to repositories and left-joined to container_remote_repositories.
• The batched rules read across every association in that list.

Both issue one statement whatever the upstream count. Both project no credential column.

The projection carries no credential column

container_remote_repositories holds the credential in five columns that a check constraint keeps present or absent together. A row type carrying them would put the whole sealed unit in every resolution's working set, for every position, including the ones no fetch reaches. The row type names its columns explicitly instead of selecting the table, and a test asserts all five are absent. A remote fetch resolves its own credentials at request time.

Both reads fail closed past their bound

Each read takes one row past its bound and refuses the whole result when that row arrives. Truncating is the unsafe direction. A dropped upstream position is one whose content never contradicts a sibling's established absence, so a clipped list answers 404 MANIFEST_UNKNOWN for an image that exists, and a Docker client caches that 404 as permanent. A clipped rule set drops deny rules. Both bounds sit above ADR-004's cap of 20 upstreams, so neither fires in normal operation.

The read flags soft-deleted upstreams rather than filtering them

The spec gives the skip record a purpose: it separates a repository whose upstreams were deleted from under it from one that was never configured. A WHERE clause would leave nothing to attribute, so the exclusion moves to the assembly step, which is where the spec puts it.

Tests

Unit tests over the statement text, the projected column set, and the bounds. Integration tests over a seeded virtual repository: ordering, the soft_deleted flag, the empty list, a remote position whose binding row is absent, a statement count measured at 2 and at 20 upstreams, and both fail-closed arms.

An *_explain_integration_test.go suite too. A statement count cannot see an Append: a query joining on the JOIN conditions alone plans an Append over all 64 partitions of every table past the first and passes every behavioral subtest. The suite asserts one partition per table and pins the joined table set.

Merge order

Merge order stays !2741 (merged), then !2743 (merged), then !2756 (merged).

Merge !2741 (merged) first. It records the two read-time bounds in the spec and why they fail closed. Until it lands, the spec says "S32 does not re-enforce it at read time" and its Error Cases table carries no row for either refusal, so the spec and this branch disagree about whether a read-side bound may exist.

Size

2,033 added lines across 5 files, past the 500-line guideline. Splitting does not help: the two reads share a row type, a bound rationale and a test fixture, so a split would land a read whose tests live in another MR.

Group	Lines
container_virtual_resolution.go	388
container_virtual_resolution_integration_test.go	996
container_virtual_resolution_test.go	435
container_virtual_resolution_explain_integration_test.go	205
query_names.go	9

Source is 397 lines. The rest is test.

Scope notes

• No docs/testing/ scenario changes. No route reaches these reads until the composition root lands, and the end-to-end step owns the catalog.
• No docs/user/** changes. A datastore read has no customer-visible surface.

Database Review Evidence

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17.10 container (matching GL_PG_CURR_VERSION from .gitlab-ci-other-versions.yml), with synthesized seed data rolled back per query and the container torn down at the end of the run. Numbers reflect moderate cardinality and do not capture production-scale effects. See Database review evidence for seed sizing, methodology, and the anomalies the skill flags. Expand each row's details for the seed shape, rendered SQL, bound args, and raw plan.

Method	Plan node	Index	Rows (plan / actual)	Cost	Time	Buffers (hit / read)	Partitions
datastore.ContainerVirtualResolutionStore.RulesByUpstreams	Limit	index_cvur_on_ns_id_repository_upstream_id	15 / 15	39.90	0.012ms	10 / 0	1/64
datastore.ContainerVirtualResolutionStore.listUpstreams	Limit	unique_cvru_ns_id_repository_id_position, pk_repositories, unique_container_remote_repositories_namespace_id_repository_id	20 / 20	338.93	0.075ms	113 / 0	1/64, 1/64, 1/64

datastore.ContainerVirtualResolutionStore.RulesByUpstreams

Summary: Plan matches the method's intent. The namespace equality the statement repeats for this reason prunes to one of 64 partitions, and index_cvur_on_ns_id_repository_upstream_id serves the whole predicate, association list included, as one index scan. Estimate and reality agree (15 / 15) and no anomalies appear.

Seed shape: namespaces=1, repositories=5250, container_virtual_repositories=250, container_virtual_repository_upstreams=5000, container_virtual_upstream_rules=5080

Rendered SQL:

SELECT container_virtual_upstream_rules.container_virtual_repository_upstream_id AS "container_virtual_repository_upstream_id",
     container_virtual_upstream_rules.rule_type AS "container_virtual_upstream_rules.rule_type",
     container_virtual_upstream_rules.target_field AS "container_virtual_upstream_rules.target_field",
     container_virtual_upstream_rules.pattern AS "container_virtual_upstream_rules.pattern"
FROM public.container_virtual_upstream_rules
WHERE (container_virtual_upstream_rules.namespace_id = $1::uuid) AND (container_virtual_upstream_rules.container_virtual_repository_upstream_id IN ($2::uuid, $3::uuid, $4::uuid))
LIMIT $5;

Bound args: [5397cc55-bb47-75df-b5c1-f69d46b83d70, 619dd680-5e38-7fb8-8d47-34d75a1a31b7, 61bffbc2-f118-7220-bcfd-12c42413c0bb, a36428ca-e38d-71b1-8e8d-88e244ce72ba, 20001]

Three associations of one virtual repository, each carrying five rules. The trailing argument is containerVirtualUpstreamRulesLimit.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..39.90 rows=15 width=42) (actual time=0.008..0.012 rows=15 loops=1)
  Buffers: shared hit=10
  ->  Index Scan using container_virtual_upstream_r_namespace_id_container_virtu_idx40 on container_virtual_upstream_rules_p40 container_virtual_upstream_rules  (cost=0.28..39.90 rows=15 width=42) (actual time=0.008..0.011 rows=15 loops=1)
        Index Cond: ((namespace_id = '5397cc55-bb47-75df-b5c1-f69d46b83d70'::uuid) AND (container_virtual_repository_upstream_id = ANY ('{619dd680-5e38-7fb8-8d47-34d75a1a31b7,61bffbc2-f118-7220-bcfd-12c42413c0bb,a36428ca-e38d-71b1-8e8d-88e244ce72ba}'::uuid[])))
        Buffers: shared hit=10
Planning:
  Buffers: shared hit=20
Planning Time: 0.199 ms
Execution Time: 0.020 ms

Timings: planning 0.199ms, execution 0.020ms, total 0.219ms.

datastore.ContainerVirtualResolutionStore.listUpstreams

Summary: Plan matches the method's intent. The association scan uses unique_cvru_ns_id_repository_id_position, whose trailing position column supplies the ORDER BY order, so no Sort node appears above it; the foundation row and the remote binding each arrive through one index lookup per position, and all three tables prune to one of 64 partitions. Estimate and reality agree (20 / 20) with no buffer reads. One note on plan stability: with the remote table seeded at 2500 rows rather than 5000, the planner instead hashed that partition whole and added a Sort. That is a cardinality choice over a small partition, not a missing index — the same unique index covers the predicate in both plans.

Seed shape: namespaces=1, repositories=7750, container_virtual_repositories=250, container_virtual_repository_upstreams=5000, container_remote_repositories=5000

250 virtual repositories hold 20 positions each, so the queried container_virtual_repository_id discriminates 20 rows out of 5000 in the partition. Half the queried positions carry a remote binding, so the LEFT JOIN both matches and misses.

Rendered SQL:

SELECT container_virtual_repository_upstreams.id AS "container_virtual_repository_upstreams.id",
     container_virtual_repository_upstreams.upstream_repository_id AS "container_virtual_repository_upstreams.upstream_repository_id",
     container_virtual_repository_upstreams.position AS "container_virtual_repository_upstreams.position",
     repositories.name AS "repositories.name",
     repositories.kind AS "repositories.kind",
     repositories.format AS "repositories.format",
     repositories.soft_deleted_at IS NOT NULL AS soft_deleted,
     container_remote_repositories.id AS "container_remote_repositories.id",
     container_remote_repositories.repository_id AS "container_remote_repositories.repository_id",
     container_remote_repositories.url AS "container_remote_repositories.url",
     container_remote_repositories.auth_url AS "container_remote_repositories.auth_url",
     container_remote_repositories.auth_status AS "container_remote_repositories.auth_status",
     container_remote_repositories.cache_validity_hours AS "container_remote_repositories.cache_validity_hours",
     container_remote_repositories.last_health_status AS "container_remote_repositories.last_health_status"
FROM public.container_virtual_repository_upstreams
     INNER JOIN public.repositories ON ((repositories.namespace_id = container_virtual_repository_upstreams.namespace_id) AND (repositories.id = container_virtual_repository_upstreams.upstream_repository_id))
     LEFT JOIN public.container_remote_repositories ON ((container_remote_repositories.namespace_id = $1::uuid) AND (container_remote_repositories.repository_id = container_virtual_repository_upstreams.upstream_repository_id))
WHERE ((container_virtual_repository_upstreams.namespace_id = $2::uuid) AND (container_virtual_repository_upstreams.container_virtual_repository_id = $3::uuid)) AND (repositories.namespace_id = $4::uuid)
ORDER BY container_virtual_repository_upstreams.position ASC
LIMIT $5;

Bound args: [56a6d52d-8d66-712a-9398-a133dd490256, 56a6d52d-8d66-712a-9398-a133dd490256, 1f3cb016-1901-77d9-b03f-8afda0d34669, 56a6d52d-8d66-712a-9398-a133dd490256, 41]

The namespace id binds three placeholders: the remote binding's ON clause, the association predicate, and the repeated foundation-row predicate. The trailing argument is containerVirtualUpstreamListLimit + 1.

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.85..338.93 rows=20 width=186) (actual time=0.022..0.075 rows=20 loops=1)
  Buffers: shared hit=113
  ->  Nested Loop Left Join  (cost=0.85..338.93 rows=20 width=186) (actual time=0.022..0.074 rows=20 loops=1)
        Buffers: shared hit=113
        ->  Nested Loop  (cost=0.56..192.68 rows=20 width=71) (actual time=0.013..0.040 rows=20 loops=1)
              Buffers: shared hit=63
              ->  Index Scan using container_virtual_repository_namespace_id_container_virtu_key50 on container_virtual_repository_upstreams_p50 container_virtual_repository_upstreams  (cost=0.28..38.43 rows=20 width=52) (actual time=0.006..0.007 rows=20 loops=1)
                    Index Cond: ((namespace_id = '56a6d52d-8d66-712a-9398-a133dd490256'::uuid) AND (container_virtual_repository_id = '1f3cb016-1901-77d9-b03f-8afda0d34669'::uuid))
                    Buffers: shared hit=3
              ->  Index Scan using repositories_p50_pkey on repositories_p50 repositories  (cost=0.28..7.70 rows=1 width=67) (actual time=0.001..0.001 rows=1 loops=20)
                    Index Cond: ((id = container_virtual_repository_upstreams.upstream_repository_id) AND (namespace_id = '56a6d52d-8d66-712a-9398-a133dd490256'::uuid))
                    Buffers: shared hit=60
        ->  Index Scan using container_remote_repositories_p5_namespace_id_repository_id_idx on container_remote_repositories_p50 container_remote_repositories  (cost=0.28..7.30 rows=1 width=122) (actual time=0.002..0.002 rows=0 loops=20)
              Index Cond: ((namespace_id = '56a6d52d-8d66-712a-9398-a133dd490256'::uuid) AND (repository_id = container_virtual_repository_upstreams.upstream_repository_id))
              Buffers: shared hit=50
Planning:
  Buffers: shared hit=78 read=1
Planning Time: 0.461 ms
Execution Time: 0.099 ms

Timings: planning 0.461ms, execution 0.099ms, total 0.560ms.

Related to #291 (closed)