Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1bAFQs-0007PZ-I9 for pgsql-performance@arkaria.postgresql.org; Tue, 07 Jun 2016 11:44:30 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1bAFQs-0002TK-45 for pgsql-performance@arkaria.postgresql.org; Tue, 07 Jun 2016 11:44:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1bAFQq-0002SR-Qd for pgsql-performance@postgresql.org; Tue, 07 Jun 2016 11:44:28 +0000 Received: from mail-wm0-x22a.google.com ([2a00:1450:400c:c09::22a]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84_2) (envelope-from ) id 1bAFQj-0002aN-DV for pgsql-performance@postgresql.org; Tue, 07 Jun 2016 11:44:27 +0000 Received: by mail-wm0-x22a.google.com with SMTP id v199so15382500wmv.0 for ; Tue, 07 Jun 2016 04:44:21 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:from:date:message-id:subject:to; bh=uk33SpDkTrR1rc+sfeIVek9F1GhOUlUsBZ7WBfP7v+0=; b=a8MWjjpXDko8dDr0vYfCznpM/dSJT7gVW2Jp+ex9we6hzwXtiK1n8O3asPfhm+zB7U SbiCFpWP9EjPpZjrYX3c1xVcW4yeN2rlqmZDvLCXrCMT7Ox3iHYqm+U3W2laIax75W5/ uaFfJBFXp6cg0OXMGa1yYta23yA4vefpiX90vY7odTsY7tlm38eEmPmrxRhv9Tpfam2P pyMqX3lR9uD7w+YuHQD8A+44elJcPJQs16j6c0rSai+D3vUPHwQ07ikXCGD1kXVBKrtG UwH16GJl6R1SRZyHjtNiTpvtU4hzgrpgdFdqXdiLsBdmgWEQrHeASd8ino817/Z4oPEB pGDw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20130820; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=uk33SpDkTrR1rc+sfeIVek9F1GhOUlUsBZ7WBfP7v+0=; b=kqHw7nxY5eZ4+H2IfOhhpd2C/yEgF5BXJD+Ct6G/d4IBbqf+QzKPxpXgJMyy6nCBB8 b9Y9guuTVKSoK3g5VoKeerb41IMgOcvxqivfOkIWsQNvDtgrVpVWsAnKW1a8fI5eebXO qnddQwEcNyybpMajzDQiGN20KzEz0IIRxKKuwETX/npzD9glmM5hG00TGkIQ+0hkfaSx yRPGu3qQz4PyaV7ZFGNZKKYfeW+oZrJdLErfc+51+NyJ0GjOwSLcFjUnpeVoqP7s11O+ JiuV8CYOa0UKttm16pStbz7ZIELh1vQbNpQaWlaUvrIg2HnLuvJ0BlG08Cd6B6K2lB/j wGnw== X-Gm-Message-State: ALyK8tKNeiwJnYqdK+gqeYdCDv73ZjzHPq3W4JYAQK4iuKQCSVtNyXb8z9pQ8OgysVC65sli4sTsu3/3EUDLnQ== X-Received: by 10.28.178.70 with SMTP id b67mr2286231wmf.13.1465299860204; Tue, 07 Jun 2016 04:44:20 -0700 (PDT) MIME-Version: 1.0 Received: by 10.194.133.196 with HTTP; Tue, 7 Jun 2016 04:44:19 -0700 (PDT) From: Nicolas Paris Date: Tue, 7 Jun 2016 13:44:19 +0200 Message-ID: Subject: array size exceeds the maximum allowed (1073741823) when building a json To: pgsql-performance Content-Type: multipart/alternative; boundary=001a11442024a833570534aeb547 X-Pg-Spam-Score: -2.7 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-performance Precedence: bulk Sender: pgsql-performance-owner@postgresql.org --001a11442024a833570534aeb547 Content-Type: text/plain; charset=UTF-8 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; --001a11442024a833570534aeb547 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 
Hello,

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


Thanks by advance,
Informations:
postgresql 9.4
shared_=
buffers =3D 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 >=3D '20=
150101'
          AND dt_deb_nda <=3D '20150401')),
  =
    diag AS
  ( SELECT ids_nda_rum,
           json_agg(diago) AS dia=
go,
           count(1) AS total
   FROM
     (SELECT ids_nda_rum,=

             json_build_object( 'cd_cim', cd_cim, 'lib_cim&=
#39;,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_nd=
a_rum),
      act AS
  ( SELECT ids_nda_rum,
           json_agg(a=
cto) AS acto,
           count(1) AS total
   FROM
     ( SELECT i=
ds_nda_rum,
              json_build_object( 'cd_act',cd_ccam, &=
#39;dt_act',dt_exec) AS acto
      FROM eds.fait_act_tr
      WHE=
RE 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, &#=
39;status',lib_statut_tr, 'dt_maj_rum_ghm',dt_maj_rum_ghm) AS g=
hmo
      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 =3D 'R=
SS'
      ORDER BY dt_maj_rum_ghm) AS ghmo
   GROUP BY ids_nda_ru=
m ),
      lab AS
  (SELECT ids_nda,
          json_agg(lab) AS la=
bo,
          count(1) AS total
   FROM
     (SELECT ids_nda,
 =
            json_build_object( 'valeur_type_tr',valeur_type_tr, =
9;dt_fait', dt_fait, 'unite',unite, 'cd_test_lab',cd_te=
st_lab, 'valeur_sign_tr',valeur_sign_tr, 'valeur_num_tr',va=
leur_num_tr, 'valeur_text_tr',valeur_text_tr, 'valeur_abnormal_=
tr',valeur_abnormal_tr) AS lab
      FROM eds.fait_lab_tr
      W=
HERE 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_buil=
d_object( 'cd_rum',cd_rum, 'dt_deb_rum', dt_deb_rum, 'd=
t_fin_rum', dt_fin_rum, 'diag', json_build_object('total=
9;,diag.total,'diag',diag.diago), 'act', json_build_object(=
'total',act.total,'act',act.acto) ) AS rum
      FROM ed=
s.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 =3D 'RUM' ) AS r=
umo
   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_ru=
m, 'ghm', json_build_object('total',ghm.total,'ghm'=
,ghm.ghmo), 'rum', json_build_object('total',rum.total, =
9;rum',rum.rumo) ) AS rss
      FROM eds.nda_rum_tr
      LEFT JO=
IN ghm USING (ids_nda_rum)
      LEFT JOIN rum USING (ids_nda)
      =
WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
  =
      AND cd_rum =3D 'RSS' ) AS rss
   GROUP BY ids_nda),
   =
   enc AS
  (SELECT 'Encounter' AS "resourceType",
=
          cd_nda AS "identifier",
          duree_hospit AS &q=
uot;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&=
#39;,lab.total, 'lab',lab.labo) AS lab,
          json_build_obj=
ect('total',rss.total, 'rss',rss.rsso) AS rss
   FROM ed=
s.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 "resourc=
eType",
       count(1) AS total,
       array_to_json(array_agg=
(ROW)) AS encounter
FROM
  (SELECT 'Patient' AS "resourc=
eType",
          ipp AS "identifier",
          nom A=
S "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;


=

--001a11442024a833570534aeb547--