Re: C trigger significantly slower than PL/pgSQL?
| От | pgchem pgchem |
|---|---|
| Тема | Re: C trigger significantly slower than PL/pgSQL? |
| Дата | |
| Msg-id | 1595558642.2519076.1681378287741@webmail.strato.de обсуждение исходный текст |
| Ответ на | C trigger significantly slower than PL/pgSQL? (pgchem pgchem <pgchem@tuschehund.de>) |
| Ответы |
Re: C trigger significantly slower than PL/pgSQL?
|
| Список | pgsql-interfaces |
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
В списке pgsql-interfaces по дате отправления: