[BUGS] BUG #14843: CREATE TABLE churns through all memory, crashes db

The following bug has been logged on the website:

Bug reference:      14843
Logged by:           Ben
Email address:      ropeladder@gmail.com
PostgreSQL version: 10rc1
Operating system:   Linux Mint 18.2
Description:

(first bug report here so please let me know if this can be improved)

Congrats on the new release!

I just installed v10 and am trying to re-run an ETL I had scripted. I'm
unable to run a CREATE TABLE command that works fine in 9.6 (it took 83
seconds). When I try to run it in v10 it quickly maxes out my RAM usage and
then swallows up all my virtual memory before finally  crashing
PostgreSQL.

The command takes a table with one jsonb document and creates a new table
with 3 rows: one jsonb, one text, and one timestamp. The initial table has
2.6M rows, the new table should have 4.9M rows (because of a
jsonb_array_elements() expansion). The actual query is below:

CREATE TABLE new_table as (
SELECT   jsonb_array_elements(doc->'text'->0->'hasauthor') doc,   doc->'text'->0->'$'->>'id' rid,
regexp_replace(doc->>'mtime','[TZ]','')::timestamp mtime
 
FROM source_table
WHERE (doc#>'{"text",0,"hasauthor"}') IS NOT NULL)

The 2.6M jsonb documents in the source table are structured similar to
this:

{"$": {"xmlns": "http://amf.openlib.org", "xmlns:xsi":
"http://www.w3.org/2001/XMLSchema-instance", "xmlns:repec":
"http://repec.openlib.org", "xsi:schemaLocation": "http://amf.openlib.org
http://amf.openlib.org/2001/amf.xsd"}, "text": [{"$": {"id":
"RePEc:zwi:ipaper:56"}, "date": [{"$": {"event": "created"}, "_":
"2009-02"}], "file": [{"url":
["http://www.url.edu/documents/issuepapers/ip56.pdf"], "format":
["application/pdf"]}], "type": ["preprint"], "title": ["The Need for New
Milk Pricing Policies"], "status": ["Number 56 18 pages"], "abstract":
["Long text string."], "ispartof": [{"collection": [{"$": {"ref":
"RePEc:zwi:ipaper"}}]}], "keywords": ["milk, dairy, pricing, price gouging,
New England"], "hasauthor": [{"person": [{"name": ["Adam Lastname"],
"email": ["email@gmail.com"], "ispartof": [{"organization": [{"name":
["University of Connecticut"]}]}]}]}, {"person": [{"name": ["Ronald
Lastname"], "email": ["email@email.com"], "ispartof": [{"organization":
[{"name": ["University of Connecticut"]}]}]}]}]}], "mtime":
"2014-05-31T03:59:33.000Z"}


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