Populating a database in PostgreSQL

Question

The following link on the PostgreSQL documentation manual http://www.postgresql.org/docs/8.3/interactive/populate.html says that to disable autocommit in postgreSQL you can simply place all insert statements within BEGIN; and COMMIT;

However I have difficulty in capturing any exceptions that may happen between the BEGIN; COMMIT; and if an error occurs (like trying to insert a duplicate PK) I have no way to explicitly call the ROLLBACK or COMMIT commands. Although all insert statements are automatically rolled back, PostgreSQL still expects an explicit call to either the COMMIT or ROLLBACK commands before it can consider the transaction to be terminated. Otherwise, the script has to wait for the transaction to time out and any statements executed thereafter will raise an error.

In a stored procedure you can use the EXCEPTION clause to do this but the same does not apply in my circumstance of performing bulk inserts. I have tried it and the exception block did not work for me because the next statement/s executed after the error takes place fails to execute with the error:

ERROR:  current transaction is aborted, commands ignored until end of transaction block

The transaction remains open as it has not been explicitly finalised with a call to COMMIT or ROLLBACK;

Here is a sample of the code I used to test this:

BEGIN;
  SET search_path TO testing;
  INSERT INTO friends (id, name) VALUES (1, 'asd');
  INSERT INTO friends (id, name) VALUES (2, 'abcd');
    INSERT INTO friends (id, nsame) VALUES (2, 'abcd'); /*note the deliberate mistake in  attribute name and also the deliberately repeated pk value number 2*/
EXCEPTION /* this part does not work for me */
    WHEN OTHERS THEN
        ROLLBACK;   
COMMIT;

When using such technique do I really have to guarantee that all statements will succeed? Why is this so? Isn't there a way to trap errors and explicitly call a rollback?

Thank you

Answer 1

if you do it between begin and commit then everything is automatically rolled back in case of an exception. Excerpt from the url you posted: "An additional benefit of doing all insertions in one transaction is that if the insertion of one row were to fail then the insertion of all rows inserted up to that point would be rolled back, so you won't be stuck with partially loaded data."