The following bug has been logged online: Bug reference: 5779 Logged by: Michel Alexandre Salim Email address: michel.sa...@cs.fau.de PostgreSQL version: 9.0.1 Operating system: Ubuntu 10.04 Description: Large INSERT transaction with FOREIGN KEY constraints exhausts available RAM Details:
Explanation: I have a table with close to a billion rows that was initially imported in csv form; as such, a lot of the columns were not normalized. For speed reasons, I created one temporary table for each column that needs to be normalized, and then did an INSERT with JOINs to populate the new table with the normalized data. If foreign key constraints are enabled, this runs out of memory (the machine has 4GB RAM + 6 GB swap and nothing else is running that requires much RAM; 4 GB of the swap was actually added to try and debug this). With vm.overcommit_memory set to the default 0 the OOM killer consistently kills the process handling the query; with it set to the recommended 2 postgresql itself aborts the query when it cannot allocate more RAM. I tried changing the fkey constraints to DEFERRABLE hoping that that would consume less RAM, but the same result occurs. Why should the memory usage pattern be different when integrity checks are done as part of the transaction (even when pushed back to the end), and in a separate transaction? The dataset I'm using is the Eclipse UDC dataset for 2009 -- http://archive.eclipse.org/technology/phoenix/usagedata/ -- and the problem is independently described at this other site, with a simpler query: http://www.jory.info/serendipity/archives/30-PostgreSQL-Out-of-Memory-with-l arge-INSERT.html My own invocations were CREATE TABLE udc (id INTEGER PRIMARY KEY, eclipse_id INTEGER NOT NULL, ac│ ion_type_id INTEGER NOT NULL, target_type_id INTEGER, source_id INTEGER NOT NUL│ , version_id INTEGER NOT NULL, target_id INTEGER, tstamp TIMESTAMPTZ NOT NULL,│ FOREIGN KEY (action_type_id) REFERENCES actions (id) DEFERRABLE, FOREIGN KEY (t│ rget_type_id) REFERENCES class_types (id) DEFERRABLE, FOREIGN KEY (source_id) R│ FERENCES classes (id) DEFERRABLE, FOREIGN KEY (version_id) REFERENCES versions │ id) DEFERRABLE , FOREIGN KEY (target_id) REFERENCES classes (id) DEFERRABLE); │ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "udc_pkey" for t│ ble "udc" │ CREATE TABLE │ udc=# INSERT INTO udc (id, eclipse_id, action_type_id, target_type_id, source_i│ , version_id, target_id, tstamp) SELECT old_udc.id, old_udc.eclipse_id, old_udc│ action_type, newttype.type_id, old_udc.source, newv.v_id, newt.t, old_udc.tstam│ FROM old_udc NATURAL JOIN newttype NATURAL JOIN newv NATURAL JOIN newt; │ ERROR: out of memory │ DETAIL: Failed on request of size 1048576. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
