Subject: Re: C trigger significantly slower than PL/pgSQL?

From: pgchem pgchem <[email-masked]>
Date: Thu, 13 Apr 2023 11:31:27 +0200 (CEST)

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 13 Apr 2023 11:42:11 -0400

pgchem pgchem <[email-masked]> writes:
> ...

Yeah, there's your problem.  Each time through the trigger, that query
is being parsed, planned, and executed from scratch.  plpgsql knows
how to cache the planned statement, so it's doing less work per-call.

			regards, tom lane

---

From: Ernst-Georg Schmid <[email-masked]>
Date: Fri, 14 Apr 2023 19:39:20 +0200

> how to cache the planned statement, so it's doing less work per-call.

Hello again,

thank you for your help.

I have now used SPI_prepare() and SPI_keepplan() to cache the plan in a 
static variable - and now it performs exactly like the PL/pgSQL version. 
Which also probably means that either the Trigger is so trivial that C 
makes no difference, or the observed TPS limit comes from somewhere else.

I have still a follow-up question, though. Since I'm not calling 
SPI_freeplan(), the cached plan lives forever, right? Which is no 
problem, since the trigger does the same statement over and over. But 
does this generate a memory leak? Or is the saved plan tied to the 
session and is deallocated when the session ends?

best regards

Ernst-Georg

---

From: Tom Lane <[email-masked]>
Date: Sat, 15 Apr 2023 13:31:55 -0400

Ernst-Georg Schmid <[email-masked]> writes:
> SPI_freeplan(), the cached plan lives forever, right?

Till the per-session server process exits.  I wouldn't call it
a leak as long as you can reuse the plan in later calls in that
session; a static variable's scope is fine for that.

			regards, tom lane