array size exceeds the maximum allowed (1073741823) when building a json

Поиск
Список
Период
Сортировка
От Nicolas Paris
Тема array size exceeds the maximum allowed (1073741823) when building a json
Дата
Msg-id CA+ssMORBYVFxedSXVVxL5VPeo=9AbYRQ-zbpuKJm6vEjmJ1CFA@mail.gmail.com
обсуждение исходный текст
Ответы Re: array size exceeds the maximum allowed (1073741823) when building a json
Список pgsql-performance
Hello,

I run a query transforming huge tables to a json document based on a period.
It works great for a modest period (little dataset).
However, when increasing the period (huge dataset) I get this error:
SQL ERROR[54000]
ERROR: array size exceeds the maximum allowed (1073741823)


Thanks by advance,
Informations:
postgresql 9.4
shared_buffers = 55GB
64bit Red Hat Enterprise Linux Server release 6.7
the query:
WITH sel AS
(SELECT ids_pat,
ids_nda
FROM eds.nda
WHERE (dt_deb_nda >= '20150101'
AND dt_deb_nda <= '20150401')),
diag AS
( SELECT ids_nda_rum,
json_agg(diago) AS diago,
count(1) AS total
FROM
(SELECT ids_nda_rum,
json_build_object( 'cd_cim', cd_cim, 'lib_cim',lib_typ_diag_tr, 'dt_cim',dt_exec) AS diago
FROM eds.fait_diag_tr
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
ORDER BY dt_exec) AS diago2
GROUP BY ids_nda_rum),
act AS
( SELECT ids_nda_rum,
json_agg(acto) AS acto,
count(1) AS total
FROM
( SELECT ids_nda_rum,
json_build_object( 'cd_act',cd_ccam, 'dt_act',dt_exec) AS acto
FROM eds.fait_act_tr
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
ORDER BY dt_exec) AS acto2
GROUP BY ids_nda_rum ),
ghm AS
( SELECT ids_nda_rum,
json_agg(ghmo) AS ghmo,
count(1) AS total
FROM
( SELECT ids_nda_rum,
json_build_object( 'cd_ghm',cd_ghm, 'cd_ghs',cd_ghs, 'status',lib_statut_tr, 'dt_maj_rum_ghm',dt_maj_rum_ghm) AS ghmo
FROM eds.nda_rum_ghm_tr
LEFT JOIN eds.nda_rum_tr rum USING (ids_nda_rum)
WHERE nda_rum_ghm_tr.ids_nda IN
(SELECT ids_nda
FROM sel)
AND rum.cd_rum = 'RSS'
ORDER BY dt_maj_rum_ghm) AS ghmo
GROUP BY ids_nda_rum ),
lab AS
(SELECT ids_nda,
json_agg(lab) AS labo,
count(1) AS total
FROM
(SELECT ids_nda,
json_build_object( 'valeur_type_tr',valeur_type_tr, 'dt_fait', dt_fait, 'unite',unite, 'cd_test_lab',cd_test_lab, 'valeur_sign_tr',valeur_sign_tr, 'valeur_num_tr',valeur_num_tr, 'valeur_text_tr',valeur_text_tr, 'valeur_abnormal_tr',valeur_abnormal_tr) AS lab
FROM eds.fait_lab_tr
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
ORDER BY dt_fait) AS labo
GROUP BY ids_nda),
rum AS
( SELECT ids_nda,
json_agg(rum) AS rumo,
count(1) AS total
FROM
( SELECT ids_nda,
json_build_object( 'cd_rum',cd_rum, 'dt_deb_rum', dt_deb_rum, 'dt_fin_rum', dt_fin_rum, 'diag', json_build_object('total',diag.total,'diag',diag.diago), 'act', json_build_object('total',act.total,'act',act.acto) ) AS rum
FROM eds.nda_rum_tr
LEFT JOIN diag USING (ids_nda_rum)
LEFT JOIN act USING (ids_nda_rum)
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
AND cd_rum = 'RUM' ) AS rumo
GROUP BY ids_nda),
rss AS
( SELECT ids_nda,
json_agg(rss) AS rsso,
count(1) AS total
FROM
( SELECT ids_nda,
json_build_object( 'cd_rum',cd_rum, 'dt_deb_rss', dt_deb_rum, 'dt_fin_rss', dt_fin_rum, 'ghm', json_build_object('total',ghm.total,'ghm',ghm.ghmo), 'rum', json_build_object('total',rum.total, 'rum',rum.rumo) ) AS rss
FROM eds.nda_rum_tr
LEFT JOIN ghm USING (ids_nda_rum)
LEFT JOIN rum USING (ids_nda)
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
AND cd_rum = 'RSS' ) AS rss
GROUP BY ids_nda),
enc AS
(SELECT 'Encounter' AS "resourceType",
cd_nda AS "identifier",
duree_hospit AS "length",
lib_statut_nda_tr AS "status",
lib_type_nda_tr AS "type",
ids_pat,
json_build_object('start', dt_deb_nda,'end', dt_fin_nda) AS "appointment",
json_build_object('total',lab.total, 'lab',lab.labo) AS lab,
json_build_object('total',rss.total, 'rss',rss.rsso) AS rss
FROM eds.nda_tr
LEFT JOIN lab USING (ids_nda)
LEFT JOIN rss USING (ids_nda)
WHERE ids_nda IN
(SELECT ids_nda
FROM sel)
ORDER BY dt_deb_nda ASC)
SELECT 'Bundle' AS "resourceType",
count(1) AS total,
array_to_json(array_agg(ROW)) AS encounter
FROM
(SELECT 'Patient' AS "resourceType",
ipp AS "identifier",
nom AS "name",
cd_sex_tr AS "gender",
dt_nais AS "birthDate",
json_build_array(enc.*) AS encounters
FROM eds.patient_tr
INNER JOIN enc USING (ids_pat) ) ROW;



В списке pgsql-performance по дате отправления:

Предыдущее
От: Streamsoft - Mirek Szajowski
Дата:
Сообщение: Re: Locking concurrency: select for update vs update
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: array size exceeds the maximum allowed (1073741823) when building a json