feat(datastore): npm virtual upstream store and delete-side guard (S17 Phase 6 plan: 39/42)

Why

The npm virtual association surface has had a schema on main since feat(npm): npm_virtual_repository_upstreams sch... (!2066 - merged) • Dzmitry (Dima) Meshcharakou • 19.4 and no code that writes it: the S31 reads in feat(npm): the ordered upstream list read 1/2 (... (!2164 - merged) • Dzmitry (Dima) Meshcharakou • 19.4 and feat(npm): the batched upstream rules read 2/2 ... (!2165 - merged) • Dzmitry (Dima) Meshcharakou • 19.4 read an upstream list nothing produces.

The delete side is the visible half of that gap. ADR-009 requires a 409 for deleting a repository that any virtual repository lists as an upstream, under either destructive value, and names the junction key's NO ACTION the backstop rather than the answer. Today the backstop is the whole answer: the key's 23503 becomes ErrRepositoryNotEmpty, what an ordinary non-empty repository gets. The npm virtual-kind step recorded that as an open deviation, feat(managementapi): serve npm's virtual kind (... (!2665 - merged) • Hayley Swimelar • 19.5.

This step lands both write halves at the store. Routes, the 409 mapping, and the audit events are Step 40's.

Acceptance is the store-level halves of AC #80 to #82 (the two concurrent pairs serialize on the documented locks, positions contiguous, cap exact, validation in the stated order) and the store half of AC #110 (closed) (the typed error).

What

Read the step from main. Step 39 there is this npm store and guard, since the resequencing merged as 90e9653f8 in docs(plans): re-home the phase 6 association su... (!2694 - merged) • Hayley Swimelar • 19.5. Step 39's scope, files, acceptance, and tests are byte-identical between the commit this was built from and what landed, so the 39/42 marker and the build both resolve to the same step.

Step 40 must follow this promptly. The guard widens what RepositoryStore.Delete refuses ahead of the handler that maps it: writeDeleteStoreError maps only ErrRepositoryNotEmpty and ErrNotFound, so ErrRepositoryIsVirtualUpstream falls through to the internal-error path. Deleting an npm repository that a virtual repository lists therefore answers 500 where it answers 409 today, and destructive=true stops reaching the 202 tombstone arm for that case (AGENTS.md guardrail 20). The window is bounded rather than open: NewNpmVirtualUpstreamStore has no production caller, and nothing outside this MR's tests writes either junction table, so only a directly-seeded row reaches the guard at all.

Positions are pinned 1-based in code, because the CHECK does not pin them. check_nvru_position_non_negative admits position >= 0 on npm where the container twin admits >= 1. Every mutation renumbers the whole list from one, so a list that was not contiguous leaves contiguous, and the settled position is what the mutation reports.

The guard is one function at one call site. checkRepositoryUpstreamAssociations is called once, inside deleteRepositoryTx. feat(managementapi): serve the container virtua... (!2666 - merged) • Hayley Swimelar • 19.5 carries a byte-identical copy of the surrounding restructure, so the whole of this change in that region is the one insertion point plus the transaction it needs. That MR has since merged, as has the npm virtual-kind step this branch was stacked on, feat(managementapi): serve npm's virtual kind (... (!2665 - merged) • Hayley Swimelar • 19.5. This branch targets main directly now, so the sequencing question is closed.

Every kind's delete now runs in a transaction. The update lock has to precede the association check or the check is not authoritative: a delete that reads the associations from its own snapshot passes, blocks inside the DELETE on a concurrent associate's FOR SHARE, and unblocks into the 23503 that reports as "not empty". Locking first makes the delete wait for the associate's verdict and re-read it. The hosted and remote path goes from one autocommit statement to a transaction of the row lock, the association read on npm, the same cascading DELETE, and the commit.

One renumber statement, and its cast is load-bearing. The renumber is a single UPDATE ... FROM (VALUES ...) joined on the primary key, not a row-at-a-time loop, which docs/dev/database-query-patterns.md forbids by name. pg.UUID() emits $n::uuid inside the VALUES row and that cast is what types the column: PostgreSQL carries no uuid = text operator, so dropping it fails the join outright rather than degrading it. The call site says so, because nothing Go-side would warn.

Both of the table's unique keys are classified, including the deferred one. The immediate no-duplicate-upstream key answers ErrVirtualUpstreamDuplicate. The position key is DEFERRABLE INITIALLY DEFERRED, so its 23505 arrives from tx.Commit() rather than from a statement, which the migration calls out as the write path's obligation: unmatched there, it surfaces as an unclassified 500 with no query-labelled metric behind it. It answers ErrVirtualUpstreamPositionConflict. Both sentinels return unwrapped, so PgError.Detail cannot carry the namespace and repository ids into a rendered error.

Two deviations, both deliberate and both disclosed in the sidecar. The mutation's list read carries no LIMIT, against that document's bounding rule, because a truncating bound would renumber a partial list and a refusing bound would trap the repair path, leaving an over-cap list unable to dissociate back under. Neither lock wait is bounded, as the parent's virtual delete already was: the service sets no lock_timeout and no statement_timeout anywhere. Bounding them is The repository DELETE's hard-delete phase waits... (#902) • Unassigned.

parentRepositoryIsActive's doc comment moved into its sidecar rather than gaining a sentence. Its census of repositories-row writers said every one of them runs a single statement on the pool, which this MR makes false. The function is unexported, so the caps gate holds its doc to one line and fails any edit inside the block, with no waiver. Measured, not assumed. The rationale moved to repository_parent_gate.md, where the census now names RepositoryStore.Delete beside SoftDelete. No behavior changed in that file.

Nine files outside the step's list changed to fix claims this branch falsifies (guardrail 17), the largest being maxNpmVirtualUpstreams' doc and its contract page, which both said no write path enforces the cap. Two of the nine arrived with the rebase onto Step 38: its container delete test calls the entry point this step renames, and its migration suite's assertion message named the old one.

npm virtual: the association write path must re... (#979 - closed) • Unassigned names two row shapes no constraint can express, and this MR refuses both at the write path it asked for: a self-referencing upstream falls out of the kind rule, a soft-deleted one out of the existence rule, with a test each. The issue stays open, because it also asks for the refusal at the route.

One residual window this guard does not close, and #979 (closed) does not describe. The guarded transaction rolls back on the contents 23503, and the destructive arm's SoftDelete then runs in a second transaction that reads no association. An associate committing in that gap leaves a junction row naming a tombstoned upstream, which RepositoryReaper.finalize can never reap past and which books as purge_outcome="repository_not_empty", the same label as a repository stalled by artifacts. Closing it needs a tombstone-side check, which is not this step's. Filed as Virtual upstream association can commit between... (#1373) • Unassigned.

Observability re-baselining. repositories_delete and repositories_delete_virtual now measure the cascade alone, because the row-lock wait moved into the new repositories_select_lock_for_delete series. A panel or alert keyed on either delete name steps down at deploy and loses the contention signal, so sum the series.

Type and exemptions. feat by the operator's ruling, and the label follows it: the store and the delete-side guard are production code, whatever the routes that reach them later. The exemptions a maintenance type would have taken silently are carried anyway. No docs/testing/ scenario is affected, because none can reach a surface with no route (guardrail 12), and the issue link is below.

Size. 4,064 reviewable lines across 25 files, counting added and removed: 1,047 production Go, 2,299 test, 718 in-tree markdown (guardrail 18). 744 of the production lines are the one new file, read as a unit, and repository_parent_gate.go's 86 are a net -84 doc relocation rather than new logic. Three of the 25 files answer round 1 rather than implementing the step: internal/virtual/position.go and npm_virtual_remote_lookup_join_order.md carry the cap claim this store makes checkable, and the npm_virtual_repository_upstreams_schema_notes.md sidecar drops the last reference to the function this step renames. The markdown is where the comment caps gate forces rationale that cannot sit in a doc comment. Splitting the store from the guard is the one split available and it does not help: the two associate-versus-delete race tests assert a single lock-order pair, so each half alone can demonstrate neither.

Test plan

#	Criterion	Tests
AC #80	An associate and its upstream's delete never both succeed, and concurrent mutations apply serially with positions contiguous and the cap exact	TestNpmVirtualUpstreamStore_AssociateVersusUpstreamDelete_DeleteCommitsFirst, ..._AssociateCommitsFirst, ..._ConcurrentAssociates_ApplyInSomeSerialOrder, ..._ConcurrentAssociatesKeepTheCapExact, ..._Associate_CapIsExact
AC #81	An associate concurrent with the virtual repository's own delete serializes on the child-row lock, and no association survives it	TestNpmVirtualUpstreamStore_AssociateVersusVirtualRepositoryDelete, ..._VirtualRepositoryDeleteRemovesACommittedAssociation, ..._MissingVirtualChildRow
AC #82	Validation applies in its stated order, so a duplicate at the cap answers duplicate	TestNpmVirtualUpstreamStore_Associate_DuplicateBeatsTheCap, ..._RefusesADuplicateBelowTheCap, TestNpmVirtualUpstreamListAdmits_RefusesTheDuplicateBeforeTheCap
AC #110 (closed)	A delete blocked by a junction row surfaces the typed error and removes nothing, and succeeds once the association is gone	TestRepositoryStore_Delete_VirtualNpmUpstreamTargetBlocked, TestCheckRepositoryUpstreamAssociations_AnswersPerFormat, TestNpmVirtualUpstreamStore_AssociateVersusUpstreamDelete_AssociateCommitsFirst

Error and edge path	Tests
Position below 1, more than one past the end, and a reposition one past the end settling at the end	..._Associate_RefusesAPositionOutsideTheRange, ..._Reposition_OnePastTheEndSettlesAtTheEnd, TestNpmVirtualUpstreamInsertIndex_SplicesWithinOnePastTheEnd, TestNpmVirtualUpstreamMoveIndex_ClampsOnePastTheEnd
An unknown upstream, another namespace's, and a soft-deleted one answer alike	..._Associate_HidesWhichUnassociableUpstreamItWas
A virtual upstream, a self-reference, a Maven upstream, and a positive remote upstream	..._Associate_RefusesAnUnassociableUpstream, ..._Associate_AdmitsARemoteUpstream
A mutation over a seeded non-contiguous or 0-based list leaves it contiguous and 1-based	..._MutationsSettleASeededNonContiguousList, TestNpmVirtualUpstreamSettled_NumbersAnyStartingStateFromOne
A list seeded past the cap dissociates back under it	..._DissociateRepairsAListSeededPastTheCap
Dissociating the only row leaves an empty list, which the renumber must not build a VALUES table for	..._DissociateTheOnlyRowLeavesAnEmptyList, TestRenumberNpmVirtualUpstreams_WritesNothingWhenNoRowMoved
Both unique keys classified, the deferred one on the commit return	TestNpmVirtualUpstreamCommitError_ClassifiesTheDeferredPositionKey, ..._MapsTheRealDeferredPositionViolation, TestNpmVirtualUpstreamInsertError_ClassifiesTheImmediateDuplicateKey
The renumber writes every moved row in one statement, and every write prunes to one namespace	TestRenumberNpmVirtualUpstreamsStmt_WritesEveryMovedRowInOneStatement, TestNpmVirtualUpstreamWriteStmts_PruneToOneNamespace
An association id belonging to another virtual repository, and a tombstoned virtual repository still mutating	..._RefusesAForeignAssociationID, ..._Associate_TombstonedVirtualRepository
Nil context, nil client, a zero id in every position, and the guard order	..._ArgumentGuards, ..._GuardsRunInOrder, TestNewNpmVirtualUpstreamStore_PanicsOnNilClient, TestNpmVirtualUpstreamReferencesRepository_ArgumentGuards

Conformance: not applicable, no protocol path changes.

Database Review Evidence

No file under internal/datastore/migrations/sql/ changed, so migration mode did not run.

Queries

Note

Plans are from EXPLAIN (ANALYZE, BUFFERS) against an ephemeral PostgreSQL 17 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. The Index column names the declared index; the plans below name the partition's copy of it, whose number follows the seeded namespace. 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.npmVirtualRepositoryLockForMutationStmt	Limit	unique_nvr_ns_id_repository_id	1 / 1	8.31	0.016ms	4 / 0	1/64
datastore.npmVirtualUpstreamCandidateStmt	Limit	pk_repositories	1 / 1	8.31	0.014ms	4 / 0	1/64
datastore.npmVirtualUpstreamsForMutationStmt	Index Scan	unique_nvru_ns_id_repository_id_position	20 / 20	38.43	0.017ms	3 / 0	1/64
datastore.npmVirtualUpstreamReferenceStmt	Limit	index_nvru_on_ns_id_upstream_repository_id	1 / 1	8.30	0.011ms	3 / 0	1/64
datastore.insertNpmVirtualUpstreamStmt	Insert	n/a	0 / 0	0.01	0.066ms	10 / 0	1/64
datastore.renumberNpmVirtualUpstreamsStmt	Update	pk_npm_virtual_repository_upstreams	0 / 0	24.97	0.169ms	64 / 3	1/64
datastore.deleteNpmVirtualUpstreamStmt	Delete	pk_npm_virtual_repository_upstreams	0 / 0	8.30	0.099ms	10 / 1	1/64
datastore.lockRepositoryForDeleteStmt	Limit	pk_repositories	1 / 1	8.31	0.014ms	4 / 0	1/64

Query notes:

• datastore.npmVirtualUpstreamsForMutationStmt: the mutation read carries no LIMIT, which deviates from "Bounding result sets" in docs/dev/database-query-patterns.md. Its predicate is an equality on the virtual repository's own key, so the result is one repository's upstream list rather than a share of the table, and NpmVirtualUpstreamStore caps that list at 20 on every write path. The bound is refused rather than forgotten: a truncating bound would renumber a partial list and leave the rows past it holding positions that collide at COMMIT, and a refusing bound would trap the repair path for a list seeded over the cap outside the store.

datastore.npmVirtualRepositoryLockForMutationStmt

Summary: Plan matches the method's intent: an Index Scan on the partition's copy of unique_nvr_ns_id_repository_id under LockRows, which is the FOR UPDATE every mutation serializes on. The namespace literal prunes to one partition of 64 and the read touches four buffers. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

SELECT npm_virtual_repositories.id AS "npm_virtual_repositories.id"
FROM public.npm_virtual_repositories
WHERE (npm_virtual_repositories.namespace_id = $1::uuid) AND (npm_virtual_repositories.repository_id = $2::uuid)
LIMIT $3
FOR UPDATE;

Bound args: [48362d6e-d973-70f3-853a-302e23f5846e, 37df1d71-ed7f-7250-8e32-1cbaf0c1facb, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.31 rows=1 width=26) (actual time=0.015..0.016 rows=1 loops=1)
  Buffers: shared hit=4
  ->  LockRows  (cost=0.28..8.31 rows=1 width=26) (actual time=0.015..0.015 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Index Scan using npm_virtual_repositories_p21_namespace_id_repository_id_idx on npm_virtual_repositories_p21 npm_virtual_repositories  (cost=0.28..8.30 rows=1 width=26) (actual time=0.012..0.012 rows=1 loops=1)
              Index Cond: ((namespace_id = '48362d6e-d973-70f3-853a-302e23f5846e'::uuid) AND (repository_id = '37df1d71-ed7f-7250-8e32-1cbaf0c1facb'::uuid))
              Buffers: shared hit=3
Planning Time: 0.077 ms
Execution Time: 0.029 ms

Timings: planning 0.077ms, execution 0.029ms, total 0.106ms.

datastore.npmVirtualUpstreamCandidateStmt

Summary: Plan matches the method's intent: a partition-pruned primary-key Index Scan under LockRows for the FOR SHARE this read holds on the upstream's parent row. soft_deleted_at IS NULL applies as a Filter on the single row the key already found, so it costs nothing. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

SELECT repositories.kind AS "repositories.kind",
repositories.format AS "repositories.format"
FROM public.repositories
WHERE ((repositories.namespace_id = $1::uuid) AND (repositories.id = $2::uuid)) AND (repositories.soft_deleted_at IS NULL)
LIMIT $3
FOR SHARE;

Bound args: [8e1ef8c4-f893-78e8-9249-5efb52de0a92, 0004d622-9dff-7eab-9307-914c715b6d71, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.29..8.31 rows=1 width=14) (actual time=0.014..0.014 rows=1 loops=1)
  Buffers: shared hit=4
  ->  LockRows  (cost=0.29..8.31 rows=1 width=14) (actual time=0.014..0.014 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Index Scan using repositories_p11_pkey on repositories_p11 repositories  (cost=0.29..8.30 rows=1 width=14) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: ((id = '0004d622-9dff-7eab-9307-914c715b6d71'::uuid) AND (namespace_id = '8e1ef8c4-f893-78e8-9249-5efb52de0a92'::uuid))
              Filter: (soft_deleted_at IS NULL)
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=8
Planning Time: 0.192 ms
Execution Time: 0.028 ms

Timings: planning 0.192ms, execution 0.028ms, total 0.22ms.

datastore.npmVirtualUpstreamsForMutationStmt

Summary: Plan matches the method's intent, and the deferred position unique index serves it twice: it satisfies the (namespace_id, npm_virtual_repository_id) predicate and supplies ORDER BY position, so no Sort node appears above the scan. Twenty rows returned against 5,000 junction rows seeded in the partition, with the estimate exact. The read carries no LIMIT; the query notes above give the reason.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

SELECT npm_virtual_repository_upstreams.id AS "npm_virtual_repository_upstreams.id",
npm_virtual_repository_upstreams.upstream_repository_id AS "npm_virtual_repository_upstreams.upstream_repository_id",
npm_virtual_repository_upstreams.position AS "npm_virtual_repository_upstreams.position"
FROM public.npm_virtual_repository_upstreams
WHERE (npm_virtual_repository_upstreams.namespace_id = $1::uuid) AND (npm_virtual_repository_upstreams.npm_virtual_repository_id = $2::uuid)
ORDER BY npm_virtual_repository_upstreams.position ASC;

Bound args: [c00b2792-79fa-7d7b-a009-68af651b674d, 000aff28-d256-7745-9637-24e6dec98d2c]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Index Scan using npm_virtual_repository_upstre_namespace_id_npm_virtual_rep_key7 on npm_virtual_repository_upstreams_p07 npm_virtual_repository_upstreams  (cost=0.28..38.43 rows=20 width=36) (actual time=0.014..0.017 rows=20 loops=1)
  Index Cond: ((namespace_id = 'c00b2792-79fa-7d7b-a009-68af651b674d'::uuid) AND (npm_virtual_repository_id = '000aff28-d256-7745-9637-24e6dec98d2c'::uuid))
  Buffers: shared hit=3
Planning Time: 0.117 ms
Execution Time: 0.027 ms

Timings: planning 0.117ms, execution 0.027ms, total 0.144ms.

datastore.npmVirtualUpstreamReferenceStmt

Summary: Plan matches the method's intent: the delete-side guard rides index_nvru_on_ns_id_upstream_repository_id, the reverse-lookup index keyed on the upstream rather than on a virtual repository, and stops at the first row. One partition of 64, three buffer hits. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

SELECT npm_virtual_repository_upstreams.id AS "npm_virtual_repository_upstreams.id"
FROM public.npm_virtual_repository_upstreams
WHERE (npm_virtual_repository_upstreams.namespace_id = $1::uuid) AND (npm_virtual_repository_upstreams.upstream_repository_id = $2::uuid)
LIMIT $3;

Bound args: [218041d2-97e3-7d52-bb13-d2bf1f241725, 0004fae4-88b6-748e-9775-f956fe1aff60, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.28..8.30 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
  Buffers: shared hit=3
  ->  Index Scan using npm_virtual_repository_upstr_namespace_id_upstream_reposi_idx19 on npm_virtual_repository_upstreams_p19 npm_virtual_repository_upstreams  (cost=0.28..8.30 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)
        Index Cond: ((namespace_id = '218041d2-97e3-7d52-bb13-d2bf1f241725'::uuid) AND (upstream_repository_id = '0004fae4-88b6-748e-9775-f956fe1aff60'::uuid))
        Buffers: shared hit=3
Planning Time: 0.071 ms
Execution Time: 0.022 ms

Timings: planning 0.071ms, execution 0.022ms, total 0.093ms.

datastore.insertNpmVirtualUpstreamStmt

Summary: A single-row Insert routed to one partition by the namespace literal, with the three foreign keys firing as constraint triggers rather than as plan nodes. Sub-millisecond against 5,000 rows already in the partition. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

INSERT INTO public.npm_virtual_repository_upstreams (id, namespace_id, npm_virtual_repository_id, upstream_repository_id, position)
VALUES ($1::uuid, $2::uuid, $3::uuid, $4::uuid, $5::integer);

Bound args: [c0acc324-2e36-7ad9-80c5-88149889cd41, 36b931b5-d0ed-7503-8bf0-73764be4da27, 001682fa-e32e-7cb9-a350-8ed3583a3b54, fff1cbde-a7b3-760f-91fb-3aecd48624c9, 21]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Insert on npm_virtual_repository_upstreams  (cost=0.00..0.01 rows=0 width=0) (actual time=0.065..0.066 rows=0 loops=1)
  Buffers: shared hit=10
  ->  Result  (cost=0.00..0.01 rows=1 width=68) (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.032 ms
Trigger for constraint fk_nvru_namespace_id_namespaces on npm_virtual_repository_upstreams_p44: time=0.020 calls=1
Trigger for constraint fk_nvru_repository_id_npm_virtual_repositories on npm_virtual_repository_upstreams_p44: time=0.088 calls=1
Trigger for constraint fk_nvru_upstream_repository_id_repositories on npm_virtual_repository_upstreams_p44: time=0.125 calls=1
Execution Time: 0.325 ms

Timings: planning 0.032ms, execution 0.325ms, total 0.357ms.

datastore.renumberNpmVirtualUpstreamsStmt

Summary: Plan confirms the one-statement renumber: a Nested Loop drives the three-row VALUES scan into the partition's primary key, so every moved row is written by one statement rather than by a row-at-a-time loop. Index Cond: ((id = "*VALUES*".column1) AND ...) is the evidence for the $n::uuid cast the call site calls load-bearing, because PostgreSQL carries no uuid = text operator for that join. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

UPDATE public.npm_virtual_repository_upstreams
SET position = renumber.position
FROM (
VALUES ($1::uuid, $2::integer),
($3::uuid, $4::integer),
($5::uuid, $6::integer)
) AS renumber (id, position)
WHERE (npm_virtual_repository_upstreams.namespace_id = $7::uuid) AND (npm_virtual_repository_upstreams.id = renumber.id);

Bound args: [f3692d51-73ee-7b39-8cfe-cc405bc684f5, 2, b5f6dbd0-7736-7411-9c1a-a314d6a9319c, 3, c9ee2e96-04c4-7d6a-bb62-228f85f85595, 1, e3603868-d078-776a-91b5-25dbde9b5621]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Update on npm_virtual_repository_upstreams  (cost=0.28..24.97 rows=0 width=0) (actual time=0.168..0.169 rows=0 loops=1)
  Update on npm_virtual_repository_upstreams_p09 npm_virtual_repository_upstreams_1
  Buffers: shared hit=64 read=3
  ->  Nested Loop  (cost=0.28..24.97 rows=3 width=58) (actual time=0.011..0.017 rows=3 loops=1)
        Buffers: shared hit=9
        ->  Values Scan on "*VALUES*"  (cost=0.00..0.04 rows=3 width=64) (actual time=0.003..0.005 rows=3 loops=1)
        ->  Index Scan using npm_virtual_repository_upstreams_p09_pkey on npm_virtual_repository_upstreams_p09 npm_virtual_repository_upstreams_1  (cost=0.28..8.30 rows=1 width=26) (actual time=0.003..0.003 rows=1 loops=3)
              Index Cond: ((id = "*VALUES*".column1) AND (namespace_id = 'e3603868-d078-776a-91b5-25dbde9b5621'::uuid))
              Buffers: shared hit=9
Planning:
  Buffers: shared hit=3
Planning Time: 0.136 ms
Trigger for constraint fk_nvru_namespace_id_namespaces on npm_virtual_repository_upstreams_p09: time=0.025 calls=3
Trigger for constraint fk_nvru_repository_id_npm_virtual_repositories on npm_virtual_repository_upstreams_p09: time=0.147 calls=3
Trigger for constraint fk_nvru_upstream_repository_id_repositories on npm_virtual_repository_upstreams_p09: time=0.286 calls=3
Execution Time: 0.818 ms

Timings: planning 0.136ms, execution 0.818ms, total 0.954ms.

datastore.deleteNpmVirtualUpstreamStmt

Summary: A primary-key Delete in one partition. The statement itself is trivial and the cost is the npm_virtual_upstream_rules foreign-key trigger at 1.2ms of the 1.6ms total, which is the rules' own cascade doing the work the method's doc describes. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

DELETE FROM public.npm_virtual_repository_upstreams
WHERE (npm_virtual_repository_upstreams.namespace_id = $1::uuid) AND (npm_virtual_repository_upstreams.id = $2::uuid);

Bound args: [526e027c-961d-727b-a016-7a2177fb1418, 057641b1-4b60-76bb-8394-c59f3327024d]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Delete on npm_virtual_repository_upstreams  (cost=0.28..8.30 rows=0 width=0) (actual time=0.099..0.099 rows=0 loops=1)
  Delete on npm_virtual_repository_upstreams_p10 npm_virtual_repository_upstreams_1
  Buffers: shared hit=10 read=1
  ->  Index Scan using npm_virtual_repository_upstreams_p10_pkey on npm_virtual_repository_upstreams_p10 npm_virtual_repository_upstreams_1  (cost=0.28..8.30 rows=1 width=10) (actual time=0.009..0.010 rows=1 loops=1)
        Index Cond: ((id = '057641b1-4b60-76bb-8394-c59f3327024d'::uuid) AND (namespace_id = '526e027c-961d-727b-a016-7a2177fb1418'::uuid))
        Buffers: shared hit=3
Planning Time: 0.094 ms
Trigger for constraint npm_virtual_upstream_rules_npm_virtual_repository_upstre_fkey10 on npm_virtual_repository_upstreams_p10: time=1.314 calls=1
Execution Time: 1.607 ms

Timings: planning 0.094ms, execution 1.607ms, total 1.701ms.

datastore.lockRepositoryForDeleteStmt

Summary: Plan matches the method's intent: a partition-pruned primary-key Index Scan under LockRows, the FOR UPDATE the association check reads beneath so its answer cannot go stale. One partition of 64, four buffer hits, and no soft_deleted_at term by contract. No anomalies.

Seed shape: namespaces=1, repositories=10000 (5000 hosted upstream candidates, 5000 virtual), npm_virtual_repositories=5000, npm_virtual_repository_upstreams=5000 (20 per virtual repository, positions 1-20)

Rendered SQL:

SELECT repositories.id AS "repositories.id"
FROM public.repositories
WHERE (repositories.namespace_id = $1::uuid) AND (repositories.id = $2::uuid)
LIMIT $3
FOR UPDATE;

Bound args: [3e73e26e-f208-71ba-98ca-842a71e8bab3, 00062f46-6078-79c0-8731-9d26df99ee6e, 1]

Plan (EXPLAIN (ANALYZE, BUFFERS) output):

Limit  (cost=0.29..8.31 rows=1 width=26) (actual time=0.013..0.014 rows=1 loops=1)
  Buffers: shared hit=4
  ->  LockRows  (cost=0.29..8.31 rows=1 width=26) (actual time=0.013..0.013 rows=1 loops=1)
        Buffers: shared hit=4
        ->  Index Scan using repositories_p26_pkey on repositories_p26 repositories  (cost=0.29..8.30 rows=1 width=26) (actual time=0.010..0.010 rows=1 loops=1)
              Index Cond: ((id = '00062f46-6078-79c0-8731-9d26df99ee6e'::uuid) AND (namespace_id = '3e73e26e-f208-71ba-98ca-842a71e8bab3'::uuid))
              Buffers: shared hit=3
Planning:
  Buffers: shared hit=2
Planning Time: 0.119 ms
Execution Time: 0.024 ms

Timings: planning 0.119ms, execution 0.024ms, total 0.143ms.

Related to #314 (closed)