From: | ropeladder(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Cc: | ropeladder(at)gmail(dot)com |
Subject: | BUG #14843: CREATE TABLE churns through all memory, crashes db |
Date: | 2017-10-05 23:03:21 |
Message-ID: | 20171005230321.28561.15927@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 14843
Logged by: Ben
Email address: ropeladder(at)gmail(dot)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(at)gmail(dot)com"], "ispartof": [{"organization": [{"name":
["University of Connecticut"]}]}]}]}, {"person": [{"name": ["Ronald
Lastname"], "email": ["email(at)email(dot)com"], "ispartof": [{"organization":
[{"name": ["University of Connecticut"]}]}]}]}]}], "mtime":
"2014-05-31T03:59:33.000Z"}
From | Date | Subject | |
---|---|---|---|
Next Message | Andres Freund | 2017-10-05 23:16:11 | Re: BUG #14843: CREATE TABLE churns through all memory, crashes db |
Previous Message | Andres Freund | 2017-10-05 23:01:05 | Re: pg_logical_slot_peek_changes crashes postgres when called from inside pl/pgsql |