Re: BUG #14843: CREATE TABLE churns through all memory, crashes db

Hi!

On 2017-10-05 23:03:21 +0000, ropeladder(at)gmail(dot)com wrote:
> (first bug report here so please let me know if this can be improved)

Thanks for reporting. This is a pretty good start.

> 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"}

Could you either try to form a reproducible demonstration of the problem
out of this, or get a memory context dump? If you disable the kernel's
overcommit heuristics, your computer won't crash on OOM, instead
postgres will get an error, and log a dump that shows where memory is
used.

I use
$ cat /etc/sysctl.d/60-oom.conf
vm.overcommit_memory = 2
vm.overcommit_ratio = 50

for that purpose (that means only swap + 50% of memory can be handed out
to applications, if you don't have swap you might want to use 80 or
such).

Greetings,

Andres Freund