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
Last activity: Jun 12, 2025, 08:10 AM