Re: 9.0 Out of memory - Mailing list pgsql-general

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
orv_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_cur1and 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 [tgl@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 <JPalmer@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 info@linz.govt.nz) and
destroythe original message. 
LINZ accepts no responsibility for changes to this email, or for any attachments, after its transmission from LINZ.

Thank you.
______________________________________________________________________________________________________