PL/pgSQL と pg_net / http – ソフトウェア技術者(ときどき科学)のつぶやき

PostgreSQL のストアドファンクション/ストアドプロシージャとして PL/pgSQL がある。

一方、postgreSQL のストアドファンクション/プロシージャから HTTP アクセスが利用できる拡張機能として、pg_net と http がある。Supabase ではどちらも標準で用意されている。

pg_net 拡張は非同期処理を行うので、処理効率が高いことが期待できる一方、動作が直線的でないので、プログラミングに若干苦労する。http 拡張は同期処理を行うので、直感的なプログラミングが可能だが、API 呼び出し待機時間が発生するため、処理効率は落ちてしまう。

…という感じだが、実はもう１つの軸があり、TRIGGER として仕掛ける場合、pg_net はうまくいかないということが分かった。

というのも、TRIGGER ではトランザクションの内側で実行されるという記述があり、pg_net で記述された処理はトランザクションが実行されるときに実行されることになるためである。逆に言うと、いくらpg_net の処理をトランザクション内で待っても実行されないため、net._http_response は常に NULL を返してしまう。

従って、トランザクション処理の中でネット処理を行いたい場合は同期処理型の http を使う方が良い。

実際にプログラムを書き下すとこんな感じになる。

CREATE EXTENSION http WITH SCHEMA 'public';

CREATE OR REPLACE FUNCTION public.http_post_(
  host TEXT,
  uri TEXT,
  auth TEXT,
  content TEXT,
  content_type TEXT
) RETURNS public.http_response AS $$
DECLARE
  url TEXT := "https://" || host || uri;
  headers public.http_header[]; 
  req public.http_request;
BEGIN
  headers.arraypush(public.http_header('Authorization', 'Bearer ' || auth));
  headers.arraypush(public.http_header('Host', host));

  req := (
    'POST',
    url,
    headers,
    content_type,
    content
  )::public.http_request;

  RETURN public.http(req);
END;
$$ LANGUAGE=plpgsql;

CREATE OR REPLACE FUNCTION public.do_post_json(
  host TEXT,
  uri TEXT,
  content jsonb
) RETURNS jsonb AS $$
DECLARE
  cron_secret TEXT := '<CRON_SECRET>';
  resp public.http_response;
BEGIN
  SELECT content::json INTO resp
    FROM public.http_post_(host, uri, cron_secret, content::TEXT, 'application/json');
  RETURN resp;
END;
$$ LANGUAGE=plpgsql;

CREATE OR REPLACE FUNCTION public.do_post_form(
  host TEXT,
  uri TEXT,
  content TEXT
) RETURNS jsonb AS $$
DECLARE
  cron_secret TEXT := '<CRON_SECRET>';
  resp public.http_response;
BEGIN
  SELECT content::json INTO resp
    FROM public.http_post_(host, uri, cron_secret, content, 'application/x-www-form-urlencoded');
  RETURN resp;
END;
$$ LANGUAGE=plpgsql;

CREATE OR REPLACE FUNCTION public.mytable_trigger(
) RETURNS 'trigger' AS $$
DECLARE
  host TEXT := '<連携ホスト>';
  uri TEXT := '<連携 URI>';
  content jsonb;
  resp jsonb;
  is_ok BOOLEAN := false;
BEGIN
  IF NOT OLD.email IS NULL THEN
    content := jsonb_build_object('ID', OLD."ID", 'email', OLD."email");
    resp := public.do_post_json(host, uri, content);
    IF resp IS NULL THEN
        RAISE WARNING '結果が NULL';
    ELSE
      IF resp->>'error' IS NOT NULL THEN
        RAISE WARNING 'トリガエラー: %', resp->>'error';
      END IF;
    END IF;
  END IF;
  RETURN OLD;
END;
$$ LANGUAGE=plpgsql;

DROP TRIGGER IF EXISTS delete_mytable_record ON public.mytable;
CREATE TRIGGER delete_mytable_record
    AFTER DELETE ON public.mytable
    FOR EACH ROW
EXECUTE FUNCTION public.mytable_trigger();

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル