Re: JSON out of memory error on PostgreSQL 9.6.x

hello good people

it is *not* JSONB, just plain JSON

the version 9.6.3 and running inside AWS RDS PostgreSQL (DBaaS)

the machine size is just 1 GB RAM and 1 CPU, is it a called "micro" size AWS RDS instance, we use it for DEV

we have also reproduced it on 2 CPU 8 GB RAM instance, FWIW.

It takes well under 1 min elapsed time to fail.

best regards and many thanks for trying to help me

________________________________
From: rob stone <floriparob(at)gmail(dot)com>
Sent: Monday, 4 December 2017 11:01 AM
To: Yuri Budilov; John R Pierce; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: JSON out of memory error on PostgreSQL 9.6.x

On Sun, 2017-12-03 at 23:18 +0000, Yuri Budilov wrote:
> Posted on Stack Overflow, sadly no replies, so trying here....
>
> CREATE TABLE X AS
> SELECT json_array_elements(json_rmq -> 'orders'::text) AS order
> FROM table_name
> WHERE blah;
> I get out of memory error.
>
> Is there anything I can do to unpack the above?
>
> The JSON column is about ~5 MB and it has about ~150,000 array
> row elements in 'orders' above.
>
> I tried work_mem values up to ~250MB and it did not help, the query
> takes about same time to fail.
>
> I guess this parameter does not help JSON processing.
>
> If there another parameter I can try? Something else?
>
> I don't have control of the size of the JSON payload, it arrives, we
> store it in a JSON column and then we need to crack it open.
>
> Many thanks!
>

Hello,

It would help if you advised:-
(a) version of PostgreSql being used.
(b) is column json_rmq defined as json or jsonb?
(c) OS.

Cheers,
Rob