Re: C trigger significantly slower than PL/pgSQL?

Hello all,

as requested, here is the C code:

#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/rel.h"
#include "utils/fmgrprotos.h"

PG_MODULE_MAGIC;

#ifdef _WIN32
extern PGDLLEXPORT Datum tf_break_cycle(PG_FUNCTION_ARGS);
#else
Datum tf_break_cycle_c(PG_FUNCTION_ARGS);
#endif

PG_FUNCTION_INFO_V1(tf_break_cycle);
Datum
tf_break_cycle(PG_FUNCTION_ARGS)
{
TriggerData* trigdata = (TriggerData*)fcinfo->context;
TupleDesc tup_desc;
HeapTuple rettuple = NULL;
bool is_replicated, is_local, isnull_is_replicated, isnull_is_local, null_flag = false;
int ret, col_num;
Datum change_val;

if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, "tf_break_cycle: not called by trigger manager");

if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event)) {
rettuple = trigdata->tg_newtuple;
}
else {
rettuple = trigdata->tg_trigtuple;
}

if (TRIGGER_FIRED_AFTER(trigdata->tg_event))
elog(ERROR, "tf_break_cycle must not fire AFTER");

tup_desc = trigdata->tg_relation->rd_att;
if ((ret = SPI_connect()) < 0)
elog(ERROR, "tf_break_cycle: SPI_connect returned %d", ret);

ret = SPI_execute("SELECT pg_backend_pid() = ANY((SELECT pid FROM pg_stat_activity WHERE backend_type = 'logical replication worker'))::boolean AS is_replicated", true, 1);

if (ret < 0)
elog(ERROR, "tf_break_cycle: SPI_execute returned %d", ret);

is_replicated = DatumGetBool(SPI_getbinval(SPI_tuptable->vals[0],
SPI_tuptable->tupdesc,
1,
&isnull_is_replicated));

col_num = SPI_fnumber(tup_desc, "is_local");

is_local = DatumGetBool(SPI_getbinval(rettuple,
tup_desc,
col_num,
&isnull_is_local));

if (is_replicated) {
if (!is_local) {
rettuple = NULL;
}
else {
change_val = BoolGetDatum(false);

rettuple = heap_modify_tuple_by_cols(rettuple, tup_desc,
1, &col_num, &change_val, &null_flag);

//rettuple = SPI_modifytuple(trigdata->tg_relation, rettuple, 1, &col_num, &change_val, &null_flag);
}
}

SPI_finish();

return PointerGetDatum(rettuple);
}

If I use SPI_modifytuple() or heap_modify_tuple_by_cols() makes no difference.

And the PL/pgSQL version:

CREATE OR REPLACE FUNCTION traktor.tf_break_cycle()
RETURNS trigger
LANGUAGE plpgsql
STRICT
AS $function$
declare
begin
if pg_backend_pid() = ANY((select pid from pg_stat_activity where backend_type = 'logical replication worker')) then
if not NEW.is_local then
return null;
else
NEW.is_local = false;
end if;
end if;
return NEW;
end;
$function$;

PostgreSQL 15.2 on Windows 11 64 Bit, Compiler is Visual Studio Community 2022 17.5.3 64 Bit.

best regards,

Ernst-Georg