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 Sent: Monday, 4 December 2017 11:14 AM To: rob stone; pgsql-general@lists.postgresql.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 Sent: Monday, 4 December 2017 11:01 AM To: Yuri Budilov; John R Pierce; pgsql-general@lists.postgresql.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