如何避免在 PostgreSQL 9.2.1 中循环触发调用 [英] How To Avoid Looping Trigger Calls In PostgreSQL 9.2.1

问题描述

我有一张桌子:

创建表 field_data.soil_samples (pgid SERIAL NOT NULL,sample_id 文本，project_id 文本，utm_zone 整数，utm_easting 整数，utm_northing 整数，wgs​​84_longitude 双精度，wgs​​84_latitude 双精度，yt_albers_geom 几何(点，3578)，约束soil_samples_pk PRIMARY KEY (pgid))

yt_albers_geom 中的 PostGIS 2.0 几何图形是使用触发器创建的，该触发器在针对该表的 INSERTS 上触发.如果插入的记录满足以下条件之一，则生成几何:

1. wgs84_latitude 和 wgs84_longitude 字段都不为空
2. utm_zone、utm_easting 和 utm_northing 都不为空

现在，我很困惑如何进行更新以实现以下目标:

1. 当对 utm_zone、utm_easting 或 utm_northing 进行更新后，然后 wgs_84_latitude、wgs​​84_longitude 和 yt_albers_geom 由触发器更新
2. 当对 wgs84_latitude 或 wgs84_longitude 进行更新后，所有 utm_ 字段以及 yt_albers_geom.
3. 当对 yt_albers_geom 进行更新后，所有坐标字段都会更新.

似乎这些触发器中的任何一个都会导致触发器触发的无限循环，对吗?

解决方案

您可以使用标准触发器来做到这一点BEFORE UPDATE OF ... ON ....
CREATE TRIGGER 手册告知:

<块引用>

触发器只会在列出的列中至少有一个是提到作为 UPDATE 命令的目标.

再往下:

<块引用>

特定于列的触发器(使用 UPDATE OF column_name 定义的触发器语法)将在其任何列被列为目标时触发UPDATE 命令的 SET 列表.列的值可能是即使没有触发触发器也会改变，因为 对不考虑 BEFORE UPDATE 触发器的行内容.

粗体强调我的.所以没有无限循环，因为触发器内部的更新不会调用另一个触发器.

测试用例

创建测试表(简化，没有不相关的行):

创建表土壤样本(pgid 串行主键,utm_zone 整数,utm_easting 整数,utm_northing 整数,wgs84_longitude 双精度,wgs84_latitude 双精度,yt_albers_geom 双精度);

您的第一个要求的虚拟触发器:

<块引用>

当对 utm_zone、utm_easting 或 utm_northing 进行更新后，然后wgs_84_latitude、wgs84_longitude 和 yt_albers_geom 由触发器更新.

CREATE OR REPLACE FUNCTION trg_upbef_utm() RETURNS trigger AS$func$开始NEW.wgs84_latitude := NEW.wgs84_latitude + 10;NEW.wgs84_longitude := NEW.wgs84_longitude + 10;NEW.yt_albers_geom := NEW.yt_albers_geom + 10;退货；结尾$func$ LANGUAGE plpgsql;创建触发器 upbef_utm更新 utm_zone、utm_easting、utm_northing ON 土壤样本之前每行WHEN (NEW.utm_zone 与 OLD.utm_zone 不同或NEW.utm_easting 与 OLD.utm_easting 不同或NEW.utm_northing 与 OLD.utm_northing 不同)——可选执行程序 trg_upbef_utm();

WHEN 子句是可选的.防止触发器在没有实际更改的值时触发.

您的第二个要求的虚拟触发器:

<块引用>

当对 wgs84_latitude 或 wgs84_longitude 进行更新后，则所有utm_ 字段以及 yt_albers_geom 字段已更新.

CREATE OR REPLACE FUNCTION trg_upbef_wgs84() RETURNS trigger AS$func$开始NEW.utm_zone := NEW.utm_zone + 100;NEW.utm_easting := NEW.utm_easting + 100;NEW.utm_northing := NEW.utm_northing + 100;NEW.yt_albers_geom := NEW.yt_albers_geom + 100;退货；结尾$func$ LANGUAGE plpgsql;创建触发器 upbef_wgs84在 wgs84_latitude、wgs84_longitude 更新之前，soil_samples每行何时(NEW.wgs84_latitude 与 OLD.wgs84_latitude 不同或NEW.wgs84_longitude 与 OLD.wgs84_longitude 不同)——可选执行程序 trg_upbef_wgs84();

触发第三个要求...

测试

插入土壤样本值(1、1、1、1、2、2、3)返回*；

触发upbef_utm:空更新，什么也没发生:

UPDATE 土壤样本 SET utm_zone = 1 RETURNING *;

使用实际更改更新:第二个触发器 upbef_wgs84 不会在 UPDATE OF utm_zone 上触发！

UPDATE 土壤样本 设置 utm_zone = 0 返回 *;

触发upbef_wgs84:

UPDATE 土壤样本 SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle 演示.

I have a table:

CREATE TABLE field_data.soil_samples (
 pgid SERIAL NOT NULL,
 sample_id text,
 project_id text,
 utm_zone integer,
 utm_easting integer,
 utm_northing integer,
 wgs84_longitude double precision,
 wgs84_latitude double precision,
 yt_albers_geom geometry(Point,3578),
 CONSTRAINT soil_samples_pk PRIMARY KEY (pgid)
)

The PostGIS 2.0 geometry in yt_albers_geom is created using a trigger which fires on INSERTS against this table. If the record being inserted satisfies one of the following conditions, the geometry is generated:

1. Both wgs84_latitude and wgs84_longitude fields are not null
2. Each of utm_zone, utm_easting, and utm_northing are not null

Now, I am confused about how to do updates which achieve the following:

1. When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger
2. When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.
3. When an update is done to yt_albers_geom, all of the coordinate fields are updated.

It seems that any of these triggers would cause an infinite loop of trigger firing, correct?

解决方案

You can do this with standard triggers BEFORE UPDATE OF ... ON ....
The manual on CREATE TRIGGER informs:

The trigger will only fire if at least one of the listed columns is mentioned as a target of the UPDATE command.

And further down:

A column-specific trigger (one defined using the UPDATE OF column_name syntax) will fire when any of its columns are listed as targets in the UPDATE command's SET list. It is possible for a column's value to change even when the trigger is not fired, because changes made to the row's contents by BEFORE UPDATE triggers are not considered.

Bold emphasis mine. So no infinite loops, because the the updates inside the trigger do not invoke another trigger.

Test case

Create test table (simplified, without irrelevant rows):

CREATE TABLE soil_samples (
  pgid SERIAL PRIMARY KEY

 ,utm_zone integer
 ,utm_easting integer
 ,utm_northing integer

 ,wgs84_longitude double precision
 ,wgs84_latitude double precision

 ,yt_albers_geom double precision
);

Dummy trigger for your first requirement:

When an update is done to utm_zone, utm_easting, or utm_northing, then wgs_84_latitude, wgs84_longitude, and yt_albers_geom are updated by a trigger.

CREATE OR REPLACE FUNCTION trg_upbef_utm()  RETURNS trigger AS
$func$
BEGIN
   NEW.wgs84_latitude  := NEW.wgs84_latitude + 10;
   NEW.wgs84_longitude := NEW.wgs84_longitude + 10;
   NEW.yt_albers_geom  := NEW.yt_albers_geom + 10;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_utm
BEFORE UPDATE OF utm_zone, utm_easting, utm_northing ON soil_samples
FOR EACH ROW
WHEN (NEW.utm_zone     IS DISTINCT FROM OLD.utm_zone    OR
      NEW.utm_easting  IS DISTINCT FROM OLD.utm_easting OR
      NEW.utm_northing IS DISTINCT FROM OLD.utm_northing)  -- optional
EXECUTE PROCEDURE trg_upbef_utm();

The WHEN clause is optional. Prevents the trigger from firing when no value has actually changed.

Dummy trigger for your second requirement:

When an update is done to wgs84_latitude or wgs84_longitude, then all the utm_ fields are updated, as well as yt_albers_geom.

CREATE OR REPLACE FUNCTION trg_upbef_wgs84()  RETURNS trigger AS
$func$
BEGIN
   NEW.utm_zone       := NEW.utm_zone + 100;
   NEW.utm_easting    := NEW.utm_easting + 100;
   NEW.utm_northing   := NEW.utm_northing + 100;
   NEW.yt_albers_geom := NEW.yt_albers_geom + 100;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER upbef_wgs84
 BEFORE UPDATE OF wgs84_latitude, wgs84_longitude ON soil_samples
 FOR EACH ROW
 WHEN (NEW.wgs84_latitude  IS DISTINCT FROM OLD.wgs84_latitude OR
       NEW.wgs84_longitude IS DISTINCT FROM OLD.wgs84_longitude)  -- optional
 EXECUTE PROCEDURE trg_upbef_wgs84();

Trigger for third requirement along these lines ...

Test

INSERT INTO soil_samples VALUES (1, 1,1,1, 2,2, 3) RETURNING *;

Trigger upbef_utm: empty update, nothing happens:

UPDATE soil_samples SET utm_zone = 1 RETURNING *;

Update with actual change: The second trigger upbef_wgs84 will not fire on UPDATE OF utm_zone!

UPDATE soil_samples SET utm_zone = 0 RETURNING *;

Trigger upbef_wgs84:

UPDATE soil_samples SET wgs84_latitude = 0 RETURNING *;

-> SQLfiddle demo.

这篇关于如何避免在 PostgreSQL 9.2.1 中循环触发调用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！