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

[Yuri Budilov <yuri.budilov@xxxxxxxxxxx>]

the plot thickens!

I have more information.

The out-of-memory error happens if I also retrieve another JSON Column like so:

-- fails

CREATE TABLE csnbi_stg.junk4
AS
SELECT
       json_rmq->>'totalSize' as totalSize, -- this plus array below causes out of memory error
       json_array_elements(json_rmq -> 'orders'::text) AS orders
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
 WHERE rmq_exchange_name = 'Staging.Salesforce.Order'
   AND rmq_message_id = 'd1200de2-30b0-4599-bb17-64405f45ca19';

if I *only* retrieve the JSON array by itself then it works:

CREATE TABLE csnbi_stg.junk5
AS
SELECT
      --  json_rmq->>'totalSize' as totalSize, -- take this OUT and below works
       json_array_elements(json_rmq -> 'orders'::text) AS orders
  FROM csnbi_stg.stg_rmq_prod_test_json_raw_jmcn stg_rmq_json_raw
 WHERE rmq_exchange_name = 'Staging.Salesforce.Order'
   AND rmq_message_id = 'd1200de2-30b0-4599-bb17-64405f45ca19';

THANK YOU

---

From: Yuri Budilov <yuri.budilov@xxxxxxxxxxx>
Sent: Monday, 4 December 2017 11:14 AM
To: rob stone; pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: 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@xxxxxxxxx>
Sent: Monday, 4 December 2017 11:01 AM
To: Yuri Budilov; John R Pierce; pgsql-general@xxxxxxxxxxxxxxxxxxxx
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