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

> No, given the info from the memory map I'd have to say that the leakage
> is in the cursor not in what you do in the plpgsql function.  The cursor
> query looks fairly unexciting except for the cast from geometry to text.
> I don't have PostGIS installed here so I can't do any testing, but I
> wonder whether the leak goes away if you remove that part of the query
> (ie, leave the shape out of the "checksum" for testing purposes).
> If so, you probably ought to file the issue as a PostGIS bug.

Ok I removed the geometry column from the cursor query within the function and the session still runs out of memory.
I'mstill seeing the same error message as well: 

    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: 2496819768 total in 9 blocks; 21080 free (15 chunks); 2496798688 used

So I guess it's not likely to be the PostGIS geometry to text cast that is leaking the memory.

One thing that has got me interested now is query that executes directly before (see SQL below). If I remove the
geometrycolumn that is generated using ST_Collect aggregate function, the subsequent function involving the cursor
querycompletes and the transaction also runs to completion. 

Is there any way that ST_Collect could be leaking memory into a context that does not get cleaned up after the query
runs?Or do I have two leaks going on here?! 

Cheers,
Jeremy

CREATE TEMP TABLE tmp_titles AS
    SELECT
        TTL.audit_id AS id,
        TTL.title_no,
        TTL.status,
        TTLT.char_value AS type,
        LOC.name AS land_district,
        TTL.issue_date,
        TTLG.char_value AS guarantee_status,
        string_agg(
            DISTINCT(
                ETTT.char_value || ', ' ||
                ETT.share || COALESCE(', ' || LGD.legal_desc_text, '') ||
                COALESCE(', ' || to_char(ROUND(LGD.total_area, 0), 'FM9G999G999G999G999') || ' m2', '')
            ),
            E'\r\n'
            ORDER BY
                ETTT.char_value || ', ' ||
                ETT.share || COALESCE(', ' || LGD.legal_desc_text, '') ||
                COALESCE(', ' || to_char(ROUND(LGD.total_area, 0), 'FM9G999G999G999G999') || ' m2', '') ASC
        ) AS estate_description,
        string_agg(
            DISTINCT
                CASE PRP.type
                    WHEN 'CORP' THEN PRP.corporate_name
                    WHEN 'PERS' THEN COALESCE(PRP.prime_other_names || ' ', '') || PRP.prime_surname
                END,
            ', '
            ORDER BY
                CASE PRP.type
                    WHEN 'CORP' THEN PRP.corporate_name
                    WHEN 'PERS' THEN COALESCE(PRP.prime_other_names || ' ', '') || PRP.prime_surname
                END ASC
        ) AS owners,
        count(
            DISTINCT
                CASE PRP.type
                    WHEN 'CORP' THEN PRP.corporate_name
                    WHEN 'PERS' THEN COALESCE(PRP.prime_other_names || ' ', '') || PRP.prime_surname
                END
        ) AS number_owners,
        TPA.title_no IS NOT NULL AS part_share,
        -- With Postgis 1.5.2 the ST_Collect aggregate returns a truncated
        -- collection when a null value is found. To fix this the shapes
        -- are order so all null shapes row are at the end of input list.
        ST_Multi(ST_Collect(PAR.shape ORDER BY PAR.shape ASC)) AS shape
    FROM
        crs_title TTL
        LEFT JOIN crs_title_estate ETT ON TTL.title_no = ETT.ttl_title_no AND ETT.status = 'REGD'
        LEFT JOIN crs_estate_share ETS ON ETT.id = ETS.ett_id AND ETT.status = 'REGD'
        LEFT JOIN crs_proprietor PRP ON ETS.id = PRP.ets_id AND PRP.status = 'REGD'
        LEFT JOIN crs_legal_desc LGD ON ETT.lgd_id = LGD.id AND LGD.type = 'ETT' AND LGD.status = 'REGD'
        LEFT JOIN crs_legal_desc_prl LGP ON LGD.id = LGP.lgd_id
        LEFT JOIN (
            SELECT
                title_no
            FROM
                tmp_parcel_titles
            GROUP BY
                title_no
            HAVING
                count(*) > 1
        ) TPA ON TTL.title_no = TPA.title_no
        LEFT JOIN (
            SELECT
                id,
                (ST_Dump(shape)).geom AS shape
            FROM
                crs_parcel
            WHERE
                status = 'CURR' AND
                ST_GeometryType(shape) IN ('ST_MultiPolygon', 'ST_Polygon')
        ) PAR ON LGP.par_id = PAR.id
        JOIN crs_locality LOC ON TTL.ldt_loc_id = LOC.id
        JOIN crs_sys_code TTLG ON TTL.guarantee_status = TTLG.code AND TTLG.scg_code = 'TTLG'
        JOIN crs_sys_code TTLT ON TTL.type = TTLT.code AND TTLT.scg_code = 'TTLT'
        LEFT JOIN crs_sys_code ETTT ON ETT.type = ETTT.code AND ETTT.scg_code = 'ETTT'
    WHERE
        TTL.status IN ('LIVE', 'PRTC') AND
        TTL.title_no NOT IN (SELECT title_no FROM tmp_excluded_titles)
    GROUP BY
        TTL.audit_id,
        TTL.title_no,
        TTL.status,
        TTLT.char_value,
        LOC.name,
        TTL.issue_date,
        TTLG.char_value,
        TPA.title_no;
______________________________________________________________________________________________________

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.
______________________________________________________________________________________________________