Out of memory - Mailing list pgsql-novice

Hi,

I am new to Postgres and I do not know much about databases in general.

I have been trying to execute a plpgsql function that creates several tables, joins and unions them before applying a
fewfunctions (which also create and join tables) before inserting a few rows into a permanent table and exiting. This
functionis called for every row of a table which is not small (>50000 rows). 

I am getting this error consistently after running for around 400s:

ERROR: out of shared memory
SQL state: 53200
Hint: You might need to increase max_locks_per_transaction.
Context: SQL statement "CREATE TABLE tmp_cands AS SELECT d.premise_id AS uid, d.* FROM tmp_cands_1 AS c INNER JOIN
nlpg.match_dataAS d ON c.pid = d.premise_id" 
PL/pgSQL function "get_match" line 61 at SQL statement

I have increased the max_locks_per_transaction to 10000 to no avail.

Please can anyone tell me how I can find out more about this error and/or what I might do to solve it. I was thinking
thatit might be related to the creation of tables that may not be getting dropped until the query exited. In which case
Iwas thinking of trying to use EXECUTE ''DROP/CREATE/ ....'' at the cost of no query planning benefits, but this is
mostlya guess.. 

Any help/suggestions much appreciated. I have pasted the offending portions of the code below for luck.

Cheers,
Tom

------------------------------

-- Inserts matches for input address into match table and returns number of matches found
DROP  FUNCTION IF EXISTS nlpg.get_match(ipt struct_address) ;
CREATE OR REPLACE FUNCTION nlpg.get_match(ipt struct_address) RETURNS INTEGER AS '
DECLARE
    ipt ALIAS FOR $1;
    num_matches integer;
    rc integer;
BEGIN
    -- Get candidate sets
    DROP TABLE IF EXISTS tmp_cands_n;
    CREATE TABLE tmp_cands_n AS SELECT nlpg.get_namenum_cands(($1).name) AS pid;
    DROP TABLE IF EXISTS tmp_cands_s;
    CREATE TABLE tmp_cands_s AS SELECT nlpg.get_street_100_cands(($1).street) AS pid;
    DROP TABLE IF EXISTS tmp_cands_p;
    CREATE TABLE tmp_cands_p AS SELECT nlpg.get_pc_cands(($1).pc) AS pid;

    -- Get full intersection...
    DROP TABLE IF EXISTS tmp_cands_pn;
    DROP TABLE IF EXISTS tmp_cands_1;
    CREATE TABLE tmp_cands_pn AS SELECT DISTINCT n.*
        FROM tmp_cands_n AS n INNER JOIN tmp_cands_p AS p ON p.pid = n.pid;
    CREATE TABLE tmp_cands_1 AS SELECT DISTINCT c.*
        FROM tmp_cands_pn AS c INNER JOIN tmp_cands_s AS s ON c.pid = s.pid;

    -- TODO: Other intersections, use more complex lookup tables, etc.
    GET DIAGNOSTICS rc = ROW_COUNT;
    --RAISE NOTICE ''1st level intersection - rc = %'',rc;

        IF rc = 0 THEN
        DROP TABLE IF EXISTS tmp_cands_ps;
        CREATE TABLE tmp_cands_ps AS
            SELECT s.* FROM tmp_cands_s AS s INNER JOIN tmp_cands_p AS p ON s.pid = p.pid;
        DROP TABLE IF EXISTS tmp_cands_ns;
        CREATE TABLE tmp_cands_ns AS
            SELECT n.* FROM tmp_cands_n AS n INNER JOIN tmp_cands_s AS s ON n.pid = s.pid;
        DROP TABLE IF EXISTS tmp_cands_1;
        CREATE TABLE tmp_cands_1 AS
            SELECT * FROM tmp_cands_pn
            UNION
            SELECT * FROM tmp_cands_ps
            UNION
            SELECT * FROM tmp_cands_ns;

        -- 2nd Level Intersection of candidates

        GET DIAGNOSTICS rc = ROW_COUNT;
        --RAISE NOTICE ''2nd level intersection - rc = %'',rc;
    END IF;

        IF rc = 0 THEN
        -- 3rd Level Intersection of candidates
        DROP TABLE IF EXISTS tmp_cands_1;
        CREATE TABLE tmp_cands_1 AS
            SELECT * FROM tmp_cands_n
            UNION
            SELECT * FROM tmp_cands_s
            UNION
            SELECT * FROM tmp_cands_p;
        --RAISE NOTICE ''3rd level intersection - rc = %'',rc;
    END IF;

    -- Expand candidates to include match data for distance metric evaluation
    DROP TABLE IF EXISTS tmp_cands;
    CREATE TABLE tmp_cands AS SELECT d.premise_id AS uid, d.*
        FROM tmp_cands_1 AS c INNER JOIN nlpg.match_data AS d ON c.pid = d.premise_id;

    -- Evaluate distances
    DROP TABLE IF EXISTS ranked_cands;
    CREATE TABLE ranked_cands AS
        SELECT
            dist(ipt,(c.uid, c.name, c.street, c.town, c.pc)::struct_address) AS dist,
            pc_dist(ipt.pc,c.pc) AS pc_dist,
            town_dist(ipt.town,c.town) AS town_dist,
            street_dist(ipt.street,c.street) AS street_dist,
            name_dist(ipt.name,c.name) AS name_dist,
            num_dist(ipt.name,c.name) AS num_dist,
            *
    FROM tmp_cands AS c ORDER BY dist ASC;

    -- Select best cands
    IF NOT tableExists(''matches'',''nlpg_matches'') THEN
        CREATE TABLE matches.nlpg_matches AS SELECT ipt AS input, r.* FROM ranked_cands AS r WHERE r.dist = (SELECT
min(dist)FROM ranked_cands); 
    ELSE
        INSERT INTO matches.nlpg_matches SELECT ipt AS input, r.* FROM ranked_cands AS r WHERE r.dist = (SELECT
min(dist)FROM ranked_cands); 
    END IF;

    GET DIAGNOSTICS num_matches = ROW_COUNT;
    RETURN num_matches;
END;
' LANGUAGE 'plpgsql';

-- Get test input
DROP TABLE IF EXISTS tmp_pft_input;
CREATE TABLE tmp_pft_input AS
    SELECT (i.uid,i.name,i.street,i.town,i.pc)::struct_address AS inp  FROM
    attr.non_resi_all_match_data AS i
    ORDER BY RANDOM()
    LIMIT 10000;

SELECT nlpg.get_match(j.inp), j.inp AS input FROM tmp_pft_input AS j;

-- 12,781ms for 10 input
-- 127,937ms for 100 input
-- 431, 193ms for 10000 input but it to fail due to
--     ERROR: out of shared memory
--     SQL state: 53200
--     Hint: You might need to increase max_locks_per_transaction.
--     Context: SQL statement "CREATE TABLE tmp_cands AS SELECT d.premise_id AS uid, d.* FROM tmp_cands_1 AS c INNER
JOINnlpg.match_data AS d ON c.pid = d.premise_id" 
--     PL/pgSQL function "get_match" line 61 at SQL statement