MIME-Version: 1.0
In-Reply-To: <717.1465365859@sss.pgh.pa.us>
References: 
 <CA+ssMORBYVFxedSXVVxL5VPeo=9AbYRQ-zbpuKJm6vEjmJ1CFA@mail.gmail.com>
 <CAKFQuwbjwyaaFUOEzxoYCBDm25ujOayCniwiWimPML_A_VaGYA@mail.gmail.com>
 <CA+ssMOT04Wg+OGfSc5AUk8nVQOxkVS6=CD-h7bH-pAH-ix50cw@mail.gmail.com>
 <CAKFQuwZEkX6o2mv2Ekqp8DpAs9F0bMsBnvQxzJGUr+SS6t2yTg@mail.gmail.com>
 <CA+ssMOSdHUTMWqswZ4njAgtf4vpAkuZ8duH7znB0G5pEswa38w@mail.gmail.com>
 <WM!d318efe352c6e124baed8053dba915699725885de80c4fdd70cff903faba6afc6e6233ba053fb428df2d56162a988029!@mailstronghold-1.zmailcloud.com>
 <5756C618.1070802@agliodbs.com>
 <CAB7nPqRdN=7xU14Q=2=fho_apR0TEoKtWyHZip0HSMkYsOUp-Q@mail.gmail.com>
 <717.1465365859@sss.pgh.pa.us>
From: Merlin Moncure <mmoncure@gmail.com>
Date: Thu, 9 Jun 2016 08:31:23 -0500
Message-ID: 
 <CAHyXU0yNbarj8ibwAzkKjWmF=M0x2HFiK0BSc84ZwLiaLyMGAg@mail.gmail.com>
Subject: Re: array size exceeds the maximum allowed (1073741823)
 when building a json
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paquier <michael.paquier@gmail.com>,
 Josh Berkus <josh@agliodbs.com>,
	Nicolas Paris <niparisco@gmail.com>,
 "David G. Johnston" <david.g.johnston@gmail.com>,
	pgsql-performance <pgsql-performance@postgresql.org>
Content-Type: text/plain; charset=UTF-8
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Wed, Jun 8, 2016 at 1:04 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Michael Paquier <michael.paquier@gmail.com> writes:
>> On Tue, Jun 7, 2016 at 10:03 PM, Josh Berkus <josh@agliodbs.com> wrote:
>>> On 06/07/2016 08:42 AM, Nicolas Paris wrote:
>>>> Will this 1GO restriction is supposed to increase in a near future ?
>
>>> Not planned, no.  Thing is, that's the limit for a field in general, not
>>> just JSON; changing it would be a fairly large patch.  It's desireable,
>>> but AFAIK nobody is working on it.
>
>> And there are other things to consider on top of that, like the
>> maximum allocation size for palloc, the maximum query string size,
>> COPY, etc. This is no small project, and the potential side-effects
>> should not be underestimated.
>
> It's also fair to doubt that client-side code would "just work" with
> no functionality or performance problems for such large values.
>
> I await with interest the OP's results on other JSON processors that
> have no issues with GB-sized JSON strings.

Yup.  Most json libraries and tools are going to be disgusting memory
hogs or have exponential behaviors especially when you consider you
are doing the transformation as well.  Just prettifying json documents
over 1GB can be a real challenge.

Fortunately the workaround here is pretty easy.  Keep your query
exactly as is but remove the final aggregation step so that it returns
a set. Next, make a small application that runs this query and does
the array bits around each row (basically prepending the final result
with [ appending the final result with ] and putting , between rows).
It's essential that you use a client library that does not buffer the
entire result in memory before emitting results.   This can be done in
psql (FETCH mode), java, libpq (single row mode), etc.   I suspect
node.js pg module can do this as well, and there certainty will be
others.

The basic objective is you want the rows to be streamed out of the
database without being buffered.  If you do that, you should be able
to stream arbitrarily large datasets out of the database to a json
document assuming the server can produce the query.

merlin


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance