PL/pgSQL و trigger: updated_at، audit log و LISTEN/NOTIFY

منطقی که باید هر بار و برای همه اجرا شود

trigger برای قاعده‌هایی است که نباید به حافظه‌ی برنامه‌نویس وابسته باشند: ثبت زمان آخرین تغییر، ثبت تاریخچه‌ی تغییرات، یکسان‌سازی «ی» و «ک». هر کسی از هر مسیری (جنگو، psql، اسکریپت ورود اکسل) داده را عوض کند، trigger اجرا می‌شود.

updated_at خودکار

CREATE OR REPLACE FUNCTION factory.touch_updated_at()
RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

ALTER TABLE factory.orders ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();

CREATE TRIGGER orders_touch
BEFORE UPDATE ON factory.orders
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION factory.touch_updated_at();

شرط WHEN باعث می‌شود UPDATEی که چیزی را عوض نکرده، زمان را هم عوض نکند.

audit log با jsonb

CREATE TABLE factory.audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text NOT NULL,
  op         text NOT NULL,
  row_pk     text,
  old_row    jsonb,
  new_row    jsonb,
  changed_by text NOT NULL DEFAULT current_user,
  app_user   text DEFAULT current_setting('app.user', true),
  changed_at timestamptz NOT NULL DEFAULT now()
);

CREATE OR REPLACE FUNCTION factory.audit_row()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER SET search_path = factory, pg_temp
AS $$
BEGIN
  INSERT INTO factory.audit_log (table_name, op, row_pk, old_row, new_row)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(to_jsonb(NEW) ->> 'id', to_jsonb(OLD) ->> 'id'),
    CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END
  );
  RETURN NULL;   -- trigger از نوع AFTER است؛ مقدار بازگشتی نادیده گرفته می‌شود
END;
$$;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON factory.orders
FOR EACH ROW EXECUTE FUNCTION factory.audit_row();

ستون app_user نام کاربر برنامه را از متغیری می‌خواند که برنامه با SET LOCAL app.user = 'zahra.karimi' تنظیم می‌کند؛ چون کاربر دیتابیس برای همه app_user است و به‌تنهایی نمی‌گوید چه کسی قیمت را عوض کرد.

LISTEN/NOTIFY: خبر دادن به برنامه

CREATE OR REPLACE FUNCTION factory.notify_order_status()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  PERFORM pg_notify('order_status',
    json_build_object('id', NEW.id, 'status', NEW.status)::text);
  RETURN NULL;
END;
$$;

CREATE TRIGGER orders_notify
AFTER UPDATE OF status ON factory.orders
FOR EACH ROW EXECUTE FUNCTION factory.notify_order_status();

-- در یک نشست دیگر:
LISTEN order_status;

پیام فقط پس از COMMIT تحویل می‌شود؛ اگر تراکنش rollback شود، هیچ پیامی نمی‌رود. سرویسی که داشبورد سالن بافندگی را زنده به‌روز می‌کند می‌تواند به‌جای پرسیدن هر ثانیه، فقط گوش بدهد.

نکته‌هایی که کمتر کسی می‌داند

• NOTIFY پیام تکراری با کانال و محتوای یکسان در یک تراکنش را یکی می‌کند و حداکثر حدود 8000 بایت محتوا می‌پذیرد؛ شناسه بفرستید، نه کل ردیف.
• LISTEN پشت PgBouncer در حالت transaction کار نمی‌کند؛ شنونده باید مستقیم به پستگرس (یا یک pool با حالت session) وصل شود.
• در تابع SECURITY DEFINER همیشه SET search_path بگذارید؛ وگرنه کاربری که schema خودش را جلوی search_path بگذارد، می‌تواند تابع یا جدولی هم‌نام بسازد و با مجوز مالک اجرا شود.
• trigger سطح دستور با «transition table» (REFERENCING NEW TABLE AS new_rows و FOR EACH STATEMENT) برای UPDATE صدهزار ردیفی یک بار اجرا می‌شود، نه صدهزار بار؛ audit گروهی را خیلی سریع‌تر می‌کند.
• از نسخه‌ی 14، CREATE OR REPLACE TRIGGER وجود دارد و migrationهای trigger دیگر به DROP و CREATE جدا نیاز ندارند.