在C中等效的plpgsql触发器 [英] Equivalent plpgsql trigger in C

问题描述

我有一个PostgreSQL 9.0服务器，并且在某些表上使用了继承性，因此，我必须通过这样的触发器来模拟外键：

I've a PostgreSQL 9.0 server and I'm using heritage on some tables, for this reason I have to simulate foreign keys through triggers like this:

CREATE OR REPLACE FUNCTION othertable_before_update_trigger()
RETURNS trigger AS
$BODY$
DECLARE
  sql   VARCHAR;
  rows  SMALLINT;
BEGIN
  IF (NEW.parenttable_id IS DISTINCT FROM OLD.parenttable_id) THEN
    sql  := 'SELECT id '
         || 'FROM parentTable '
         || 'WHERE id = ' || NEW.parenttable_id || ';';
     BEGIN
        EXECUTE sql;
        GET DIAGNOSTICS rows = ROW_COUNT;

     EXCEPTION
        WHEN OTHERS THEN
           RAISE EXCEPTION 'Error when I try find in parentTable the id %. SQL: %. ERROR: %',
                            NEW.parenttable_id,sql,SQLERRM;
     END;

     IF rows = 0 THEN
        RAISE EXCEPTION 'Not found a row in parentTable with id %. SQL: %.',NEW.parenttable_id,sql;
     END IF;
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

但是由于性能原因，我尝试在C代码中创建等效触发器：

But due to performance I try to create a equivalent trigger in C code:

#include "postgres.h"
#include "executor/spi.h"       /* this is what you need to work with SPI */
#include "commands/trigger.h"   /* ... and triggers */

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
extern Datum othertable_before_update_trigger(PG_FUNCTION_ARGS);

PG_FUNCTION_INFO_V1(othertable_before_update_trigger);

Datum
 othertable_before_update_trigger(PG_FUNCTION_ARGS) {

   TriggerData *trigdata = (TriggerData *) fcinfo->context;
   TupleDesc   tupdesc;
   HeapTuple   rettuple;
   bool        isnull;
   int         ret, i;

   /* make sure it's called as a trigger at all */
   if (!CALLED_AS_TRIGGER(fcinfo))
     elog(ERROR, "othertable_before_update_trigger: not called by trigger manager");

   /* tuple to return to executor */
   if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
     rettuple = trigdata->tg_newtuple;
   else
     rettuple = trigdata->tg_trigtuple;

   tupdesc = trigdata->tg_relation->rd_att;

   /* connect to SPI manager */
   if ((ret = SPI_connect()) < 0)
     elog(ERROR, "othertable_before_update_trigger (fired %s): SPI_connect returned %d", "before", ret);

   [A]

   [B]

   return PointerGetDatum(rettuple);
 }

我需要填写以下代码：

1. [A] ：获取 parenttable_id 的先前值和新值。用：

1. [A]: get the previous and new values for parenttable_id. With:

int32 att = DatumGetInt32（heap_getattr（rettuple，1，tupdesc，& isnull））;

int32 att = DatumGetInt32(heap_getattr(rettuple, 1, tupdesc, &isnull));

或

int32 att = DatumGetInt32（SPI_getbinval（rettuple，tupdesc，1，& isnull））;

int32 att = DatumGetInt32(SPI_getbinval(rettuple, tupdesc, 1, &isnull));

我只能得到旧值 parenttable_id ，而不能得到新值。即使我尝试将列名而不是其列号用于：

I can get only the old value of parenttable_id but not the new value. Even if I try to use the column name instead of their number with:

GetAttributeByName (rettuple->t_data, "parenttable_id", &isnull);

获取错误：记录类型尚未注册

1. [B] ：执行查询 SELECT parent from ParentTable WHERE id = NEW.parenttable_id

1. [B]: execute the query SELECT id FROM parentTable WHERE id = NEW.parenttable_id

我找到了函数 SPI_execute_with_args ，但是我没有找到针对此情况的示例。

I found the function SPI_execute_with_args, but I haven't found examples of this for my case.

预先感谢。

推荐答案

这并不令我感到惊讶触发器，它将受益于迁移到C。您可以利用pl / pgsql中大量计划的缓存，这可能比迁移到C可以加快处理速度更多。此外，这里还有两个严重的性能危险信号，这些使我感到很值得修复。

This does not strike me as the sort of trigger that will benefit from moving to C. You can take advantage of a lot of caching of plans in pl/pgsql and this is likely to help more than moving to C will speed things up. Additionally there are two big performance red flags here that strike me as worth fixing.

首先是EXCEPTION块具有显着的性能成本。您在这里所做的只是以更友好的方式报告异常。如果性能有问题，最好将其删除。

The first is that EXCEPTION blocks have significant performance costs. All you are doing here is reporting an exception in more friendly terms. You would do better to just remove it if performance is an issue.

第二个是您的EXECUTE，这意味着查询计划将永远不会被缓存。您确实应该将其更改为直接查询。

The second is your EXECUTE which means that the query plan will never be cached. You really should change this to a straight query.

当您将其与可能导致后端崩溃或更糟糕的C语言触发器结合使用时，我认为您将花费大量精力重写触发器，以使其性能提升比通过pl / pgsql重写触发器要少。

When you combine this with the possibility of a C-language trigger causing crashes or worse in the back-end, I think you will be putting a lot of effort into rewriting the trigger for fewer performance gains than you could get by rewriting it in pl/pgsql.

这篇关于在C中等效的plpgsql触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！