在同一事件之前和之后触发?填充子表PostgreSQL [英] Before and After trigger on the same event? Fill a child table PostgreSQL
问题描述
情况
我在PostgreSQL 9.5中有一个数据库,用于按时间存储对象位置。
I have a database in PostgreSQL 9.5 used to store object locations by time.
我有一个名为 position的主表,其中的各列(仅相关):
I have a main table named "position" with the columns (only relevant):
-
position_id
-
position_timestamp
-
object_id
position_id
position_timestamp
object_id
它在object_id上分为100个子表,条件是:
It is partitioned into 100 child tables on object_id with the condition:
CREATE TABLE position_object_id_00
( CHECK object_id%100 = 0 )
INHERITS ( position );
以此类推。我通过模数关系进行分区,以平均分配对象。
每个孩子都在 position_id和object_id
(两个不同的索引)上建立索引。
And so on for the others children. I partitioned with a modulus relation to distribute equally the objects.
Each child is indexed on position_id and object_id
(two different indexes).
重定向的触发器在子项上插入:
The trigger to redirect inserts on children is:
CREATE TRIGGER insert_position_trigger
BEFORE INSERT ON position
FOR EACH ROW EXECUTE PROCEDURE insert_position();
然后过程 insert_position()
查找正确的子表来插入数据,将其插入,然后返回NEW对象:
And the procedure insert_position()
looks for the right child table to insert the data, inserts it and then return the NEW object:
CREATE OR REPLACE FUNCTION insert_position() RETURNS TRIGGER AS $insert_position$
DECLARE
BEGIN
--Look for child table
[...]
--Insert data in right child table
[...]
RETURN NEW;
END;
$insert_position$ LANGUAGE plpgsql;
我有一个汇总表 object_last_known_position
与触发器更新的列相同:
I have a summary table object_last_known_position
with the same columns that is updated with the trigger:
CREATE TRIGGER update_object_last_known_position
AFTER INSERT OR UPDATE ON position
FOR EACH ROW
EXECUTE PROCEDURE update_object_last_known_position();
过程 update_object_last_known_position()
基本上检查是否 position_timestamp
是较新的,然后删除较旧的条目并使用在INSERT或UPDATE查询(NEW)上传递的数据创建一个新条目。
The procedure update_object_last_known_position()
basically checks if the position_timestamp
is more recent, then delete the older entry and create a new entry with the data passed on the INSERT or UPDATE query (NEW).
问题
因此,这两个触发器对同一事件做出反应:插入位置,一个在之前,其他是在
之后。 insert_position()
返回新值使我可以在触发器 update_object_last_known_position()
中使用NEW ,这是绝对必要的。但这会在主表位置插入数据。因此,我的数据现在已被复制。
So these two triggers react to the same event: insert on position, one is before, the other is after
Returning new for insert_position()
allows me to use NEW in the trigger update_object_last_known_position()
, and this is absolutely necessary. But doing that, it also insert the data on the master table position. So my data is now duplicated.
我试图将这两个触发器放在前面,如果我这样插入数据,它们都会在我插入数据时执行,但是如果我删除了不执行过程 insert_position()
中的
I tried to put the two triggers before, they both execute when I insert data if I let it like that, but if I remove the "return new" from the procedure insert_position()
, update_object_last_known_position()
is not executed.
我被这个问题困扰,在插入数据时,我没有找到一种方法来执行这两个触发器而不填充主表位置。
I am stuck with this issue and I didn't find a way to execute both of these triggers without filling the master table position when I insert data.
因此,如果您有任何想法,我将不胜感激:)
So if you have any ideas, I will really appreciate :)
感谢您的帮助!
编辑
解决方案
感谢答案
我合并了两个触发器: insert_position()
现在直接调用 update_object_last_known_position
。为此,我将 update_object_last_known_position
修改为带有参数的存储过程。该参数是刚刚创建的位置 insert_position()
的ID,因此我能够找到它并检索信息。
(在另一个触发器内调用 update_object_last_known_position
意味着我们不能再使用 NEW
)
insert_position()
的返回类型现在为 NULL
,并且一切正常:)
I "merged" my two triggers: insert_position()
now calls update_object_last_known_position
directly. For that, I modified update_object_last_known_position
to a stored procedure with a parameter. The parameter is the id of the position insert_position()
just created, so I am able to find it and retrieve information.
(Calling update_object_last_known_position
inside the other trigger means we cannot use NEW
anymore)
And obviously return type for insert_position()
is now NULL
, and everything works fine :)
推荐答案
如果我对您的理解正确,那么您将尝试:
If I understand you correctly you are trying to:
- 停止插入,然后将其替换为插入到另一个表中(由触发器确定)
- 更新摘要表(
删除
/插入
)指向新行。
- Stop the insert, and replace it with an insert into another table (determined by the trigger)
- Update a summary table (
delete
/insert
) to point to the new row.
您的问题是1个停靠点2发生了什么?这是合乎逻辑的,因为您已经停止了插入,因此也停止了对该插入的任何处理。
Your problem is that 1 stops 2 from happening? That's logical because you've stopped the insert so you've stopped any processing on the insert as well.
因此,要解决此问题,您有几个选择(选择1和2个相似)
So to solve this you have a couple of options (options 1 and 2 are similar)
- 从
update_object_last_known_position()
> insert_position()并且只有一个触发器 - 为
insert_position()
和update_object_last_known_position()
并且只有一个触发器。 - 将触发器放入
update_object_last_known_position()
可能插入insert_position()
的所有表上。
- Call
update_object_last_known_position()
frominsert_position()
and only have one trigger - Create a wrapper method for both
insert_position()
andupdate_object_last_known_position()
and have only one trigger. - Put the trigger for
update_object_last_known_position()
on all of the tables thatinsert_position()
might insert into.
这篇关于在同一事件之前和之后触发?填充子表PostgreSQL的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!