From 2441c4c29fcf6acb98c72b91c036dbb0a0b65957 Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Tue, 8 Apr 2014 15:36:52 -0400 Subject: [PATCH] LP#1271661: Use unlogged tables to avoid lock churn Attempting to load a large number of bib records into a Vandelay import queue can stall and fail, with no records getting staged into the import queue. It is suspected that this is because of the locks required to maintain temp tables, which we create and destroy for each record we look at. Instead, let's just use unlogged tables and empty them explicitly after each record. Autovacuum should protect us from bloat growth. Signed-off-by: Mike Rylander --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 21 +++++++++++---------- 1 file changed, 11 insertions(+), 10 deletions(-) diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index c7eb3a53a1..8bc09beee1 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -496,6 +496,10 @@ $_$ LANGUAGE SQL; -- vandelay.match_bib_record() is strictly in service of that function. CREATE TYPE vandelay.match_set_test_result AS (record BIGINT, quality INTEGER); +-- These are always empty from the point of view of a new transaction +CREATE UNLOGGED TABLE vandelay.tmp_qrows ( q INTEGER ); +CREATE UNLOGGED TABLE vandelay.tmp_jrows ( j TEXT ); + CREATE OR REPLACE FUNCTION vandelay.match_set_test_marcxml( match_set_id INTEGER, record_xml TEXT, bucket_id INTEGER ) RETURNS SETOF vandelay.match_set_test_result AS $$ @@ -512,9 +516,6 @@ BEGIN tags_rstore := vandelay.flatten_marc_hstore(record_xml); svf_rstore := vandelay.extract_rec_attrs(record_xml); - CREATE TEMPORARY TABLE _vandelay_tmp_qrows (q INTEGER); - CREATE TEMPORARY TABLE _vandelay_tmp_jrows (j TEXT); - -- generate the where clause and return that directly (into wq), and as -- a side-effect, populate the _vandelay_tmp_[qj]rows tables. wq := vandelay.get_expr_from_match_set(match_set_id, tags_rstore); @@ -524,7 +525,7 @@ BEGIN -- qrows table is for the quality bits we add to the SELECT clause SELECT STRING_AGG( 'COALESCE(n' || q::TEXT || '.quality, 0)', ' + ' - ) INTO coal FROM _vandelay_tmp_qrows; + ) INTO coal FROM vandelay.tmp_qrows; -- our query string so far is the SELECT clause and the inital FROM. -- no JOINs yet nor the WHERE clause @@ -532,7 +533,7 @@ BEGIN -- jrows table is for the joins we must make (and the real text conditions) SELECT STRING_AGG(j, E'\n') INTO joins - FROM _vandelay_tmp_jrows; + FROM vandelay.tmp_jrows; -- add those joins and the where clause to our query. query_ := query_ || joins || E'\n'; @@ -551,8 +552,8 @@ BEGIN RETURN NEXT rec; END LOOP; - DROP TABLE _vandelay_tmp_qrows; - DROP TABLE _vandelay_tmp_jrows; + DELETE FROM vandelay.tmp_qrows; + DELETE FROM vandelay.tmp_jrows; RETURN; END; $$ LANGUAGE PLPGSQL; @@ -645,7 +646,7 @@ CREATE OR REPLACE FUNCTION vandelay._get_expr_push_qrow( ) RETURNS VOID AS $$ DECLARE BEGIN - INSERT INTO _vandelay_tmp_qrows (q) VALUES (node.id); + INSERT INTO vandelay.tmp_qrows (q) VALUES (node.id); END; $$ LANGUAGE PLPGSQL; @@ -666,7 +667,7 @@ BEGIN -- remember $1 is tags_rstore, and $2 is svf_rstore caseless := FALSE; - SELECT COUNT(*) INTO jrow_count FROM _vandelay_tmp_jrows; + SELECT COUNT(*) INTO jrow_count FROM vandelay.tmp_jrows; IF jrow_count > 0 THEN my_using := ' USING (record)'; my_join := 'FULL OUTER JOIN'; @@ -731,7 +732,7 @@ BEGIN jrow := jrow || ' ' || my_alias || my_using || E'\n'; END IF; - INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); + INSERT INTO vandelay.tmp_jrows (j) VALUES (jrow); END; $$ LANGUAGE PLPGSQL; -- 2.11.0