I have a database that is comprised of ~10 tables, most of which have many-to-many relationships with each other (and an associated join table). I am inserting of the order 100k XML files into a raw data table. From this table I am using xpath to extract data items into their respective tables for each raw record. Each XML file has unique data which goes into a single row in a "unique" table, with the row id stored in a variable to be called later when filling the join tables.
Currently I am using a trigger function for this, called AFTER INSERT OR UPDATE ON raw_data FOR EACH ROW, of general form:
DECLARE #variables declared here, v_blah format BEGIN BEGIN INSERT INTO unique (fields) VALUES (xpath statements) RETURNING unique_id INTO v_unique_id; END; BEGIN BEGIN INSERT INTO table1 (fields) VALUES (xpath statements) RETURNING table1_id INTO v_table1_id; EXCEPTION WHEN unique_violation THEN v_table1_id := (SELECT table1_id FROM table1 WHERE field = xpath statement from above AND field2 = xpath statement 2 from above AND etc); END; BEGIN INSERT INTO unique_table1_join (unique_id, table1_id) VALUES (v_unique_id, v_table1_id); EXCEPTION WHEN unique_violation THEN CONTINUE; END; END; --...other tables/joins of similar nature to table1 here... FOREACH v_item IN ARRAY (xpath array statement) LOOP v_item_name := xpath statement v_item_prop1 := xpath statement v_item_prop2 := xpath statement v_item_prop3 := xpath statement BEGIN INSERT INTO table2 (fields) VALUES (v_item_name, v_item_prop1, etc) RETURNING table2_id INTO v_table2_id; EXCEPTION WHEN unique_violation THEN v_table2_id := (SELECT table2_id FROM table2 WHERE field = v_item_name AND field2 = v_item_prop1 AND etc); END; BEGIN INSERT INTO unique_table2_join (unique_id, table2_uid) VALUES (v_unique_id, v_table2_id); EXCEPTION WHEN unique_violation THEN CONTINUE; END; END LOOP; --...other tables/joins of similar nature to table2 here... --...creation of temporary table for combination of a couple of xml arrays into --1 logical array and then insertion/join similar to above (not included as too --arduous to sanitise)... END;
I am hoping that people here who are far more knowledgable in the arcane can suggest faster ways to do this kind of insert/shredding operation. I am using Postgres 9.3.
Possible changes I have considered but not found enough information about to understand the relative speed of include:
1) Using NOT EXISTS or NOT IN searches for prevention of duplicate insertion instead of the EXCEPTIONs.
2) Sending results from the array handling sections to temporary tables before comparing the result to the live tables, to minimise the amount of work inside the FOREACH loops.
3) Splitting the trigger function up into multiple functions that each handle one unique-table pair including joins.
Thanks for reading