Out of memory

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 few functions (which also create and join tables) before inserting a few rows into a permanent table and exiting. This function is 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_data AS 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 that it might be related to the creation of tables that may not be getting dropped until the query exited. In which case I was thinking of trying to use EXECUTE ''DROP/CREATE/ ....'' at the cost of no query planning benefits, but this is mostly a 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 JOIN nlpg.match_data AS d ON c.pid = d.premise_id"
-- PL/pgSQL function "get_match" line 61 at SQL statement