コンシューマブルSaaS<Queue as a Service>

キューに追加するとcurlで1つずつ取得できるよ。最後まで読み込んだら空が帰るよ

See the Pen コンシューマブルSaaS by John Doe (@04) on CodePen.

レスポンスが空になるまでループ処理することで、分散処理が簡単にできるようになるよ

ソースコード

import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { Client } from "https://deno.land/x/postgres@v0.17.0/mod.ts";

const options = {
    headers: {
        "access-control-allow-origin": "*"
    }
}

const client = new Client({
  user: "postgres",
  database: "postgres",
  hostname: "YOUR_SUPABASE_HOST"
});
await client.connect();

const array = []

serve(async (req: Request) => {
  const url = new URL(req.url)
  if (url.pathname === '/') {
    if (req.method === 'POST') {
      const obj = await req.json()
      await client.queryArray('UPDATE item SET deleted_at = now() WHERE topic = $1', [obj.topic])
      const sql = 'INSERT INTO item (item_name, topic) VALUES ' + obj.lines.map((_, i) => `($${i*2+1},$${i*2+2})`).join(',')
      await client.queryArray(sql, obj.lines.flatMap(line => [line, obj.topic]))
      return new Response(array.join('\n'), options)
    } else {
      const topic = url.searchParams.get('topic')
      if (topic) {
        const { rows } = await client.queryArray('SELECT item_name FROM item WHERE topic = $1 AND deleted_at IS NULL', [topic])
        return new Response(rows.map(r => r[0]).join('\n'), options)
      }
      return new Response(array.join('\n'), options)
    }
  }
  const { rows } = await client.queryArray('select select_item($1)', [url.pathname.slice(1)])
  console.log(rows)
  return new Response(rows?.[0]?.[0], options)
});

Supabase(ポスグレ)のSQL

CREATE TABLE item (
  item_id SERIAL NOT NULL,
  item_name VARCHAR(255) NULL,
  topic VARCHAR(255) NULL,
  deleted_at TIMESTAMP NULL,
  created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (item_id));

CREATE OR REPLACE Function select_item(VARCHAR(255)) RETURNS VARCHAR
AS $$
DECLARE
  _item_id int;
  _item_name VARCHAR(255);
BEGIN
  SELECT item_id, item_name INTO _item_id, _item_name FROM item WHERE topic = $1 AND deleted_at IS NULL LIMIT 1;
  UPDATE item SET deleted_at = now() WHERE item_id = _item_id;
  RETURN _item_name;
END;
$$
LANGUAGE plpgsql;

プロシージャでやらないとSELECTとUPDATEの間で他のリクエストがSELECTすると同じ値がカエル🐸からプロシージャでしてる
FirebaseだとSELECTとUPDATEの間が長すぎて大量アクセスで同じ値を返すことがあった

以下でもいいかも

UPDATE item SET deleted_at = now() WHERE item_id = (SELECT item_id FROM item WHERE topic = $1 AND deleted_at IS NULL LIMIT 1) RETURNING item_name