085.sql
View Options

	-- SWH DB schema upgrade
	-- from_version: 84
	-- to_version: 85
	-- description: content cache: insert lines in deterministic order to reduce lock contention

	insert into dbversion(version, release, description)
	values(85, now(), 'Work In Progress');

	CREATE OR REPLACE FUNCTION swh_cache_content_revision_add() RETURNS void
	LANGUAGE plpgsql
	AS $$
	declare
	cnt bigint;
	d sha1_git;
	begin
	delete from tmp_bytea t where exists (select 1 from cache_content_revision_processed ccrp where t.id = ccrp.revision);

	select count(*) from tmp_bytea into cnt;
	if cnt <> 0 then
	create temporary table tmp_ccr (
	content sha1_git,
	directory sha1_git,
	path unix_path
	) on commit drop;

	create temporary table tmp_ccrd (
	directory sha1_git,
	revision sha1_git
	) on commit drop;

	insert into tmp_ccrd
	select directory, id as revision
	from tmp_bytea
	inner join revision using(id);

	insert into cache_content_revision_processed
	select distinct id from tmp_bytea order by id;

	for d in
	select distinct directory from tmp_ccrd
	loop
	insert into tmp_ccr
	select sha1_git as content, d as directory, name as path
	from swh_directory_walk(d)
	where type='file';
	end loop;

	with revision_contents as (
	select content, false as blacklisted, array_agg(ARRAY[revision::bytea, path::bytea]) as revision_paths
	from tmp_ccr
	inner join tmp_ccrd using (directory)
	group by content
	order by content
	), updated_cache_entries as (
	update cache_content_revision ccr
	set revision_paths = ccr.revision_paths \|\| rc.revision_paths
	from revision_contents rc
	where ccr.content = rc.content and ccr.blacklisted = false
	returning ccr.content
	) insert into cache_content_revision
	select * from revision_contents rc
	where not exists (select 1 from updated_cache_entries uce where uce.content = rc.content)
	order by rc.content
	on conflict (content) do update
	set revision_paths = cache_content_revision.revision_paths \|\| EXCLUDED.revision_paths
	where cache_content_revision.blacklisted = false;
	return;
	else
	return;
	end if;
	end
	$$;

	COMMENT ON FUNCTION swh_cache_content_revision_add() IS 'Cache the revisions from tmp_bytea into cache_content_revision';

File Metadata

Mime Type: text/plain
Expires: Thu, Apr 17, 7:22 AM (4 d, 20 h ago)
Storage Engine: blob
Storage Format: Raw Data
Storage Handle: 3240598

085.sql
No OneTemporary
Actions

085.sql
View Options

File Metadata

Event Timeline

085.sqlNo OneTemporaryActions

085.sqlView Options

File Metadata

Event Timeline

085.sql
No OneTemporary
Actions

085.sql
View Options