Postgres：缩小触发范围
（Postgres 8.3）
我正在使用一个数据库表X 100+列宽（遗憾的是我无法更改）其中许多表通过正常业务流程不断且非常频繁地更新。
我需要根据由异常业务流程更新的 X 中特定列 foo 的更新来更新表 Y。但是，由于针对 X 的更新数量非常多，因此只需应用一个触发器来检查 X.foo 来决定是否更新 Y不可接受的。
表 Y 也不是行尾，有一个祖先链，有几个深，所有这些都需要冒泡到根。
我能想到的唯一解决方案是：
- 将 X 分成多个表（不允许）
- 显式更新 Y （和 Z 和其他）作为更新X的业务逻辑的一部分，但这会占用很大的空间，并且当有人必须在另一个中实现相同的内容时，他们可能会犯错或错过它。过程。这显然不是一个好的设计（我正在尝试逐步修复）。
有谁知道按列或任何其他替代方案限制触发器执行的方法？触发视图？其他巫术？
(Postgres 8.3)
I'm working with a DB table X 100+ columns wide (which I can't change sadly) many of which get updated constantly and very frequently by normal business process.
I have a requirement to update table Y based on updates to a particular column foo in X updated by unusual business process. However, because of the very high number of updates against X simply applying a trigger which inspects X.foo to decide whether to update Y is considered unacceptable.
Table Y is not the end of the line either, there is a chain of ancestors a few deep, all of which need to bubble up to the root.
The only solutions I can think of are:
- breaking X into multiple tables (not allowed to)
- explicitly making the updates to Y (and Z and others) as part of the business logic for updating X but this is going to have a big footprint and leaves a lot of room for somebody getting it wrong or missing it when they have to implement the same in another process. And this is clearly just not good design (which I'm trying to gradually fix where I can).
Does anyone know a way to limit trigger execution by column or any other alternative? Triggers on views? Other voodoo?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
您也许可以使用规则做一些事情，但之前已经说过，触发器可能应该“足够好”。但如果您试图解决管理问题而不是技术问题，规则可能会对您有所帮助。他们会在执行期间更早地申请。请注意它们的一些陷阱，通常是序列等。
You might be able to do something with rules, but has has been previously said, triggers should probably be "good enough". But if you are trying to solve a management problem rather than a technical one, rules might help you. They'll apply much earlier during execition. Beware of some pitfalls with them typically with sequences and such.
不幸的是，直到版本 9.0 发布（其中包括列触发器和 WHEN 子句对于触发器）你必须求助于第二种解决方案。
Unfortunately until version 9.0 is released (which includes both column triggers and a WHEN clause for triggers) you'll have to resort to the second solution.
为什么标准触发器不可接受？运行一个函数，首先检查NEW.column_name=OLD.column_name是否相同，然后返回是否相同，这是很便宜的。你可以在一秒钟内发射数十万个。您的系统每秒可能无法处理超过数百个事务——少了 3 个数量级。
有条件后，9.0中的延迟触发器会更快，但只比普通触发器快2倍左右。请参阅相关帖子< /a> 在 Depesz 的博客中。您可以在 Postgres 9 开发版本中运行一些基准测试。
Why a standard trigger is unacceptable? Running a function, which first checks if
NEW.column_name=OLD.column_name, and just returns if it is the same, is cheap. You can fire hundreds of thousands of them in a second. Your system probably can not handle more than several hundred transactions per second — 3 orders of magnitude less.Conditional after, deferred triggers in 9.0 would be faster, but only about 2 times faster than ordinary trigger. See a relevant post in Depesz's blog. You can run some benchmarks in Postgres 9 development version.