Continue Postgres transaction with exceptions in Java

Question

The insert method below insert links on a table in the database (PostgreSQL), but if a error occurs, the rest of transaction is affected and dont works. The exception is because the field URL is unique.

It's possible in PostgreSQL continue the transaction even with the exceptions?

for (int i = 0, n = page.getLinks().length; i < n; i++) {
     linkBD.insert(page.getLink(i), idPage);
}            

public boolean insert(Link link, int idPage) {
    try {
        String sql = "INSERT INTO link (id,idPage,url,linkText,"
        + "visited,broken) VALUES(nextval('idLinkSqc'),?,?,?,?,?)";

        PreparedStatement pstm = Conn.conn.prepareStatement(sql);

        pstm.setInt(1, idPage);
        pstm.setString(2, link.getUrl());
        pstm.setString(3, link.getLinkText());
        pstm.setBoolean(4, false);
        pstm.setBoolean(5, false);

        pstm.execute();            
        Conn.commit();
        pstm.close();            
        return true;
    } catch (Exception e) {
        System.out.println("Erro inserindo link no banco de dados: " + e.getMessage());
        System.out.println("Erro no link: "+link.getUrl());
        return false;
    }
}

The error message in portuguese: transação atual foi interrompida, comandos ignorados até o fim do bloco de transação

The translation of Google Translate, I think is right: current transaction is aborted, commands ignored until end of transaction block

Answer 1

It is possible to continue if the failure was inside a SAVEPOINT. Here's an example in psql:

# create temporary table foo (i int primary key);
CREATE TABLE

Begin a transaction and insert a row:

# begin;
BEGIN
# insert into foo values(1);
INSERT 0 1

Start a savepoint, the insert the same row twice. This will cause an error:

# savepoint bar;
SAVEPOINT
# insert into foo values(2);
INSERT 0 1
# insert into foo values(2);
ERROR:  duplicate key value violates unique constraint "foo_pkey"

Roll back to the savepoint, then insert another row.

# rollback to savepoint bar;
ROLLBACK
# insert into foo values(3);
INSERT 0 1

Commit and see what's there:

# commit;
COMMIT
# select * from foo;
 i 
---
 1
 3
(2 rows)