Continuing a transaction after primary key violation error

by

You can also use SAVEPOINTs in a transaction.

Pythonish pseudocode is illustrate from the application side:

database.execute("BEGIN")
foreach data_row in input_data_dictionary:
    database.execute("SAVEPOINT bulk_savepoint")
    try:
        database.execute("INSERT", table, data_row)
    except:
        database.execute("ROLLBACK TO SAVEPOINT bulk_savepoint")
        log_error(data_row)
        error_count = error_count + 1
    else:
        database.execute("RELEASE SAVEPOINT bulk_savepoint")

if error_count > error_threshold:
    database.execute("ROLLBACK")
else:
    database.execute("COMMIT")

Edit: Here’s an actual example of this in action in psql based on a slight variation of the example in the documentation (SQL statements prefixed by “>”):

> CREATE TABLE table1 (test_field INTEGER NOT NULL PRIMARY KEY);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "table1_pkey" for table "table1"
CREATE TABLE

> BEGIN;
BEGIN
> INSERT INTO table1 VALUES (1);
INSERT 0 1
> SAVEPOINT my_savepoint;
SAVEPOINT
> INSERT INTO table1 VALUES (1);
ERROR:  duplicate key value violates unique constraint "table1_pkey"
> ROLLBACK TO SAVEPOINT my_savepoint;
ROLLBACK
> INSERT INTO table1 VALUES (3);
INSERT 0 1
> COMMIT;
COMMIT
> SELECT * FROM table1;  
 test_field 
------------
          1
          3
(2 rows)

Note that the value 3 was inserted after the error, but still inside the same transaction!

The documentation for SAVEPOINT is at http://www.postgresql.org/docs/8.4/static/sql-savepoint.html.

More Related Contents:

  1. NOT NULL constraint over a set of columns
  2. Refactor a PL/pgSQL function to return the output of various SELECT queries
  3. How to implement a many-to-many relationship in PostgreSQL?
  4. How to update selected rows with values from a CSV file in Postgres?
  5. Optimise PostgreSQL for fast testing
  6. How to delete duplicate rows without unique identifier
  7. Best way to check for “empty or null value”
  8. Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?
  9. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  10. PostgreSQL parameterized Order By / Limit in table function
  11. How can I send email from PostgreSQL trigger?
  12. Correct use of transactions in SQL Server
  13. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  14. How do I alter the position of a column in a PostgreSQL database table?
  15. Query that ignore the spaces
  16. How to write a constraint concerning a max number of rows in postgresql?
  17. Postgres constraint for unique datetime range
  18. sql unique constraint on a 2 columns combination
  19. Use email address as primary key?
  20. Does Postgres support nested or autonomous transactions?
  21. ‘Lost update’ vs ‘Write skew’
  22. SQL query to find record with ID not in another table
  23. How to show row numbers in PostgreSQL query?
  24. Postgres Error: More than one row returned by a subquery used as an expression
  25. How to compare dates in datetime fields in Postgresql?
  26. Should I commit or rollback a read transaction?
  27. PostgreSQL how to create a copy of a database or schema?
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment