Commit cache clearing

Following on from yesterdays Quarterly branches: what’s next? and Processing commits on branches with git, I’ve started in on clearing commits from cache.

The code

First step, create a table which looks like this:

freshports.devgit=# \d cache_clearing_commits
                     Table "public.cache_clearing_commits"
     Column      |            Type             | Collation | Nullable | Default 
-----------------+-----------------------------+-----------+----------+---------
 commit_to_clear | text                        |           | not null | 
 date_added      | timestamp without time zone |           |          | now()
Indexes:
    "cache_clearing_commits_idx" PRIMARY KEY, btree (commit_to_clear)

freshports.devgit=# 

Then, adjust some existing triggers to do something like this:

+   INSERT INTO cache_clearing_commits (commit_to_clear) VALUES (OLD.message_id)
+      ON CONFLICT ON CONSTRAINT cache_clearing_commits_idx DO NOTHING;
+
+   GET DIAGNOSTICS l_row_count = ROW_COUNT;
+   IF l_row_count > 0 THEN
+      NOTIFY commit_updated;
+   END IF;
+

At first, I started created new triggers etc, but then I looked at the existing triggers on the commit_log table:

Triggers:
    commit_log_delete_check BEFORE DELETE ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_delete_check()
    commit_log_insert AFTER INSERT ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_insert()
    commit_log_update AFTER UPDATE ON commit_log FOR EACH ROW EXECUTE FUNCTION commit_log_update()

I removed what I created and moved that code into the existing triggers.

The test

Let’s do a simple non-destructive test:

freshports.devgit=# begin;
BEGIN
freshports.devgit=# update commit_log set message_id = message_id where message_id = '524260db7683681c7deec9f1968c15a717317685';
UPDATE 1
freshports.devgit=# select * from cache_clearing_commits;
             commit_to_clear              |         date_added         
------------------------------------------+----------------------------
 524260db7683681c7deec9f1968c15a717317685 | 2021-06-21 22:46:35.674694
(1 row)

freshports.devgit=# 

It works.

Next step: modify the fp-listen daemon to listen for commit_updated.