Re: Cursors removed with commit

Christophe,

Sorry, no possibly monday

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 3 September 2014 04:36, Christophe Canovas <cc(dot)ais40(at)wanadoo(dot)fr> wrote:

> Hi Dave,
>
> Did you had some time to have a look on my problem ? Or not yet ?
>
> Best Regards,
> Christophe
>
>
> ----- Mail original -----
> De: "Dave Cramer" <pg(at)fastcrypt(dot)com>
> À: "Christophe Canovas" <cc(dot)ais40(at)wanadoo(dot)fr>
> Cc: "List" <pgsql-jdbc(at)postgresql(dot)org>
> Envoyé: Jeudi 21 Août 2014 16:08:59
> Objet: Re: [JDBC] Cursors removed with commit
>
>
> Christophe,
>
>
> This is unlikely to be a trivial fix. I haven't looked at the code yet,
> but I expect it may not be straight forward.
>
>
> Dave
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On 21 August 2014 08:10, Christophe Canovas < cc(dot)ais40(at)wanadoo(dot)fr > wrote:
>
>
>
> Cursors generally only exist inside a transaction so the first problem
> makes sense.
> ==> yes, I agree
>
>
> Apparently we aren't dealing with holdable cursors properly optimally
> though
> ==> holdable cursors cancels fetch ; OutOfMemory for our application
>
> Best Regards,
>
> ----- Mail original -----
> De: "Dave Cramer" < pg(at)fastcrypt(dot)com >
> À: "Christophe Canovas" < cc(dot)ais40(at)wanadoo(dot)fr >
> Cc: "List" < pgsql-jdbc(at)postgresql(dot)org >
> Envoyé: Jeudi 21 Août 2014 15:12:47
> Objet: Re: [JDBC] Cursors removed with commit
>
>
>
>
> Cursors generally only exist inside a transaction so the first problem
> makes sense.
>
>
> Apparently we aren't dealing with holdable cursors properly optimally
> though
>
>
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
>
> On 20 August 2014 10:22, Christophe Canovas < cc(dot)ais40(at)wanadoo(dot)fr > wrote:
>
>
> 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(dot)ais40(at)wanadoo(dot)fr >
> À: pgsql-jdbc(at)postgresql(dot)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(at)postgresql(dot)org )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
> --
> Sent via pgsql-jdbc mailing list ( pgsql-jdbc(at)postgresql(dot)org )
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-jdbc
>
>
>