|
13.12.2019, 17:44
#39902789
Ссылка:
Ссылка на сообщение:
Ссылка с названием темы:
|
|
|
|
ДОбрый день.
Подскажите, в какую сторону искать. Есть функция принимает на вход массив текста(json) и для каждого элемента массива вызывает функцию которая обрабатывает этот элемент(json). При массиве в 3-5 элементов и json на 3 элемента функция отрабатывает нормально. При большем объеме массива функция паадет с ошибкой 'ERROR: unexpected failure to find arbiter index'.
Основная функция:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
CREATE OR REPLACE FUNCTION statistic.f_insert_p1_from_nodejs_d_arr(
vdata text[])
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
x_json text;
BEGIN
-----------------------------------------------------------------
FOREACH x_json IN ARRAY $1
LOOP
RAISE NOTICE 'ID object: %', x_json::json;
PERFORM statistic.f_insert_p1_from_nodejs_d(x_json::json);
PERFORM pg_sleep(0.1);
END LOOP;
return 0;
----------------------------------------------------------------------
END;
$BODY$;
Функция обработки элемента массива(json):
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
CREATE OR REPLACE FUNCTION statistic.f_insert_p1_from_nodejs_d(
vdata json)
RETURNS integer
LANGUAGE 'plpgsql'
COST 100
VOLATILE
AS $BODY$
DECLARE
varkey integer;
P_NAME text;
id_Device text;
BEGIN
id_Device:=vdata->>'id';
P_NAME:='p1';
-- check having id device
SELECT INTO varkey p.id
FROM inventorydb.param p
left join inventorydb.device as d on p.devid = d.id
left join inventorydb.object o on d.parentid = o.id
WHERE
lower(p.paramname)='p1'
and d.devtype = 4
and o.customid = id_Device;
--
IF varkey IS NULL THEN
RAISE NOTICE 'ID object not exists: %', id_Device;
RETURN 1;
-----------------------------------------------------------------
ELSE
-----------------------------------------------------------------
RAISE NOTICE 'Vdata: %, varkey: %, P_NAME: %, id_Device: %', vdata->'data', varkey, P_NAME, id_Device;
INSERT INTO statistic.vardata(
vartime, varid, varvalue, varqual)
SELECT date_trunc('minute',to_timestamp(f.time/1000)), varkey, f.value, 180
FROM json_to_recordset((vdata->'data')::json)
as f("time" numeric, "value" numeric)
ON CONFLICT (vartime, varid) DO NOTHING;
RETURN 0;
----------------------------------------------------------------------
END IF;
----------------------------------------------------------------------
END;
Объект который передается:
1.
2.
3.
4.
5.
6.
7.
8.
xlsJson = [
'{"id":"БС1313","data":[{"time":1572570000000,"value":6.22699999999895},{"time":1572573600000,"value":6.21099999999569},{"time":1572577200000,"value":6.19499999999243},{"time":1572580800000,"value":6.17200000000594}]}',
'{"id":"БС2311","data":[{"time":1572570000000,"value":4.5},{"time":1572573600000,"value":4.46100000001024},{"time":1572577200000,"value":4.35099999999511},{"time":1572580800000,"value":4.36699999999837}]}',
'{"id":"БС2500","data":[{"time":1572570000000,"value":8.5},{"time":1572573600000,"value":1.79200000001583},{"time":1572577200000,"value":7.23699999996461},{"time":1572580800000,"value":5.84799999999814}]}',
'{"id":"БС2608","data":[{"time":1572570000000,"value":4.63300000000163},{"time":1572573600000,"value":4.5},{"time":1572577200000,"value":4.5},{"time":1572580800000,"value":4.44499999999243}]}',
'{"id":"БС2638","data":[{"time":1572570000000,"value":4.96099999999569},{"time":1572573600000,"value":5.36000000000058},{"time":1572577200000,"value":5.2960000000021},{"time":1572580800000,"value":5.15699999999197}]}',
'{"id":"БС3161","data":[{"time":1572570000000,"value":8.88999999998487},{"time":1572573600000,"value":8.375},{"time":1572577200000,"value":8.21900000001187},{"time":1572580800000,"value":8.25}]}'
]
Лог ошибки:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
ERROR: unexpected failure to find arbiter index
CONTEXT: SQL statement "INSERT INTO statistic.vardata(
vartime, varid, varvalue, varqual)
SELECT date_trunc('minute',to_timestamp(f.time/1000)), varkey, f.value, 180
FROM json_to_recordset((vdata->'data')::json)
as f("time" numeric, "value" numeric)
ON CONFLICT (vartime, varid) DO NOTHING"
PL/pgSQL function statistic.f_insert_p1_from_nodejs_d(json) line 32 at SQL statement
SQL statement "SELECT statistic.f_insert_p1_from_nodejs_d(x_json::json)"
PL/pgSQL function statistic.f_insert_p1_from_nodejs_d_arr(text[]) line 11 at PERFORM
SQL state: XX000
|
|
|
