Loading...

2023-11-27(月) 15:00

🗓️ Supabaseでpg_cronを使ってスケジュール実行する

Supabasepg_cron
Supabaseの拡張機能にあるpg_cronを使用して、Database Functionを指定したスケジュールや定期実行する方法を解説します。

目次

前提と注意事項

  • Supabase アカウントを作成済みであること
  • Supabase のプロジェクトを作成済みであること

この記事では以下の公式ドキュメントを参考にします。

pg_cron: Job Scheduling

The pg_cron extension is a simple cron-based job scheduler for PostgreSQL that runs inside the database.

supabase.com

この記事のゴール

任意の Supabase Database Function を決まった日時に定期実行できるようにすることがこの記事のゴールです。

pg_cron を有効化する

まずはじめに、Supabase でpc_cronを使えるようにするために有効化する必要があります。 以下のように Supabase の Web 管理画面のサイドメニューにある「Database」ページの「Extensions」ページで、pg_cronと検索します。そして以下のように表示されたpg_cronのスイッチを ON にします。

Supabaseのpg_cron拡張機能

ON にすると以下のように確認ダイアログが開くので、Enable extensionボタンをクリックします。
Select a schema to enable the extension forについては、extensionを選択します。

Supabaseのpg_cronを有効化する

以下のように ON になりました。これでスケジュールを定期的に実行するためのpg_cronを Supabase 内で使用できるようになります。

pg_cronの有効化完了

以上でpg_cronの有効化が完了です。

スケジュールを登録する Database Function

テスト用に以下のようなhello worldと表示するだけの Database Function をhello_world()という名前で Supabase の Web 管理画面にあるSQL Editorで作成しておきます。

hello_world Database Function
create or replace function hello_world()
returns text
language sql
as $$
  select 'hello world';
$$;

そして同様にSQL Editorで以下のような Database Function 作成し、Runをクリックして実行します。

Database Functionを実行する

上記では、以下のようにcron.scheduleを使用して、hello_worldという Database Function を毎日 13:30 に実行するように登録しています。

スケジュール登録するためのDatabase Function
select
  cron.schedule(
    'invoke-hello-world', -- スケジュール名
    '30 4 * * *', -- 毎日13:30に実行する
    $$
    select hello_world(); -- 実行する内容
    $$
  );

実行すると画像にあるようにcron.scheduleの実行結果として登録したスケジュールの ID(上記画像内だと8)が表示されます。
これでスケジュール登録が完了し、毎日 13:30 にhello_worldという Database Function が実行されるようになります。

なお、もし以下のようにschema "cron" does not existというエラーが表示される場合は、pg_cronが有効になっていないために発生します。再度pg_cronが有効になっていると確認すると良いかもしれません。

pg_cronが有効になっていない場合のエラー

作成済みのスケジュール一覧を確認する

以下の Database Function を実行することで、作成済みのスケジュールジョブ一覧を確認できます。

作成済みのスケジュール一覧を確認する
select * from cron.job;

以下が Supabase の Web 管理画面で上記のクエリを実行した例です。

作成済みのスケジュール一覧

なお、以下のように Supabase の Web 管理画面にある Table Editor でも同様にスケジュールジョブを確認できます。pg_cronで作成したスケジュールジョブはjob、実行したスケジュールジョブの履歴はjob_run_detailsで確認できます。なお、いずれもcronスキーマの中にあります。

作成済みのスケジュール一覧

作成済みのスケジュールを更新する

記事公開時点では、スケジュール名が同じcron.scheduleを再度実行すれば新しい内容で更新されます。

スケジュールを更新する
select
  cron.schedule(
    'invoke-hello-world',
    '0 10 * * *', -- 毎日19時に実行する
    $$
    select hello_world();
    $$
  );

なお、スケジュールの更新にはalter_jobを使用することもできます。 例えば、以下はalter_jobを使用してスケジュールのみを更新しています。

alter_jobを使ったスケジュールジョブの更新
select cron.alter_job(
  job_id := (select jobid from cron.job where jobname = 'invoke-hello-world'),
  '0 10 * * *', -- 毎日19時に実行する
);

上記では,job_idとしてselect jobid from cron.job where jobname = 'invoke-hello-world'の値を使っていますが、これは単純にinvoke-hello-worldという名前のスケジュールの ID を取得しています。 もし目的のjob_idがわかっている場合は、そのままその値を渡して OK です。 そしてスケジュールジョブの更新としてはalter_jobが本来の方法で、alter_jobの方がより細かい設定を更新することができます。 例えば、alter_jobでは以下の内容を編集することができます。

alter_jobで編集できる内容
cron.alter_job(
  job_id bigint,
  schedule text default null,
  command text default null,
  database text default null,
  username text default null,
  active boolean default null
)

詳しくは以下の公式ドキュメントに記載されています。

Edit job

Edit job

supabase.com

スケジュールを一時停止/再開する

alter_jobを使用することで、作成済みのスケジュールジョブを一時停止することできます。
以下のようにactiveにfalseを指定することで、スケジュールジョブを一時停止することができます。

alter_jobを使ったスケジュールジョブの一時停止
select cron.alter_job(
  job_id := (select jobid from cron.job where jobname = 'invoke-hello-world'),
  active := false
);

再度スケジュールジョブを再開する場合は、以下のようにactiveにtrueを指定することで、スケジュールジョブを再開することができます。

alter_jobを使ったスケジュールジョブの再開
select cron.alter_job(
  job_id := (select jobid from cron.job where jobname = 'invoke-hello-world'),
  active := true
);

スケジュールを削除する

以下のクエリを実行することで登録したスケジュールを削除できます。

スケジュールジョブの削除
select cron.unschedule('invoke-hello-world');

上記を実行後、再度select * from cron.job;を実行してみると、削除されていることを確認できると思います。

まとめ

この記事では、hello worldを表示するだけの Database Function をスケジュール実行したため、実用性はありませんが、hello_world()の代わりに他の好きな Databse Function を実行できます。
そのため Supabase の中で好きな処理をスケジュール実行ができ、例えば毎日0時に特定のテーブルのデータを一掃したり、デイリーレポートやメールマガジンのような内容を毎日決まった時間にメールを送信したりなど色々なことができるようになります。