调试 postgresql 触发器

debugging postgresql trigger

提问by Dave Vogt

I have this Trigger in Postgresql that I can't just get to work (does nothing). For understanding, there's how I defined it:

我在 Postgresql 中有这个触发器，我不能开始工作（什么都不做）。为了理解，我是这样定义的：

CREATE TABLE documents (
    ...
    modification_time timestamp with time zone DEFAULT now()
);

CREATE FUNCTION documents_update_mod_time() RETURNS trigger
AS $$
    begin
    new.modification_time := now();
    return new;
    end
$$
    LANGUAGE plpgsql;

CREATE TRIGGER documents_modification_time
    BEFORE INSERT OR UPDATE ON documents
    FOR EACH ROW
    EXECUTE PROCEDURE documents_update_mod_time();

Now to make it a bit more interesting.. How do you debug triggers?

现在让它变得更有趣..你如何调试触发器？

回答by Kev

1. Use the following code within a trigger function, then watch the 'messages' tab in pgAdmin3 or the output in psql:

   RAISE NOTICE 'myplpgsqlval is currently %', myplpgsqlval;       -- either this
   RAISE EXCEPTION 'failed';  -- or that
   

2. To see which triggers actually get called, how many times etc, the following statement is the life-saver of choice:

   EXPLAIN ANALYZE UPDATE table SET foo='bar'; -- shows the called triggers
   

   Note that if your trigger is not getting called and you use inheritance, it may be that you've only defined a trigger on the parent table, whereas triggers are not inherited by child tables automatically.

3. To step through the function, you can use the debugger built into pgAdmin3, which on Windows is enabled by default; all you have to do is execute the code found in ...\8.3\share\contrib\pldbgapi.sqlagainst the database you're debugging, restart pgAdmin3, right-click your trigger function, hit 'Set Breakpoint', and then execute a statement that would cause the trigger to fire, such as the UPDATE statement above.

1. 在触发器函数中使用以下代码，然后查看 pgAdmin3 中的“消息”选项卡或 psql 中的输出：

   RAISE NOTICE 'myplpgsqlval is currently %', myplpgsqlval;       -- either this
   RAISE EXCEPTION 'failed';  -- or that
   

2. 要查看实际调用了哪些触发器、调用了多少次等，以下语句是首选：

   EXPLAIN ANALYZE UPDATE table SET foo='bar'; -- shows the called triggers
   

   请注意，如果您的触发器没有被调用并且您使用了继承，则可能是您只在父表上定义了一个触发器，而子表不会自动继承触发器。

3. 要逐步执行该功能，您可以使用 pgAdmin3 中内置的调试器，该调试器在 Windows 上默认启用；您所要做的就是针对您正在调试的数据库执行...\8.3\share\contrib\pldbgapi.sql中的代码，重新启动 pgAdmin3，右键单击您的触发器函数，点击“设置断点”，然后执行会导致触发器触发的语句，例如上面的 UPDATE 语句。

回答by Dave Vogt

Turns out I was using inheritance in the above problem and forgot to mention it. Now for everybody who might run into this as well, here's some debugging hints:

原来我在上面的问题中使用了继承而忘了提及它。现在，对于可能遇到此问题的每个人，这里有一些调试提示：

Use the following code to debug what a trigger is doing:

使用以下代码调试触发器正在执行的操作：

RAISE NOTICE 'test';       -- either this
RAISE EXCEPTION 'failed';  -- or that

To see which triggers actually get called, how many times etc, the following statement is the life-saver of choice:

要查看实际调用了哪些触发器、调用了多少次等，以下语句是首选：

EXPLAIN ANALYZE UPDATE table SET foo='bar'; -- shows the called triggers

Then there's the one thing I didn't know before: triggers only fire when updating the exact table they're defined on. If you use inheritance, you MUST define them on the child tables as well!

然后还有一件事我以前不知道：触发器仅在更新它们定义的确切表时触发。如果您使用继承，您也必须在子表上定义它们！

回答by Kev

You can use 'raise notice' statements inside your trigger function to debug it. To debug the trigger not being called at all is another story.

您可以在触发器函数中使用“raise notice”语句来调试它。调试根本没有被调用的触发器是另一回事。

If you add a 'raise exception' inside your trigger function, can you still do inserts/updates?

如果您在触发器函数中添加“引发异常”，您还可以进行插入/更新吗？

Also, if your update test occurs in the same transaction as your insert test, now() will be the same (since it's only calculated once per transaction) and therefore the update won't seem to do anything. If that's the case, either do them in separate transactions, or if this is a unit test and you can't do that, use clock_timestamp().

此外，如果您的更新测试与插入测试发生在同一个事务中，那么 now() 将相同（因为每个事务只计算一次），因此更新似乎不会做任何事情。如果是这种情况，要么在单独的事务中执行它们，要么如果这是一个单元测试而您不能这样做，请使用 clock_timestamp()。

I have a unit test that depends on some time going by between transactions, so at the beginning of the unit test I have something like:

我有一个单元测试，它取决于事务之间的一些时间，所以在单元测试开始时，我有类似的东西：

ALTER TABLE documents
   ALTER COLUMN modification_time SET DEFAULT clock_timestamp();

Then in the trigger, use "set modification_time = default".

然后在触发器中，使用“set modify_time = default”。

So normally it doesn't do the extra calculation, but during a unit test this allows me to do inserts with pg_sleep in between to simulate time passing and actually have that be reflected in the data.

所以通常它不会做额外的计算，但在单元测试期间，这允许我在中间插入 pg_sleep 以模拟时间流逝，并实际将其反映在数据中。