List volumes endpoint errors if any volume disappears while presenting

[vito]

We currently make 3 DB calls per volume, making this endpoint incredibly slow and error prone:

https://github.com/concourse/atc/blob/master/api/present/volume.go#L9-L23

[jtarchie]

We are going to make this a single query, not an N+1.

[jtarchie]

We saw that when any of volumes errored (ie: something got deleted) the entire endpoint failed. We've fixed it up so that it only returns volumes that haven't errored, logs the errored volumes, and get's you ice cream (not really).

We also took a crack, eh, at unifying the query for the endpoint, so that we wouldn't run into this issue. We have a single query that can return the necessary information. The inclusion of the query would change quite a bit of modeling outside this endpoint -- mainly db/volume.go.

If we want to move on with a remodeling, let's discuss and schedule some work.

And my pair will copy+pasta a nice SQL statement.

[jwntrs]

WITH RECURSIVE
worker_base_resource_type_version AS (
	SELECT wbrt.id wbrt_id, brt.id brt_id, brt.name, wbrt.worker_name, wbrt.version
	FROM worker_base_resource_types wbrt
	LEFT JOIN base_resource_types brt ON brt.id = wbrt.base_resource_type_id
), 
base_resource_type_configs AS (
		SELECT rc.id, rcfg.base_resource_type_id, rcfg.resource_cache_id
		FROM resource_caches rc, resource_configs rcfg 
		WHERE rcfg.id = rc.resource_config_id
),
base_resource_configs(id, base_resource_type_id, resource_cache_id) AS (
	SELECT * FROM base_resource_type_configs brtc 
		WHERE brtc.base_resource_type_id IS NOT NULL
	UNION ALL
	SELECT c.id, p.base_resource_type_id, c.resource_cache_id
		FROM base_resource_configs AS p, base_resource_type_configs c
		WHERE p.id = c.resource_cache_id
)
SELECT
	v.id,
	v.handle,
	v.state,
	w.name,
	v.path,
	c.handle,
	pv.handle,
	v.team_id,
	wrc.resource_cache_id,
	v.worker_base_resource_type_id,
	v.worker_task_cache_id,
	rc.version,
	wbrtv_resource.name resource_type_base_resource_type_name,
	wbrtv_resource.version resource_type_base_resource_type_version,
	wbrtv_base_resource.name base_resource_type_name,
	wbrtv_base_resource.version base_resource_type_version,
	p.name,
	j.name,
	wtc.step_name,
	CASE
		WHEN v.worker_base_resource_type_id IS NOT NULL THEN 'resource-type'
		WHEN v.worker_resource_cache_id IS NOT NULL THEN 'resource'
		WHEN v.container_id IS NOT NULL THEN 'container'
		WHEN v.worker_task_cache_id IS NOT NULL THEN 'task-cache'
		ELSE 'unknown'
	END
FROM volumes v
LEFT JOIN workers w ON v.worker_name = w.name
LEFT JOIN containers c ON v.container_id = c.id
LEFT JOIN volumes pv ON v.parent_id = pv.id
LEFT JOIN worker_resource_caches wrc ON wrc.id = v.worker_resource_cache_id
LEFT JOIN resource_caches rc ON rc.id = wrc.resource_cache_id
LEFT JOIN base_resource_configs brc ON brc.id = rc.id
LEFT JOIN worker_base_resource_type_version wbrtv_resource ON wbrtv_resource.brt_id = brc.base_resource_type_id AND wbrtv_resource.worker_name = w.name
LEFT JOIN worker_base_resource_type_version wbrtv_base_resource ON wbrtv_base_resource.wbrt_id = v.worker_base_resource_type_id
LEFT JOIN worker_task_caches wtc ON wtc.id = v.worker_task_cache_id
LEFT JOIN jobs j ON j.id = wtc.job_id
LEFT JOIN pipelines p ON p.id = j.pipeline_id
WHERE (v.team_id = $1 OR v.team_id IS NULL);

[vito]

I've split out #1947 for the optimization aspect, so I'll just do acceptance by seeing that it doesn't blow up anymore. The code change looks good.