feat(npm): carry npm_files.created_at to the virtual tarball winner
What this MR does
The hosted tarball lookup of an npm virtual repository now projects npm_files.created_at.
NpmVirtualHostedHit.CreatedAt carries the value, and the assembly copies it onto the hosted VirtualPosition.
The virtual tarball handler (part 3) sends this time as Last-Modified for a hosted winner, with no second query (ruling E1 of !3111 (merged)).
VirtualHostedUpstreams.lookupAnswers returns the existing Lookup answers plus the tarball times.
The assembly uses it when the hosted seam supports it, and falls back to Lookup when it does not.
Tests
TestNpmVirtualHostedTarballLookupStmt_ProjectsCreatedAtandTestNpmVirtualHostedTarballLookup_CarriesTheFilesCreatedAtcover the datastore half.- The npm half has no direct test in this part.
Part 3 covers it through the handler:
TestVirtualTarballHandler_ServesTheWinnersBytesandTestVirtualTarballHandler_ConditionalRequestsFollowTheWinnersKindassert the hostedLast-Modified.
Split of !3111 (merged)
This MR is one part of a split of !3111 (merged) into six stacked MRs. Each part targets the branch of the part before it, so each diff shows only that part. Merge the parts in order: 1, 2, 3, 4, 5, then !3111 (merged) itself as part 6.
| Part | MR | Contents |
|---|---|---|
| 1 | !3150 (merged) | npm_files.created_at to the hosted tarball winner |
| 2 | !3147 (merged) | Resolver re-entry on a virtual drain failure |
| 3 | !3148 (merged) | VirtualTarballHandler, not wired |
| 4 | !3149 (merged) | Copy-fault accounting, kill log, cold HEAD drain failure |
| 5 | !3151 (merged) | Usage events |
| 6 | !3111 (merged) | Slot wiring, run recipe, docs |
The six parts together are byte-identical to the original !3111 (merged) diff (same git patch-id --stable).
The spec coverage table, the rulings (E1 to E22), and the spec gaps stay in the description of !3111 (merged).
Database Review Evidence
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.
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.NpmVirtualHostedTarballLookup |
Unique | npm_packages_p34_namespace_id_npm_repository_id_name_idx, npm_versions_p34_namespace_id_npm_package_id_id_idx, npm_files_p34_namespace_id_npm_version_id_idx |
1 / 3 | 46.71 | 0.254ms | 482 / 0 | 1/64, 1/64, 1/64 |
Query notes:
datastore.NpmVirtualHostedTarballLookup: plan vs actual rows diverge 50x on thenpm_versionsleg (1 estimated, 50 actual per loop). The planner uses the average version count per package, and the seed gives the target package 50 versions.ANALYZEran.datastore.NpmVirtualHostedTarballLookup: thenpm_filesprobe uses(namespace_id, npm_version_id)and filtersfile_name, so it probes once per live version (150 probes for 3 hits). The work grows with the live version count of the package, multiplied by the position count.datastore.NpmVirtualHostedTarballLookup: the plan starts atnpm_packages. The merged Step 5 MR !1975 (merged) rejected this join order: driven fromnpm_packages, the read walks every live version of the package and probesnpm_filesonce for each. ADR-004 permits 25,000 versions per package.datastore.NpmVirtualHostedTarballLookup: no test pins the join order since commit23f228ed9("test(datastore): delete the query-plan assertions"), which removedTestNpmVirtualHostedTarballLookup_DrivesFromNpmFiles. The database review evidence is the only record of this plan.datastore.NpmVirtualHostedTarballLookup: this MR addsnpm_files.created_atto the select list. It makes no query change and no index change. The plan shape, the indexes, and the estimates are the same at the merge-base1ea37fc3.- The join order is tracked in the follow-up issue #1518.
datastore.NpmVirtualHostedTarballLookup
Summary: The plan returns the right rows: index scans on all three tables prune to 1 of 64 partitions each, and an Incremental Sort feeds DISTINCT ON. The plan starts at npm_packages, the order that !1975 (merged) rejected. The versions-leg estimate is 50x low, and the files probe filters file_name once per version (see Query notes).
Seed shape: namespaces=1, repositories=10, npm_repositories=10, blob_storage_blobs=1, blob_storage_attachments=1, npm_packages=5000, npm_versions=5490, npm_files=5490
Rendered SQL:
SELECT DISTINCT ON (npm_packages.npm_repository_id) npm_packages.npm_repository_id AS "npm_packages.npm_repository_id",
npm_files.blob_sha256 AS "npm_files.blob_sha256",
npm_files.created_at AS "npm_files.created_at"
FROM public.npm_packages
INNER JOIN public.npm_versions ON ((npm_versions.namespace_id = npm_packages.namespace_id) AND (npm_versions.npm_package_id = npm_packages.id))
INNER JOIN public.npm_files ON ((npm_files.namespace_id = npm_versions.namespace_id) AND (npm_files.npm_version_id = npm_versions.id))
WHERE ((((((((npm_packages.namespace_id = $1::uuid) AND (npm_packages.npm_repository_id IN ($2::uuid, $3::uuid, $4::uuid))) AND (npm_packages.name = $5::text)) AND (npm_packages.soft_deleted_at IS NULL)) AND (npm_versions.namespace_id = $6::uuid)) AND (npm_versions.soft_deleted_at IS NULL)) AND (npm_files.namespace_id = $7::uuid)) AND (npm_files.file_name = $8::text)) AND (npm_files.soft_deleted_at IS NULL)
ORDER BY npm_packages.npm_repository_id ASC, npm_files.created_at DESC, npm_files.id DESC;Bound args: [030333d2-2ab0-7cbf-9f9e-d828640fe8e9, 45c747e1-f808-73f0-9347-f02f8d9f1861, f120888d-5280-779f-a06c-8206853a076b, 4a31e261-21c4-759c-bcca-2a08deec867f, review-prep-pkg-0250, 030333d2-2ab0-7cbf-9f9e-d828640fe8e9, 030333d2-2ab0-7cbf-9f9e-d828640fe8e9, review-prep-pkg-0250-1.0.25.tgz]
Plan (EXPLAIN (ANALYZE, BUFFERS) output):
Unique (cost=23.74..46.71 rows=1 width=73) (actual time=0.252..0.254 rows=3 loops=1)
Buffers: shared hit=482
-> Incremental Sort (cost=23.74..46.71 rows=2 width=73) (actual time=0.252..0.253 rows=3 loops=1)
Sort Key: npm_packages.npm_repository_id, npm_files.created_at DESC, npm_files.id DESC
Presorted Key: npm_packages.npm_repository_id
Full-sort Groups: 1 Sort Method: quicksort Average Memory: 25kB Peak Memory: 25kB
Buffers: shared hit=482
-> Nested Loop (cost=0.85..46.62 rows=1 width=73) (actual time=0.094..0.240 rows=3 loops=1)
Buffers: shared hit=482
-> Nested Loop (cost=0.56..45.35 rows=3 width=48) (actual time=0.036..0.069 rows=150 loops=1)
Buffers: shared hit=32
-> Index Scan using npm_packages_p34_namespace_id_npm_repository_id_name_idx on npm_packages_p34 npm_packages (cost=0.28..20.41 rows=3 width=48) (actual time=0.022..0.027 rows=3 loops=1)
Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_repository_id = ANY ('{45c747e1-f808-73f0-9347-f02f8d9f1861,f120888d-5280-779f-a06c-8206853a076b,4a31e261-21c4-759c-bcca-2a08deec867f}'::uuid[])) AND (name = 'review-prep-pkg-0250'::text))
Buffers: shared hit=7
-> Index Scan using npm_versions_p34_namespace_id_npm_package_id_id_idx on npm_versions_p34 npm_versions (cost=0.28..8.30 rows=1 width=48) (actual time=0.006..0.011 rows=50 loops=3)
Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_package_id = npm_packages.id))
Filter: (soft_deleted_at IS NULL)
Buffers: shared hit=25
-> Index Scan using npm_files_p34_namespace_id_npm_version_id_idx on npm_files_p34 npm_files (cost=0.28..0.41 rows=1 width=89) (actual time=0.001..0.001 rows=0 loops=150)
Index Cond: ((namespace_id = '030333d2-2ab0-7cbf-9f9e-d828640fe8e9'::uuid) AND (npm_version_id = npm_versions.id))
Filter: ((soft_deleted_at IS NULL) AND (file_name = 'review-prep-pkg-0250-1.0.25.tgz'::text))
Rows Removed by Filter: 1
Buffers: shared hit=450
Planning:
Buffers: shared hit=105
Planning Time: 0.860 ms
Execution Time: 0.294 msTimings: planning 0.860ms, execution 0.294ms, total 1.154ms.
E2E scenario catalog and user documentation
This part adds no e2e scenario and changes no file in docs/user/.
Nothing wires the virtual tarball handler before part 6, so the route still answers 501 and no client-visible behavior changes.
Related to #898 (closed)