Обсуждение: 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 fetchloop. 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
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 (andin 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 fetchloop. 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
Cursors generally only exist inside a transaction so the first problem makes sense.
Apparently we aren't dealing with holdable cursors properly optimally though
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://<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
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@fastcrypt.com> À: "Christophe Canovas" <cc.ais40@wanadoo.fr> Cc: "List" <pgsql-jdbc@postgresql.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.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://<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 (andin 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 fetchloop. 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
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
On 21 August 2014 08:10, Christophe Canovas <cc.ais40@wanadoo.fr> wrote:
Cursors generally only exist inside a transaction so the first problem makes sense.==> yes, I agree==> holdable cursors cancels fetch ; OutOfMemory for our application
Apparently we aren't dealing with holdable cursors properly optimally though
Best Regards,
----- Mail original -----
De: "Dave Cramer" <pg@fastcrypt.com>
À: "Christophe Canovas" <cc.ais40@wanadoo.fr>
Cc: "List" <pgsql-jdbc@postgresql.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.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://<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
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@fastcrypt.com> À: "Christophe Canovas" <cc.ais40@wanadoo.fr> Cc: "List" <pgsql-jdbc@postgresql.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.ais40@wanadoo.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@fastcrypt.com > À: "Christophe Canovas" < cc.ais40@wanadoo.fr > Cc: "List" < pgsql-jdbc@postgresql.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.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://<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 (andin 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 fetchloop. 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
Christophe,
Sorry, no possibly monday
On 3 September 2014 04:36, Christophe Canovas <cc.ais40@wanadoo.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@fastcrypt.com>Envoyé: Jeudi 21 Août 2014 16:08:59
À: "Christophe Canovas" <cc.ais40@wanadoo.fr>
Cc: "List" <pgsql-jdbc@postgresql.org>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.ais40@wanadoo.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@fastcrypt.com >
À: "Christophe Canovas" < cc.ais40@wanadoo.fr >
Cc: "List" < pgsql-jdbc@postgresql.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.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://<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