title: Populating a database in PostgreSQL
body: <p>The following link on the PostgreSQL documentation manual <a href="http://www.postgresql.org/docs/8.3/interactive/populate.html" rel="nofollow">http://www.postgresql.org/docs/8.3/interactive/populate.html</a> says that to disable autocommit in postgreSQL you can simply place all insert statements within BEGIN; and COMMIT;</p>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>ERROR:  current transaction is aborted, commands ignored until end of transaction block
  </code></pre>
  
  <p>The transaction remains open as it has not been explicitly finalised with a call to COMMIT or ROLLBACK;</p>
  
  <p>Here is a sample of the code I used to test this:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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?</p>
  
  <p>Thank you</p>