Re: Cursors removed with commit

To be more explicit, here is my example :
  private void displayMem() {
    Runtime rt = Runtime.getRuntime();
    long alloue = rt.totalMemory();
    long libre = rt.freeMemory();
    System.out.println("Free mem = " + libre + " / allocated = " + alloue);
  }

  public void testJdbcSRS() throws Exception {
    String driver = "org.postgresql.Driver";
    Class.forName(driver);

    String acces = "jdbc:postgresql://<myserver>/<mydb>";
    Connection con = java.sql.DriverManager.getConnection(acces, "<account>", "<pass>");
    con.setAutoCommit(false);

    Statement st = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);//,ResultSet.HOLD_CURSORS_OVER_COMMIT);
    st.setFetchSize(10);
    displayMem();
    ResultSet rs = st.executeQuery("SELECT * FROM doc_index");
    for (int i = 0 ; i < 100; i++) {
      rs.next();
    }
    displayMem();
//    Statement st2 = con.createStatement();
//    st2.execute("DELETE FROM doc_index WHERE id=100");
//    st2.execute("INSERT INTO doc_index(id,version) VALUES (100,1)");
//    con.commit();
    for (int i = 0 ; i < 100; i++) {
      rs.next();
    }
    displayMem();

    rs.close();
    st.close();
    con.close();
  }


First run : OK

Remove the comments on lines on Statement st2 = ... to con.commit()
 ==> cursor error
  (in that case the same table is updated, but i've tried on another table, and it's the same behavior)

Try to HOLD_CURSORS_OVER_COMMIT : it works, BUT the fetch is not used anymore ; the whole table is loaded in the memory (and in my case : OutOfMemory)


Regards,
Christophe

----- Mail original -----
De: "Christophe Canovas" <cc.ais40@wanadoo.fr>
À: pgsql-jdbc@postgresql.org
Envoyé: Mercredi 20 Août 2014 15:39:12
Objet: [JDBC] Cursors removed with commit

Hello,

I have a problem with my huge database ; using cursors (so using a transaction with noautocommit, statement with type_forward_only) make cursors removed if I update and save (commit) database values in the same transaction during the fetch loop.

Error message is : portal "C_03" doesn't exist

All is fine if the commit is done in a different transaction.

In more details, in the statement creation :
  con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY, ResultSet.HOLD_CURSORS_OVER_COMMIT);
the last parameter seems to have no effect ... ?


Best Regards,
Christophe


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc