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

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(dot)budilov(at)hotmail(dot)com>
Sent: Monday, 4 December 2017 11:14 AM
To: rob stone; pgsql-general(at)lists(dot)postgresql(dot)org
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(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