Hi PostgreSQL Community.
I tried to rewrite some plv8 stored procedures, which process in bulk JSONB
documents, to PL/pgSQL.
A SP usually has to delete/update/add multiple key with the same document
and do it for multiple documents (~40K) in loop.
When updating a single key PL/pgSQL wins against plv8, but when I need to
update multiple keys with *jsonb_set*, timing increase linearly with number
of *jsonb_set*s and takes longer than similar SP in PLV8.
Below are test-cases I've used.
*QUESTION:* Is it expected behavior or I do something wrong or there are
some better approaches or we can treat datum as object?
test case:
PG 9.6, CentOS 7
CREATE TABLE public.configurationj2b
(
id integer NOT NULL PRIMARY KEY,
config jsonb NOT NULL
);
Each jsonb column has 3 top keys, and one of top-key ('data') has another
700-900 key-value pairs e.g. {"OID1":"Value1"}
PL/pgSQL SP
CREATE OR REPLACE FUNCTION public.process_jsonb()
RETURNS void AS
$BODY$
DECLARE
r integer;
cfg jsonb;
BEGIN
RAISE NOTICE 'start';
FOR r IN
SELECT id as device_id FROM devices
LOOP
select config into cfg from configurationj2b c where c.id = r;
--select jsonb one by one
-- MULTIPLE KEYs, Conditional Busiines Logic (BL) updates
*cfg := jsonb_set(cfg, '{data,OID1}', '"pl/pgsql1"');*
*IF cfg@>'{"data" : { "OID1":"pl/pgsql1"} }' THENcfg :=
jsonb_set(cfg, '{data,OID2}', '"pl/pgsql2"');END IF;IF
cfg@>'{"data" : { "OID2":"pl/pgsql2"} }' THENcfg := jsonb_set(cfg,
'{data,OID3}', '"pl/pgsql3"');END IF;IF cfg@>'{"data" : {
"OID3":"pl/pgsql3"} }' THENcfg := jsonb_set(cfg, '{data,OID4}',
'"pl/pgsql4"');END IF;IF cfg@>'{"data" : { "OID4":"pl/pgsql4"} }'
THENcfg := jsonb_set(cfg, '{data,OID5}', '"pl/pgsql5"');END IF;*
update configurationj2b c set config = cfg where c.id = r;
END LOOP;
RAISE NOTICE 'end';
RETURN;
END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
or in pseudo-code I would have
for-each child_jsonb do
begin
foreach (key-value in parent_jsonb) do
begin
*child_jsonb := jsonb_set(child_jsonb , '{key}', '"value"');*
end
update *child_jsonb * in db;
end;
plv8 snippet:
$BODY$var ids = plv8.execute('select id from devices');
var CFG_TABLE_NAME = 'configurationj2b';
var selPlan = plv8.prepare( "select c.config from " + CFG_TABLE_NAME + " c
where c.id = $1", ['int'] );
var updPlan = plv8.prepare( 'update ' + CFG_TABLE_NAME + ' set config = $1
where id = $2', ['json','int'] )
try {
for (var i = 0; i < ids.length; i++) {
var db_cfg = selPlan.execute([ids[i].id]);
var cfg = db_cfg[0].config;
var cfg_data = cfg['data'];
*cfg_data['OID1'] = 'plv8_01';*
*if (cfg_data['OID1'] == 'plv8_01') {cfg_data['OID2'] =
'plv8_02'};if (cfg_data['OID2'] == 'plv8_02') {
cfg_data['OID3'] = 'plv8_03'}if (cfg_data['OID3'] ==
'plv8_03') {cfg_data['OID4'] = 'plv8_04'}if
(cfg_data['OID4'] == 'plv8_04') {cfg_data['OID5'] =
'plv8_05'}*
updPlan.execute([cfg, ids[i].id]);
plv8.elog(NOTICE, "UPDATED = " + ids[i].id);
}
} finally {
selPlan.free();
updPlan.free();
}
return;$BODY$
but for now plv8 has other issues related to resource consumption.
So could I get similar performance in PL/pgSQL?
