Hi Tom, Wow thank you so much for the hint!
The plpgsql code that is could be to blame is in the below snippet. I had a look and I'm not sure why it might be leaking. Is it because I assign the v_id1 and v_id2 to the return table 'id' record, return it and then assign to v_id1 or v_id2 again from the cursor? CREATE OR REPLACE FUNCTION bde_control.bde_gettabledifferences(p_table1 regclass, p_table2 regclass, p_compare_key name) RETURNS TABLE("action" character, id bigint) AS ... ... FETCH FIRST FROM v_table_cur1 INTO v_id1, v_check1, v_uniq1; FETCH FIRST FROM v_table_cur2 INTO v_id2, v_check2, v_uniq2; WHILE v_id1 IS NOT NULL AND v_id2 IS NOT NULL LOOP IF v_id1 < v_id2 THEN action := 'D'; id := v_id1; RETURN NEXT; FETCH NEXT FROM v_table_cur1 INTO v_id1, v_check1, v_uniq1; CONTINUE; ELSIF v_id2 < v_id1 THEN action := 'I'; id := v_id2; RETURN NEXT; FETCH NEXT FROM v_table_cur2 INTO v_id2, v_check2, v_uniq2; CONTINUE; ELSIF v_uniq1 <> v_uniq2 THEN action := 'X'; id := v_id1; RETURN NEXT; ELSIF v_check1 <> v_check2 THEN action := 'U'; id := v_id1; RETURN NEXT; END IF; FETCH NEXT FROM v_table_cur1 INTO v_id1, v_check1, v_uniq1; FETCH NEXT FROM v_table_cur2 INTO v_id2, v_check2, v_uniq2; END LOOP; WHILE v_id1 IS NOT NULL LOOP action := 'D'; id := v_id1; RETURN NEXT; FETCH NEXT FROM v_table_cur1 INTO v_id1, v_check1, v_uniq1; END LOOP; WHILE v_id2 IS NOT NULL LOOP action := 'I'; id := v_id2; RETURN NEXT; FETCH NEXT FROM v_table_cur2 INTO v_id2, v_check2, v_uniq2; END LOOP; CLOSE v_table_cur1; CLOSE v_table_cur2; RETURN; The full function can be read in full here: https://github.com/linz/linz_bde_uploader/blob/master/sql/bde_control_functions.sql#L3263 The actual query for both cursors in the case of the table that was proabily causing the error looks like this (v_table_cur1 and v_table_cur2 only differ by table referenced): SELECT id AS ID, COALESCE('V|' || CAST(T.estate_description AS TEXT), '|N') || '|V' || CAST(T.guarantee_status AS TEXT) || '|V' || CAST(T.issue_date AS TEXT) || '|V' || CAST(T.land_district AS TEXT) || '|V' || CAST(T.number_owners AS TEXT) || '|V' || CAST(T.part_share AS TEXT) || COALESCE('V|' || CAST(T.shape AS TEXT), '|N') || '|V' || CAST(T.status AS TEXT) || '|V' || CAST(T.title_no AS TEXT) || '|V' || CAST(T.type AS TEXT) AS check_sum, '' AS check_uniq FROM lds.titles AS T ORDER BY id ASC; The definition for the table looks like this: CREATE TABLE titles ( id INTEGER NOT NULL PRIMARY KEY, title_no VARCHAR(20) NOT NULL, status VARCHAR(4) NOT NULL, type TEXT NOT NULL, land_district VARCHAR(100) NOT NULL, issue_date TIMESTAMP NOT NULL, guarantee_status TEXT NOT NULL, estate_description TEXT, number_owners INT8 NOT NULL, part_share BOOLEAN NOT NULL, shape GEOMETRY, ); CREATE INDEX shx_title_shape ON titles USING gist (shape); Thanks, Jeremy ________________________________________ From: Tom Lane [t...@sss.pgh.pa.us] Sent: Wednesday, 13 April 2011 5:44 p.m. To: Jeremy Palmer Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] 9.0 Out of memory Jeremy Palmer <jpal...@linz.govt.nz> writes: > Ok I have attached the map, or least what I think the map is. Yup, that's what I was after. It looks like the main problem is here: > PortalHeapMemory: 16384 total in 4 blocks; 5944 free (0 chunks); 10440 > used > ExecutorState: 122880 total in 4 blocks; 63984 free (8 chunks); 58896 > used > ExprContext: 2622363000 total in 9 blocks; 21080 free (15 chunks); > 2622341920 used You've evidently got a leak during execution of a query that's being run in a "portal", which most likely is a cursor or plpgsql FOR-IN-SELECT query. Unfortunately there's not enough information here to tell which query that is, but maybe you can narrow it down now. I'm guessing that some specific function or operator you're using in that query is leaking memory with successive executions. regards, tom lane ______________________________________________________________________________________________________ This message contains information, which is confidential and may be subject to legal privilege. If you are not the intended recipient, you must not peruse, use, disseminate, distribute or copy this message. If you have received this message in error, please notify us immediately (Phone 0800 665 463 or i...@linz.govt.nz) and destroy the original message. LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ. Thank you. ______________________________________________________________________________________________________ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general