4

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.

I found related questions here and here but neither was applicable enough for it to answer my question.

Thanks for reading

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.