Create jobs in the database using triggers

[timgit]

Create jobs in the database using triggers

[bradleyayers]

I've gone this route (queuing jobs via a trigger) and have had a great success with it. I use singletonkey and startin (where startin is the throttle window). I'm using it to update my search index when records change that would affect the index. It's turned out to be extremely easy to setup!

In case it's helpful to others, here's what I'm using:

create or replace function dovetail_private.queuenoteforsearchindexing(note_id uuid) returns void as $$
  insert into dovetail_pgboss.job (id, name, startin, singletonkey, data)
  values
    (
      uuid_generate_v1(),
      'SEARCH_INDEXING_INDEX_NOTE',
      '10 seconds'::interval,
      -- Using the note ID as the singletonkey serves to debounce based on note.
      $1::text,
      json_build_object('noteId', $1::text)
    )
  on conflict do nothing
$$ language sql strict security definer;

create function dovetail_private.note$queuesearchindexing() returns trigger as $$
begin
  perform dovetail_private.queuenoteforsearchindexing(new.id);
  -- result is ignored since this is an AFTER trigger
  return null;
end;
$$ language plpgsql strict;

create trigger afterupdate050$queuesearchindexing after update
  on dovetail.note
  for each row
  when (
    new.title is distinct from old.title
    or new.deleted is distinct from old.deleted
    or new.type is distinct from old.type
    or new.content_version is distinct from old.content_version
    or new.ranges::text is distinct from old.ranges::text
  )
  execute procedure dovetail_private.note$queuesearchindexing();

[timgit]

👍 I have defaults defined for most fields, so unless you need to change them, they can be excluded from COPY or INSERT.