Execute multiple queries as one dblink transaction

Question

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.

Answer 1

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?

Answer 2

If all else fails, chain multiple SQL commands into one using one or more CTEs:

WITH upd AS (
  UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436
  )
INSERT INTO table3(field4) 
VALUES (5)') AS result;

Or INSERT first, does not matter. Typically it wouldn't make sense to chain two unrelated commands this way, but it's a clean application of the feature. You can chain as many commands as you like this way. You just cannot have two commands writing to the same row. You can even have a final SELECT return related or unrelated values. Unlike SELECT in CTEs, all data-modifying CTEs are always executed to completion. The manual:

Data-modifying statements in WITH are executed exactly once, and always to completion, independently of whether the primary query reads all (or indeed any) of their output. Notice that this is different from the rule for SELECT in WITH: as stated in the previous section, execution of a SELECT is carried only as far as the primary query demands its output.

Related:

• PostgreSQL: using foreign keys, delete parent if it's not referenced by any other child

---

Another option would be to create a function (LANGUAGE sql or LANGUAGE plpgsql - but any language should do) on the target server to encapsulate any number of commands in a single transaction:

CREATE OR REPLACE FUNCTION f_wrapper()
  RETURNS void AS
$func$
   UPDATE table1 SET field2 = field2 + 3.0 WHERE field1 = 16436;
   INSERT INTO table3(field4) VALUES (5);
$func$ LANGUAGE sql;

Then:

SELECT dblink_exec('hostaddr=xxx.xx.xxx.xxx port=5432 dbname=bdname user=myuser password=mypass connect_timeout=2',
'SELECT f_wrapper()') AS result;

You can create (and drop) a function on the fly or persist one that takes parameters for values.