Execute multiple queries as one dblink transaction

I am working in a Java application where I need to execute these the two queries (as Strings in java) at the same time and rollback the transaction if there where errors.

SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',
'INSERT INTO table3(field4) 
VALUES (5)') AS result;

SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',
'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result;

UPDATE

I created one String with the two queries separated with ; as in the comments suggest

UPDATE

I have tried JDBC atomic transactions as the code in java. I force the second sql fail but even if I am specifying .setAutoCommit(false); the dblink affected the other database with the first query. I tried the same code with NO dblink transactions and the rollback works well. dblink is the problem.

Java UPDATE

public static boolean ejecutarTransaccionDblink(String sql) {
    boolean estado = false;
    try {
        Statement sentencia = conexion.createStatement();
        conexion.setAutoCommit(false);
        if (sql.length() != 0) {
            if (sentencia.execute(sql)) {
                conexion.commit();
                estado = true;
            }
        }
    } catch (SQLException ex) {
        System.out.println(ex.toString());
        try {
            estado = false;
            conexion.rollback();
        } catch (SQLException ex1) {
        }
    } finally {
        try {
            conexion.setAutoCommit(true);
            return estado;
        } catch (SQLException ex) {
            return estado;
        }
    }
}

Thanks for your help.

In order to run the queries in a transaction, you simply need to set the auto-commit feature to false on the connection (remembering to set it back to true when you're done, especially if the connection is retrieved from a connection pool - and therefore reused).

The code is relatively simple:

ResultSet resultado = null;
String statement1 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','INSERT INTO table3(field4) VALUES (5)') AS result";
String statement2 = "SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2','UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436') AS result";
    try {
        // set auto-commit to false, to indicate start of transaction
        conexion.setAutoCommit(false);

        // run whatever queries you want on the connection, in a transaction, e.g. :
        Statement sentencia = conexion.createStatement();
        resultado = sentencia.executeQuery(sql);

        //manually commit the transaction when you're done
        conexion.commit();

        return resultado;
    } catch (SQLException ex) {
        System.out.println("Error Consulta:" + ex);

        // ensure transaction is rolled-back in case of error. (note: you might want to add an NPE check here
        con.rollback();
        return null;
    } finally {
        // close any statements / preparedStatements, etc. Note you MUST do this in the finally block, to ensure your connection won't stay in transaction.
        con.setAutoCommit(true);
    }

Hope that helps

UPDATE

As @a_horse_with_no_name pointed out, dblink_exec connects to a remote db, so the above is not complete, as it only handles transactions in the first db.

I believe the answer should lie with using named connections with dblink_exec where the process involves:

• opening a new connection with dblink_connect
• starting a transaction in new named connection with dblink_exec
• executing query 1 with dblink_exec in previously opened connection
• executing query 2 with dblink_exec in previously opened connection
• committing transaction in previously opened connection

Therefore, the code would look like this:

SELECT dblink_connect('myconn','hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2');
SELECT dblink_exec('myconn','BEGIN');
SELECT dblink_exec('myconn', 'INSERT INTO table3(field4) VALUES (5)');
SELECT dblink_exec('myconn', 'UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436');
SELECT dblink_exec('myconn','COMMIT');

The thing is, this is all untested, so @KazMiller could you please give this a try?