Project

General

Profile

« Previous | Next » 

Revision 3019

Added by Aaron Marcuse-Kubitza over 12 years ago

sql.py: mk_insert_select(): INSERT IGNORE: Use an EXCEPTION block for each individual row because "When an error is caught by an EXCEPTION clause, [...] all changes to persistent database state within the block are rolled back." (http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING). Documented that cursor stays at current position in spite of automatic ROLLBACK on EXCEPTION.

View differences:

lib/sql.py
671 671 
BEGIN
672 672 
    OPEN cur;
673 673
674 
    /* Use extra loop inside EXCEPTION because the EXCEPTION block is expensive:
675 
    "A block containing an EXCEPTION clause is significantly more expensive to
676 
    enter and exit than a block without one."
674 
    /* Need an EXCEPTION block for each individual row because "When an error is
675 
    caught by an EXCEPTION clause, [...] all changes to persistent database
676 
    state within the block are rolled back."
677 
    This is unfortunate because "A block containing an EXCEPTION clause is
678 
    significantly more expensive to enter and exit than a block without one."
677 679 
    (http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html\
678 680 
#PLPGSQL-ERROR-TRAPPING)
679 681 
    */
680 
    <<outer>>
681 682 
    WHILE true LOOP
682 683 
        BEGIN
683 
            WHILE true LOOP
684 
                FETCH FROM cur INTO \
684 
            FETCH FROM cur INTO \
685 685 
'''+(', '.join((c.to_str(db) for c in row)))+''';
686 
                EXIT outer WHEN NOT FOUND;
687
688 
                RETURN QUERY
686 
            EXIT WHEN NOT FOUND;
687
688 
            RETURN QUERY
689 689 
'''+mk_insert(sql_gen.Values(row).to_str(db))+'''
690 690 
;
691 
            END LOOP;
692 691 
        EXCEPTION
693 
            WHEN unique_violation THEN NULL; -- outer loop continues to next row
692 
            /* Note that cursor stays at current position in spite of automatic
693 
            ROLLBACK to before EXCEPTION block: "If a previously opened cursor
694 
            is affected by a FETCH command inside a savepoint that is later
695 
            rolled back, the cursor position remains at the position that FETCH
696 
            left it pointing to (that is, FETCH is not rolled back)."
697 
            (http://www.postgresql.org/docs/8.3/static/sql-rollback-to.html)
698 
            */
699 
            WHEN unique_violation THEN NULL; -- continue to next row
694 700 
        END;
695 701 
    END LOOP;
696 702

Also available in: Unified diff