Postgres INSERT触发“ ON CONFLICT DO NOTHING” [英] Postgres INSERT triggers with 'ON CONFLICT DO NOTHING'

问题描述

我在Postgres 9.5+中有一个 INSERT 语句，但是有时由于键冲突而实际上没有发生INSERT（我将在冲突上什么也不做）。

I have an INSERT statement in Postgres 9.5+, but the INSERT sometimes doesn't actually happen because of a key conflict (I've set ON CONFLICT DO NOTHING on the INSERT).

如果发生了INSERT，则触发器当然会运行。但是，如果由于键冲突而没有发生INSERT，触发器将仍然运行吗？

If the INSERT happens, then of course the trigger runs. But if the INSERT doesn't happen because of a key conflict, will triggers still run?

这是否取决于之前

Does it depend on whether it's a BEFORE or AFTER trigger?

推荐答案

对于之后触发器有更多的机会可取消 INSERT （以及其他触发器），从而不触发触发器。但是这个问题似乎不依赖于之前或之后。如果在UPSERT命令中跳过了该行（ INSERT ... ON CONFLICT DO NOTHING ），则在任何<$ c $之前 会跳过该行c> ON INSERT 触发器将触发。

For an AFTER trigger there are more "opportunities" to cancel an INSERT (with other triggers) and thereby not fire the trigger. But this problem does not seem to depend on BEFORE or AFTER. If the row is skipped in an UPSERT command (INSERT ... ON CONFLICT DO NOTHING), then it is skipped before any ON INSERT triggers would fire.

您必须检查 INSERT 对结果采取行动-就像我们在您的其他问题下讨论的一样：

You have to check the success of the INSERT and act upon the outcome - just like we discussed under your other question:

• 仅在不存在行的情况下在PL / pgSQL中运行SQL语句

• Run SQL statements in PL/pgSQL only if a row doesn't exist

您在那里找到了解决问题的方法。
如果触发器中的命令不依赖于插入行，则可以只使用带有多个单独命令的事务-或将其包装在函数中以方便使用。

You got solutions for the problem there. If the commands in the trigger do not depend on the inserted row, you might just use a transaction with several separate commands - or wrap this in a function for convenience.

如果触发器依赖中的命令取决于插入的行，则无论哪种情况，您都希望所涉及的行返回。然后，您有 INSERT或SELECT ...

If the commands in the trigger depend on the inserted row, you want the involved row back in either case. Then you have a classical case of INSERT or SELECT ...

• < a href = https://stackoverflow.com/questions/15939902/is-select-or-insert-in-a-function-prone-to-race-conditions/15950324#15950324>函数中是SELECT还是INSERT

• Is SELECT or INSERT in a function prone to race conditions?

...然后在其他命令中使用结果。您可以将其与修改数据的CTE链接起来：

... and then use the result in additional commands. You might chain that with data-modifying CTEs:

• 将插入的ID插入到另一个表中

• Insert inserted id to another table

我想到了使用 RULE 代替，它可以重写 INSERT 以运行其他命令，而与结果无关。比触发器更棘手，但是它会在之前插入，而 INSERT 可能会被取消。 但是 ，手册警告：

I thought of using a RULE instead, which can rewrite an INSERT to run additional commands, independent of the outcome. More tricky than a trigger, but it kicks in before the INSERT might be cancelled. However, the manual warns:

请注意， INSERT 包含 ON CONFLICT 子句不能用于具有 INSERT 或 UPDATE 规则。考虑使用
可更新视图。

Note that an INSERT containing an ON CONFLICT clause cannot be used on tables that have either INSERT or UPDATE rules. Consider using an updatable view instead.

所以，没有骰子。

这篇关于Postgres INSERT触发“ ON CONFLICT DO NOTHING”的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！