Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmwH2-0005Ru-7X for pgsql-interfaces@arkaria.postgresql.org; Thu, 13 Apr 2023 12:42:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1pmwH1-0001pz-4R for pgsql-interfaces@arkaria.postgresql.org; Thu, 13 Apr 2023 12:41:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1pmtIm-0002PF-Ir for pgsql-interfaces@lists.postgresql.org; Thu, 13 Apr 2023 09:31:36 +0000 Received: from mo4-p00-ob.smtp.rzone.de ([81.169.146.162]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1pmtIf-002c6v-BM for pgsql-interfaces@postgresql.org; Thu, 13 Apr 2023 09:31:35 +0000 ARC-Seal: i=1; a=rsa-sha256; t=1681378287; cv=none; d=strato.com; s=strato-dkim-0002; b=RZ8epm07m9nx6dOdKF6ss8EnAULA6ZIGNJVCWhkJc16vb+TH0b4jdLE+yUGV7/Ow2s FBRhFHi4HrguNQtXOiyS28SNGDk7b9s20ljpnFSrhKzHZOWVp1oUNyZqVkanI8ZaCX9e 5XKJsS0xClWF0zDw4nyjT7GHm2bFi3f8/gnRrH67q8ybFTaddjv2l5m61u3JVI4xOARh nbIJQZXR4OP0cfamXTrGu6f2oqdvs5g8E1Pg8b8KLc+e8vd2i9bxV0SobksK1njwBThV x0NnZunkPOrJGF1/FruJfn85H3aw/MIMhDFy3M74Te2Gm9KnwPuxpgGJweQE9N0YF+pq hyjA== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; t=1681378287; s=strato-dkim-0002; d=strato.com; h=Subject:Message-ID:To:From:Date:Cc:Date:From:Subject:Sender; bh=HZqAbi3awHR9iQYSLydkP9/Dtt7wqfXl0KyY14JPZSQ=; b=nM6OLiWKjR8CB+ymKUyN9+9klbpi8s+uMOdKaEsK30lWdZEYCsCPWcxtCZE/C7daD5 4hBeWm906yXK9YuuABXEKWO/7dSrnSfFDTPyoE1E4nYdjIVZfuEkVJ5GkbKIdNMK7Ygx k+a46pLty48x2idLKbfAscyRBMvGyM/NttbFHpX8uKqZ9cqSqEisDIEn5Su70J6qELTh c4VgiVrNOw+4Sgwcnz8hYsl4lJHRP9fNzFsq97rfkW8XoV+KEsRMR912GJuA0qS/jjQX k1sxQJs4Y7YQZMkkpvZahq/4Q5pS9TQfHvC6+fZg35lLQA9V2VmNXk5EvarnbMZSJy1C X8VQ== ARC-Authentication-Results: i=1; strato.com; arc=none; dkim=none X-RZG-CLASS-ID: mo00 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; t=1681378287; s=strato-dkim-0002; d=tuschehund.de; h=Subject:Message-ID:To:From:Date:Cc:Date:From:Subject:Sender; bh=HZqAbi3awHR9iQYSLydkP9/Dtt7wqfXl0KyY14JPZSQ=; b=Wc8Bzkm+koa23fGTUS8N67JqIsTKQ1PJIr61Nyfu/Ggrzg+e82hvTVFFZuNNBt/IR8 8oZ5NcPgDm0j3cqkAAYhs/16lrNrDz+qwkW4rWsZ1ysXVn10PKod0+4Ku+P/qTwBDPB4 ky10Z050DuW/f3Hy6/R5qYQjk+7NamM8uRTqbnbd8TmDQNcwbwWbNknJ48apJe62O7eQ CE/1GIBpzbjt7QJnETY0v8DZgZCdZk+6ie0qKWp11ENKrXpeRT1GqQTZ/C47cVvBJuHn 0en5/rrxfA6T9wTHmY+dn+tT5kllu7AnHemopsZMNXAMBNj09BvY9+6UOYOjWjCsgwDl z4ug== DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; t=1681378287; s=strato-dkim-0003; d=tuschehund.de; h=Subject:Message-ID:To:From:Date:Cc:Date:From:Subject:Sender; bh=HZqAbi3awHR9iQYSLydkP9/Dtt7wqfXl0KyY14JPZSQ=; b=9UR1ahGuIMX4wnh9PbzDGPj8UoZzf8F2We9brkttKWspiQTNibKW7iH9wGl/DAFAMg sF+UyvFZOMyttEsYC3CA== X-RZG-AUTH: ":PGsHfUWlW+tE6dGLopx2tLHUf2ZSPNp165/ViMXYddg/otfF87vGByLk0QyiSbpP+ZeH" Received: from ox-live-app104.back.ox.d0m.de by smtp-ox.front (RZmta 49.4.0 AUTH) with ESMTPSA id Y067e0z3D9VRptw (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (curve X9_62_prime256v1 with 256 ECDH bits, eq. 3072 bits RSA)) (Client did not present a certificate) for ; Thu, 13 Apr 2023 11:31:27 +0200 (CEST) Date: Thu, 13 Apr 2023 11:31:27 +0200 (CEST) From: pgchem pgchem To: "pgsql-interfaces@postgresql.org" Message-ID: <1595558642.2519076.1681378287741@webmail.strato.de> Subject: Re: C trigger significantly slower than PL/pgSQL? MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2519075_1246677297.1681378287738" X-Priority: 3 Importance: Normal X-Mailer: Open-Xchange Mailer v7.10.6-Rev40 X-Originating-Client: open-xchange-appsuite Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_2519075_1246677297.1681378287738 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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 ------=_Part_2519075_1246677297.1681378287738 MIME-Version: 1.0 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
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
------=_Part_2519075_1246677297.1681378287738--