Re: Cursors removed with commit

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" 
À: "Christophe Canovas" 
Cc: "List" 
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.ais40@wanadoo.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:///"; 
Connection con = java.sql.DriverManager.getConnection(acces, "", ""); 
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