Sample Header Ad - 728x90

Speed up PostgreSQL logical decoding plugin

1 vote
1 answer
225 views
I'm writing a new logical decoding output plugin for PostgreSQL inspired by wal2json . But mine produces JSON by using SPI to repeatedly invoke to_json(...) on column values (instead of wal2json's simpler JSON output that doesn't work well for some data types). Testing shows that this is pretty CPU intensive. Is there a way to achieve the same result with better performance? Could you somehow directly invoke row_to_json on the tuple given to the change callback? Is there a way to directly invoke to_json on each column value without SPI? (My attempts to call those functions directly with DirectFunctionCall1Coll crashed the server, so any pointers are welcome!) ---- Here's what the functioning, but CPU-intensive, code looks like:
spiArgType = columnInfo->atttypid;
/* Prepare the SQL */
spiPlan = SPI_prepare("SELECT to_json($1)::TEXT", 1, &spiArgType);
/* Execute it */
spiReturnValue = SPI_execute_plan(spiPlan, &columnValue, NULL, false, 1);
/* And if it succeeded, append to output string */
if (spiReturnValue vals, SPI_tuptable->tupdesc, 1, &isNull);
  appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
}
SPI_freeplan(spiPlan);
---- A failed attempt using OidFunctionCall1Coll:
TupleDesc cols = ...;
HeapTuple tuple = ...;
Form_pg_attribute colInfo;
Datum colValue;
bool isNull;

for (i=0; i natts; ++i) {
  colInfo = TupleDescAttr(cols, i);

  // (Omitted checks for dropped or system column)

  colValue = heap_getattr(tuple, i+1, cols, &isNull);
  if (! isNull) {
    // The following call fails, and emits this error:
    // ERROR:  could not determine input data type
    jsonDatum = OidFunctionCall1Coll(F_TO_JSON, InvalidOid, colValue);
    // This is never reached:
    appendStringInfo(out, "%s", TextDatumGetCString(jsonDatum));
  }
  // ...
}
Asked by csd (700 rep)
May 19, 2020, 11:09 PM
Last activity: Jun 12, 2025, 08:10 AM