主页 / dba / 问题 / 212135
Accepted
Evgeny Nozdrev
Asked: 2018-07-14 04:17:32 +0800 CST

如何从 PostgreSQL 中的触发器访问其他插入/更新的行？

  • 772

MS SQL 中的解决方案

MS SQL 触发器函数具有存储受操作影响的所有行的系统表deleted。inserted您可以计算更新的行数：

set @updatedCount = (select count(*) from deleted)

或找出最小值：

set @updatedMinimumCol1 = (select min(col1) from deleted)

PostgreSQL 的问题

对于FOR EACH ROW触发器，我可以使用 OLD 和 NEW 系统记录，但每次调用触发器时它们只存储 1 行。触发器的调用是分开的，所以如果用户更新 10 行，触发器将被调用 10 次，但每次我只能知道 1 个当前行，而不是全部 10 行。

因为FOR EACH STATEMENT我根本不知道任何访问更新行的机制。我使用 PostgreSQL v9.6，OLD TABLE并NEW TABLE在 v10 中引入。

PostgreSQL 不允许在语句级触发器中引用旧表和新表，即包含所有旧和/或新行的表，这些表由 SQL 标准中的 OLD TABLE 和 NEW TABLE 子句引用。

尝试使用 transaction_timestamp() 附加列

我可以在主表中添加特殊列，DEFAULT transaction_timestamp()然后使用它来区分刚刚更新的行与其他行，但这不是解决方案，因为多个INSERTs/UPDATEs可以在一个事务中并且它们将具有相同的事务时间戳。可能我可以在每个语句之后清除触发器中的这个时间戳列以避免这个问题，但是如果这样的清除将再次发出更新触发器，如何做到这一点 - 将是无限更新触发器调用。

所以，这次尝试失败了。

PostgreSQL 中的错误解决方案

我知道的唯一方法是：

首先，使用FOR EACH ROW触发器来收集当前统计信息（最小值和计数），例如聚合函数。我使用临时表在调用之间存储它（此触发器每行调用 1 次）。但是我们不会知道哪一行是最后一行（何时使用此统计信息）。

CREATE TEMP TABLE IF NOT EXISTS _stats (
  _current_min   int,
  _current_count int
) ON COMMIT DROP;

IF EXISTS(SELECT 1 FROM _stats LIMIT 1) THEN
  --Current row is not first, there is statistics for previous rows.
  UPDATE _stats
  SET _current_min   = (CASE WHEN NEW.col1 < _current_min THEN NEW.col1
                        ELSE _current_min END)
    , _current_count = _current_count + 1;
ELSE
  --There is no stats because current row is first for this INSERT/UPDATE
  INSERT INTO _stats (_current_min, _current_count)
  VALUES (NEW.col1, 1);
END IF;

其次，使用FOR EACH STATEMENT触发器来使用收集的统计信息。不要忘记清除临时表（如果用户将在一个事务中运行多个插入/更新，旧的统计信息将保留在临时表中并破坏所有接下来的计算！）。

对于更复杂的任务，我们可以创建临时表inserted，deleted方法与_stats.

解决方法

在 PostgreSQL 中，我们可以对 INSERT/UPDATE/DELETE 使用 RETURNING 子句来获取受操作影响的所有行的新值。然后我们可以使用它们进行操作，但是每个带有 INSERT/UPDATE 的函数都必须实现这项技术 ===> 1. 带有这种 INSERT/UPDATE 的函数中的附加代码 - RETURNING 的重复；2.我们可以忘记将此类技术应用于新功能；3. 数据将被破坏，因为不会自动调用所需的操作（就像触发器一样）。

问题

也许，您知道访问受 INSERT/UPDATE 影响的所有行的更好方法？

postgresql trigger

2 个回答

  1. Best Answer

    请参阅文档，您应该能够从语句触发器访问旧记录和新记录：

    CREATE TRIGGER some_table_update_trigger
  AFTER UPDATE ON some_table
  REFERENCING NEW TABLE AS newtab OLD TABLE AS oldtab
  FOR EACH STATEMENT
  EXECUTE PROCEDURE do_something_with_newtab_and_oldtab();
    • 7

  2. 对于 PostgreSQL 10+，请参阅@ewramner 的回答。

    对于较低版本，我找到了 2 个解决方案。仅当您希望在触发器中使用inserted和deleted表时，两者都有效。AFTER

    解决方案 1. 临时表 _inserted 和 _deleted。

    首先，在BEFORE FOR EACH ROW触发器中创建临时表并填充它们：

    CREATE TRIGGER trigger_fill_sys_tables
  BEFORE INSERT OR UPDATE OR DELETE
  ON public.ttest2
  FOR EACH ROW
EXECUTE PROCEDURE public.tr_fill_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_fill_sys_tables()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
  EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _deleted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
  IF tg_op <> 'INSERT' THEN
    INSERT INTO _deleted
      SELECT old.*;
  END IF;

  EXECUTE 'CREATE TEMP TABLE IF NOT EXISTS _inserted (LIKE ' || tg_table_schema || '.' || tg_relname || ');';
  IF tg_op <> 'DELETE' THEN
    INSERT INTO _inserted
      SELECT new.*;
  END IF;

  IF tg_op <> 'DELETE' THEN
    RETURN new;
  ELSE
    RETURN old;
  END IF;
END;
$$;

    每次调用触发器时，通过new和系统记录我们都可以访问当前记录。old但是当它被调用为第一行时，我们不知道第二行。我们不知道是否存在更多行。这就是为什么

    第二。在AFTER EACH STATEMENT触发器中已经收集了所有行。您可以使用此表：

    CREATE TRIGGER trigger_use_sys_tables
  AFTER INSERT OR UPDATE OR DELETE
  ON ttest2
  FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_use_sys_tables();


CREATE OR REPLACE FUNCTION public.tr_use_sys_tables()
  RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
  _row record;
BEGIN
  --If 0 rows was affected by statement, tr_fill_sys_tables() will NOT be called, _inserted will NOT be created. To avoid a crash, check it:
  IF NOT EXISTS(SELECT 1
                FROM pg_class
                WHERE relname = '_inserted') THEN
    RETURN NULL;
  END IF;

  --Work with sys tables.
  --Note: changing data in them will not affect to main table!
  --Note: changing data in main table can fire this trigger again and fall into infinity loop. CREATE TEMP TABLE _lock() before UPDATE and DROP it after one to check if trigger was called recursively.
  FOR _row IN
  SELECT
      COALESCE(n.id, o.id) AS id
    , o.data               AS old_data
    , n.data               AS new_data
  FROM _inserted n
    FULL OUTER JOIN _deleted o ON n.id = o.id
  LOOP
    RAISE NOTICE 'id = %, old data = %, new data = %', _row.id, _row.old_data, _row.new_data;
  END LOOP;

  --DO NOT FORGET to drop the tables!
  --Just clear is not a solution, since next INSERT/UPDATE/DELETE can work with another table with different structure
  DROP TABLE _deleted;
  DROP TABLE _inserted;

  RETURN NULL;
END;
$$;

    解决方案 2. 表中的附加列。

    如果您想在触发器中再次更新插入/更新的字符串，那很好。不适用于DELETE触发器，请参阅解决方案 1。

    首先，将列添加trans_timest timestamp到主表。

    其次，transaction_timestamp()通过BEFORE FOR EACH ROW触发器写入：

    CREATE TRIGGER trigger_trans_mark
  BEFORE INSERT OR UPDATE
  ON public.ttest
  FOR EACH ROW
EXECUTE PROCEDURE public.tr_ttest_trans_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_trans_mark()
  RETURNS trigger AS $$
BEGIN
  IF tg_op = 'INSERT' THEN --to not crash when checking "old" record
    new.trans_timest = transaction_timestamp();
  ELSE
    IF old.trans_timest IS NULL THEN --if we are clearing marks, do not set them again
      new.trans_timest = transaction_timestamp();
    END IF;
  END IF;
  RETURN new;
END;
$$
LANGUAGE 'plpgsql';

    第三，您AFTER FOR EACH STATEMENT可以使用此标记将受此影响的行INSERT/UPDATE与其他行区分开来。不要忘记清除此触发器中的标记（如果用户在一个事务中执行多个 INSERT/UPDATE，则它们都将具有相同的 trans_timest 并且将被混合）。但是只有当它们还没有被清除时，你才能清除这些标记（如果你在 UPDATE 触发器中调用 UPDATE，它会调用自己 - 如果没有这个检查，你将陷入无限循环）：

    CREATE TRIGGER trigger_use_mark
  AFTER INSERT OR UPDATE
  ON public.ttest
  FOR EACH STATEMENT
EXECUTE PROCEDURE public.tr_ttest_use_mark();

CREATE OR REPLACE FUNCTION public.tr_ttest_use_mark()
  RETURNS trigger AS $$
BEGIN
  IF NOT EXISTS(SELECT 1
            FROM public.ttest t
            WHERE t.trans_timest = transaction_timestamp()
            LIMIT 1) THEN --To avoid infinity loop
    RETURN NULL;
  END IF;

    --Work with marked rows.
    ...

  --DO NOT FORGET to clear marks!
  UPDATE public.ttest
  SET trans_timest = NULL --update this rows again only simultaniously with clearing of marks!
  WHERE trans_timest = transaction_timestamp();

  RETURN NULL;
END;
$$
LANGUAGE 'plpgsql';
    • 2

相关问题