Hi Michal
We do not currrently clean up deleted branches - depending on repo type they are hard to detect.
Before you do anything, backup the database
Resetting a respository with “Delete history of changesets which are not linked to an existing build” is the safest option.
This option deletes every changeset in that repository that isn’t linked to a build, on every branch, not just deleted ones. It removes their files, tags and issues too, then the branch rows left with no changesets. The repository then re-initialises from the source repository, so history on live branches is re-imported. Commits from a deleted branch come back only if a remaining branch or tag can still reach them.
That should cleanup a lot - but given the size ofd your db/repo, you might want to do this when people are not running builds as the reset may take some time.
A less safe option - this script (assuming you are using postgresql) - use at your own risk!
Note : the Continua CI Server service must be stopped when running this (dry run is ok).
Backup DB before you do anything.
There are some things to be aware of :
- More than deleted branches can match.
isdeletedorclosed is also set on closed Mercurial branches, and on any branch the poller stops listing as open
- Deleted changesets probably won’t come back. If a branch is pushed again later, its old commits are likely already in the repository cache, so they won’t be re-imported. Only a reset with “delete changesets” ticked rebuilds that history.
- Merged history can disappear from the list. If a feature branch was merged and then deleted, and Continua recorded its commits against the feature branch, those commits drop out of the repository’s changeset list as well. Builds that included them keep them, because of the
builds_buildchangeset check.
- The database file won’t shrink on its own. plain
VACUUM makes the space reusable, and VACUUM FULL builds_changesetfile returns it to the OS but locks the table while it runs (so run with continua service stopped).
-- Needs PostgreSQL 11 or later. Counts are reported as notices (the Messages tab in pgAdmin).
-- A dry run only reads, so it is safe while the Continua service is running.
-- With dryRun := false: stop the service, back up first, and run with autocommit on
-- (not inside BEGIN), because the block commits after each batch.
DO $$
DECLARE
dryRun boolean := true; -- set to false to actually delete
minAgeDays integer := 30; -- skip branches with a commit in the last N days
batchSize integer := 1000; -- changesets per transaction
changesetCount bigint;
fileCount bigint;
branchCount bigint;
batchIds uuid[];
BEGIN
DROP TABLE IF EXISTS pg_temp.cleanupTargets;
CREATE TEMP TABLE cleanupTargets AS
SELECT c.id
FROM builds_changeset c
INNER JOIN builds_repositorybranch rb ON rb.id = c.branchid
WHERE rb.isdeletedorclosed = true
AND rb.lastcommitdate < (now() AT TIME ZONE 'utc') - make_interval(days => minAgeDays)
AND NOT EXISTS (SELECT 1 FROM builds_buildchangeset bc WHERE bc.changesetid = c.id)
AND NOT EXISTS (SELECT 1 FROM builds_changesettag t WHERE t.changesetid = c.id);
ALTER TABLE cleanupTargets ADD PRIMARY KEY (id);
SELECT COUNT(*) INTO changesetCount FROM cleanupTargets;
SELECT COUNT(*) INTO fileCount FROM builds_changesetfile f INNER JOIN cleanupTargets t ON t.id = f.changesetid;
-- branch rows that will have no changesets left once the targets are gone
SELECT COUNT(*) INTO branchCount
FROM builds_repositorybranch rb
WHERE rb.isdeletedorclosed = true
AND NOT EXISTS (SELECT 1 FROM builds_changeset c WHERE c.branchid = rb.id AND NOT EXISTS (SELECT 1 FROM cleanupTargets t WHERE t.id = c.id))
AND NOT EXISTS (SELECT 1 FROM builds_configurationdefaultrepobranch d WHERE d.repositorybranchid = rb.id)
AND NOT EXISTS (SELECT 1 FROM builds_configrepobranchlinkage l WHERE l.triggeringrepositorybranchid = rb.id OR l.otherrepositorybranchid = rb.id);
RAISE NOTICE 'Changesets: %, changeset files: %, branch rows: %', changesetCount, fileCount, branchCount;
IF dryRun THEN
RAISE NOTICE 'Dry run - nothing deleted';
RETURN;
END IF;
LOOP
SELECT array_agg(id) INTO batchIds FROM (SELECT id FROM cleanupTargets LIMIT batchSize) s;
EXIT WHEN batchIds IS NULL;
DELETE FROM builds_changesetfile WHERE changesetid = ANY(batchIds);
DELETE FROM builds_changesetissue WHERE changesetid = ANY(batchIds);
DELETE FROM builds_changeset WHERE id = ANY(batchIds);
DELETE FROM cleanupTargets WHERE id = ANY(batchIds);
COMMIT;
END LOOP;
DELETE FROM builds_repositorybranch rb
WHERE rb.isdeletedorclosed = true
AND NOT EXISTS (SELECT 1 FROM builds_changeset c WHERE c.branchid = rb.id)
AND NOT EXISTS (SELECT 1 FROM builds_configurationdefaultrepobranch d WHERE d.repositorybranchid = rb.id)
AND NOT EXISTS (SELECT 1 FROM builds_configrepobranchlinkage l WHERE l.triggeringrepositorybranchid = rb.id OR l.otherrepositorybranchid = rb.id);
GET DIAGNOSTICS branchCount = ROW_COUNT;
RAISE NOTICE 'Deleted % changesets, % changeset files and % branch rows', changesetCount, fileCount, branchCount;
END $$;
I ran this against our production db (yes I backed up first!) - I recommend you do the dry run first.
NOTICE: Changesets: 75418, changeset files: 1692600, branch rows: 2726
NOTICE: Deleted 75418 changesets, 1692600 changeset files and 2726 branch rows
DO
Query returned successfully in 43 secs 371 msec.
