Thread
-
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Dave Cramer <dave@micro-automation.net> — 2002-09-06T14:05:18Z
Harris, What error do you get? Also you don't need the quotes around id Dave On Fri, 2002-09-06 at 10:06, snpe wrote: > Hello, > I have simple table with column ID and values '4' in this. > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in postgresql.conf. > Next program don't work . > I am tried with compiled postgresql.jar form CVS and with > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > What is wrong ? > > regards > Haris Peco > import java.io.*; > import java.sql.*; > import java.text.*; > > public class PrepStatTest > { > Connection db; > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > String delid = "4"; > public PrepStatTest() throws ClassNotFoundException, FileNotFoundException, > IOException, SQLException > { > Class.forName("org.postgresql.Driver"); > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe", > "snpe"); > PreparedStatement st = db.prepareStatement(stat); > st.setString(1, delid); > int rowsDeleted = st.executeUpdate(); > System.out.println("Rows deleted " + rowsDeleted); > db.commit(); > st.close(); > db.close(); > } > > public static void main(String args[]) > { > try > { > PrepStatTest test = new PrepStatTest(); > } > catch (Exception ex) > { > System.err.println("Exception caught.\n" + ex); > ex.printStackTrace(); > } > } > } > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > -
JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T14:06:52Z
Hello, I have simple table with column ID and values '4' in this. I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in postgresql.conf. Next program don't work . I am tried with compiled postgresql.jar form CVS and with pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org What is wrong ? regards Haris Peco import java.io.*; import java.sql.*; import java.text.*; public class PrepStatTest { Connection db; String stat="DELETE FROM org_ban WHERE \"id\" = ?"; String delid = "4"; public PrepStatTest() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { Class.forName("org.postgresql.Driver"); db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe", "snpe"); PreparedStatement st = db.prepareStatement(stat); st.setString(1, delid); int rowsDeleted = st.executeUpdate(); System.out.println("Rows deleted " + rowsDeleted); db.commit(); st.close(); db.close(); } public static void main(String args[]) { try { PrepStatTest test = new PrepStatTest(); } catch (Exception ex) { System.err.println("Exception caught.\n" + ex); ex.printStackTrace(); } } } -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Dave Cramer <dave@micro-automation.net> — 2002-09-06T14:35:53Z
Remove the quotes around id, and let me know what happens Dave On Fri, 2002-09-06 at 10:52, snpe wrote: > Hello Dave, > There isn't any error.Program write 'Rows deleted 1', but row hasn't been > deleted > > Thanks > Haris Peco > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > > Harris, > > > > What error do you get? > > > > Also you don't need the quotes around id > > > > Dave > > > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > > Hello, > > > I have simple table with column ID and values '4' in this. > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > > > postgresql.conf. Next program don't work . > > > I am tried with compiled postgresql.jar form CVS and with > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > > > What is wrong ? > > > > > > regards > > > Haris Peco > > > import java.io.*; > > > import java.sql.*; > > > import java.text.*; > > > > > > public class PrepStatTest > > > { > > > Connection db; > > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > > String delid = "4"; > > > public PrepStatTest() throws ClassNotFoundException, > > > FileNotFoundException, IOException, SQLException > > > { > > > Class.forName("org.postgresql.Driver"); > > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > > "snpe", "snpe"); > > > PreparedStatement st = db.prepareStatement(stat); > > > st.setString(1, delid); > > > int rowsDeleted = st.executeUpdate(); > > > System.out.println("Rows deleted " + rowsDeleted); > > > db.commit(); > > > st.close(); > > > db.close(); > > > } > > > > > > public static void main(String args[]) > > > { > > > try > > > { > > > PrepStatTest test = new PrepStatTest(); > > > } > > > catch (Exception ex) > > > { > > > System.err.println("Exception caught.\n" + ex); > > > ex.printStackTrace(); > > > } > > > } > > > } > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 3: if posting/reading through Usenet, please send an appropriate > > > subscribe-nomail command to majordomo@postgresql.org so that your > > > message can get through to the mailing list cleanly > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T14:52:05Z
Hello Dave, There isn't any error.Program write 'Rows deleted 1', but row hasn't been deleted Thanks Haris Peco On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > Harris, > > What error do you get? > > Also you don't need the quotes around id > > Dave > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > Hello, > > I have simple table with column ID and values '4' in this. > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > > postgresql.conf. Next program don't work . > > I am tried with compiled postgresql.jar form CVS and with > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > What is wrong ? > > > > regards > > Haris Peco > > import java.io.*; > > import java.sql.*; > > import java.text.*; > > > > public class PrepStatTest > > { > > Connection db; > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > String delid = "4"; > > public PrepStatTest() throws ClassNotFoundException, > > FileNotFoundException, IOException, SQLException > > { > > Class.forName("org.postgresql.Driver"); > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > "snpe", "snpe"); > > PreparedStatement st = db.prepareStatement(stat); > > st.setString(1, delid); > > int rowsDeleted = st.executeUpdate(); > > System.out.println("Rows deleted " + rowsDeleted); > > db.commit(); > > st.close(); > > db.close(); > > } > > > > public static void main(String args[]) > > { > > try > > { > > PrepStatTest test = new PrepStatTest(); > > } > > catch (Exception ex) > > { > > System.err.println("Exception caught.\n" + ex); > > ex.printStackTrace(); > > } > > } > > } > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T15:17:21Z
Hi Dave, That is same.Program work with and without quote but row don't deleted. Postgresql is 7.3 beta (from cvs) and parameter autocommit in postgresql.conf is off (no auto commit). I am tried with db.autocommit(true) after getConnection, but no success I thin that is bug in JDBC PGSql 7.3 beta have new features autocommit on/off and JDBC driver don't work with autocommit off Thanks P.S I am play ith Oracle JDeveloper 9i and Postgresql and I get error in prepared statement like this error : (oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement preparation. Statement: DELETE FROM org_ban WHERE "id"=? and pgsqlerror is : (org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM org_ban WHERE "id"=?] usage : {[? =] call <some_function> ([? [,?]*]) } I think that JDeveloper call CallableStatement for insert or delete (select and update work fine), but I don't know how. On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > Remove the quotes around id, and let me know what happens > > Dave > > On Fri, 2002-09-06 at 10:52, snpe wrote: > > Hello Dave, > > There isn't any error.Program write 'Rows deleted 1', but row hasn't > > been deleted > > > > Thanks > > Haris Peco > > > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > > > Harris, > > > > > > What error do you get? > > > > > > Also you don't need the quotes around id > > > > > > Dave > > > > > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > > > Hello, > > > > I have simple table with column ID and values '4' in this. > > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > > > > postgresql.conf. Next program don't work . > > > > I am tried with compiled postgresql.jar form CVS and with > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > > > > > What is wrong ? > > > > > > > > regards > > > > Haris Peco > > > > import java.io.*; > > > > import java.sql.*; > > > > import java.text.*; > > > > > > > > public class PrepStatTest > > > > { > > > > Connection db; > > > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > > > String delid = "4"; > > > > public PrepStatTest() throws ClassNotFoundException, > > > > FileNotFoundException, IOException, SQLException > > > > { > > > > Class.forName("org.postgresql.Driver"); > > > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > > > "snpe", "snpe"); > > > > PreparedStatement st = db.prepareStatement(stat); > > > > st.setString(1, delid); > > > > int rowsDeleted = st.executeUpdate(); > > > > System.out.println("Rows deleted " + rowsDeleted); > > > > db.commit(); > > > > st.close(); > > > > db.close(); > > > > } > > > > > > > > public static void main(String args[]) > > > > { > > > > try > > > > { > > > > PrepStatTest test = new PrepStatTest(); > > > > } > > > > catch (Exception ex) > > > > { > > > > System.err.println("Exception caught.\n" + ex); > > > > ex.printStackTrace(); > > > > } > > > > } > > > > } > > > > > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 3: if posting/reading > > > > through Usenet, please send an appropriate subscribe-nomail command > > > > to majordomo@postgresql.org so that your message can get through to > > > > the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T15:21:00Z
I set autocommit true in postgresql.conf and program work fine regards Haris Peco On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > Remove the quotes around id, and let me know what happens > > Dave > > On Fri, 2002-09-06 at 10:52, snpe wrote: > > Hello Dave, > > There isn't any error.Program write 'Rows deleted 1', but row hasn't > > been deleted > > > > Thanks > > Haris Peco > > > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > > > Harris, > > > > > > What error do you get? > > > > > > Also you don't need the quotes around id > > > > > > Dave > > > > > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > > > Hello, > > > > I have simple table with column ID and values '4' in this. > > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > > > > postgresql.conf. Next program don't work . > > > > I am tried with compiled postgresql.jar form CVS and with > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > > > > > What is wrong ? > > > > > > > > regards > > > > Haris Peco > > > > import java.io.*; > > > > import java.sql.*; > > > > import java.text.*; > > > > > > > > public class PrepStatTest > > > > { > > > > Connection db; > > > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > > > String delid = "4"; > > > > public PrepStatTest() throws ClassNotFoundException, > > > > FileNotFoundException, IOException, SQLException > > > > { > > > > Class.forName("org.postgresql.Driver"); > > > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > > > "snpe", "snpe"); > > > > PreparedStatement st = db.prepareStatement(stat); > > > > st.setString(1, delid); > > > > int rowsDeleted = st.executeUpdate(); > > > > System.out.println("Rows deleted " + rowsDeleted); > > > > db.commit(); > > > > st.close(); > > > > db.close(); > > > > } > > > > > > > > public static void main(String args[]) > > > > { > > > > try > > > > { > > > > PrepStatTest test = new PrepStatTest(); > > > > } > > > > catch (Exception ex) > > > > { > > > > System.err.println("Exception caught.\n" + ex); > > > > ex.printStackTrace(); > > > > } > > > > } > > > > } > > > > > > > > > > > > ---------------------------(end of > > > > broadcast)--------------------------- TIP 3: if posting/reading > > > > through Usenet, please send an appropriate subscribe-nomail command > > > > to majordomo@postgresql.org so that your message can get through to > > > > the mailing list cleanly > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T15:53:48Z
Hello, If I set db.setAutoCommit(false) after getConnection row is deleted Driver don't see parameter autocommit in postgresql.conf regards On Friday 06 September 2002 05:21 pm, snpe wrote: > I set autocommit true in postgresql.conf and program work fine > > regards > Haris Peco > > On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > > Remove the quotes around id, and let me know what happens > > > > Dave > > > > On Fri, 2002-09-06 at 10:52, snpe wrote: > > > Hello Dave, > > > There isn't any error.Program write 'Rows deleted 1', but row hasn't > > > been deleted > > > > > > Thanks > > > Haris Peco > > > > > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > > > > Harris, > > > > > > > > What error do you get? > > > > > > > > Also you don't need the quotes around id > > > > > > > > Dave > > > > > > > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > > > > Hello, > > > > > I have simple table with column ID and values '4' in this. > > > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > > > > > postgresql.conf. Next program don't work . > > > > > I am tried with compiled postgresql.jar form CVS and with > > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > > > > > > > What is wrong ? > > > > > > > > > > regards > > > > > Haris Peco > > > > > import java.io.*; > > > > > import java.sql.*; > > > > > import java.text.*; > > > > > > > > > > public class PrepStatTest > > > > > { > > > > > Connection db; > > > > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > > > > String delid = "4"; > > > > > public PrepStatTest() throws ClassNotFoundException, > > > > > FileNotFoundException, IOException, SQLException > > > > > { > > > > > Class.forName("org.postgresql.Driver"); > > > > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > > > > "snpe", "snpe"); > > > > > PreparedStatement st = db.prepareStatement(stat); > > > > > st.setString(1, delid); > > > > > int rowsDeleted = st.executeUpdate(); > > > > > System.out.println("Rows deleted " + rowsDeleted); > > > > > db.commit(); > > > > > st.close(); > > > > > db.close(); > > > > > } > > > > > > > > > > public static void main(String args[]) > > > > > { > > > > > try > > > > > { > > > > > PrepStatTest test = new PrepStatTest(); > > > > > } > > > > > catch (Exception ex) > > > > > { > > > > > System.err.println("Exception caught.\n" + ex); > > > > > ex.printStackTrace(); > > > > > } > > > > > } > > > > > } > > > > > > > > > > > > > > > ---------------------------(end of > > > > > broadcast)--------------------------- TIP 3: if posting/reading > > > > > through Usenet, please send an appropriate subscribe-nomail command > > > > > to majordomo@postgresql.org so that your message can get through to > > > > > the mailing list cleanly > > > > > > ---------------------------(end of > > > broadcast)--------------------------- TIP 2: you can get off all lists > > > at once with the unregister command (send "unregister > > > YourEmailAddressHere" to majordomo@postgresql.org) > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 4: Don't 'kill -9' the postmaster > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -
Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T16:45:27Z
Hello Dave, I am find bug with CallableStatement. in Pgsql JDBC (I think that is bug) CallableStatement interface extends PreparedStatement (jdbc 3.0 specification) and command DELETE or UPDATE must work like with PreparedStatement Pgsql JDBC work only with {[? =] call <some_function> ([? [,?]*]) } form Next code work in Oracle : import java.io.*; import java.sql.*; import java.text.*; public class PrepStatTestOra { Connection db; String stat="DELETE FROM org_ban WHERE id = ?"; String delid = "4"; public PrepStatTestOra() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { Class.forName("oracle.jdbc.OracleDriver"); db = DriverManager.getConnection("jdbc:oracle:thin:@spnew:1521:V9i", "snpe2001", "snpe2001"); //db.setAutoCommit(false); //PrepareStatement st = db.prepareStatement(stat); CallableStatement st = db.prepareCall(stat); st.setString(1, delid); int rowsDeleted = st.executeUpdate(); System.out.println("Rows deleted " + rowsDeleted); db.commit(); st.close(); db.close(); } public static void main(String args[]) { try { PrepStatTestOra test = new PrepStatTestOra(); } catch (Exception ex) { System.err.println("Exception caught.\n" + ex); ex.printStackTrace(); } } } This is for pgsql : import java.io.*; import java.sql.*; import java.text.*; public class PrepStatTest { Connection db; String stat="DELETE FROM org_ban WHERE id = ?"; String delid = "4"; public PrepStatTest() throws ClassNotFoundException, FileNotFoundException, IOException, SQLException { Class.forName("org.postgresql.Driver"); db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe", "snpe"); db.setAutoCommit(false); // hack for 'autocommit true' in postgresql.conf //PrepareStatement st = db.prepareStatement(stat); // PreparedStatement work fine CallableStatement st = db.prepareCall(stat); // this must work like previous line with PreparedStatement st.setString(1, delid); int rowsDeleted = st.executeUpdate(); System.out.println("Rows deleted " + rowsDeleted); db.commit(); st.close(); db.close(); } public static void main(String args[]) { try { PrepStatTest test = new PrepStatTest(); } catch (Exception ex) { System.err.println("Exception caught.\n" + ex); ex.printStackTrace(); } } } Example for Oracle work fine and Pgsql get error (same in JDeveloper) : Exception caught. Malformed stmt [DELETE FROM org_ban WHERE "id" = ?] usage : {[? =] call <some_function> ([? [,?]*]) } Malformed stmt [DELETE FROM org_ban WHERE "id" = ?] usage : {[? =] call <some_function> ([? [,?]*]) } at org.postgresql.jdbc1.AbstractJdbc1Statement.modifyJdbcCall(AbstractJdbc1Statement.java:1720) at org.postgresql.jdbc1.AbstractJdbc1Statement.parseSqlStmt(AbstractJdbc1Statement.java:88) at org.postgresql.jdbc1.AbstractJdbc1Statement.<init>(AbstractJdbc1Statement.java:79) at org.postgresql.jdbc2.AbstractJdbc2Statement.<init>(AbstractJdbc2Statement.java:32) at org.postgresql.jdbc3.AbstractJdbc3Statement.<init>(AbstractJdbc3Statement.java:23) at org.postgresql.jdbc3.Jdbc3CallableStatement.<init>(Jdbc3CallableStatement.java:11) at org.postgresql.jdbc3.Jdbc3Connection.prepareCall(Jdbc3Connection.java:36) at org.postgresql.jdbc2.AbstractJdbc2Connection.prepareCall(AbstractJdbc2Connection.java:39) at PrepStatTest.<init>(PrepStatTest.java:16) at PrepStatTest.main(PrepStatTest.java:29) On Friday 06 September 2002 05:38 pm, you wrote: > Possibly, callable statements are a bit of a hack in postgres, since > they don't really exist. If you can send me something that causes the > errors I can try to fix it. > > Dave > > On Fri, 2002-09-06 at 11:59, snpe wrote: > > Hello, > > This is postgresql error on DELETE command > > 'Malformed stmt' is in CallableStatement in JDBC source only > > I think that CallableStatement in Pgsql JDBC driver have any > > incompatibilty because meratnt drivers (for DB2, MS SQL) work fine > > > > Thanks > > Haris Peco > > (org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM org_ban > > WHERE "id"=?] usage : {[? =] call <some_function> ([? [,?]*]) } > > > > On Friday 06 September 2002 05:21 pm, you wrote: > > > No, not off hand, I've never used JDeveloper, nor have I seen that > > > error message > > > > > > Sorry, > > > > > > Dave > > > > > > On Fri, 2002-09-06 at 11:36, snpe wrote: > > > > Hi Dave > > > > Have You any cooment on my P.S. (problem with JDeveloper) ? > > > > > > > > On Friday 06 September 2002 05:07 pm, you wrote: > > > > > Hmmm.... interesting, I guess we have to fix that in the driver > > > > > > > > > > Dave > > > > > > > > > > On Fri, 2002-09-06 at 11:21, snpe wrote: > > > > > > I set autocommit true in postgresql.conf and program work fine > > > > > > > > > > > > regards > > > > > > Haris Peco > > > > > > > > > > > > On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > > > > > > > Remove the quotes around id, and let me know what happens > > > > > > > > > > > > > > Dave > > > > > > > > > > > > > > On Fri, 2002-09-06 at 10:52, snpe wrote: > > > > > > > > Hello Dave, > > > > > > > > There isn't any error.Program write 'Rows deleted 1', but > > > > > > > > row hasn't been deleted > > > > > > > > > > > > > > > > Thanks > > > > > > > > Haris Peco > > > > > > > > > > > > > > > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > > > > > > > > > Harris, > > > > > > > > > > > > > > > > > > What error do you get? > > > > > > > > > > > > > > > > > > Also you don't need the quotes around id > > > > > > > > > > > > > > > > > > Dave > > > > > > > > > > > > > > > > > > On Fri, 2002-09-06 at 10:06, snpe wrote: > > > > > > > > > > Hello, > > > > > > > > > > I have simple table with column ID and values '4' in > > > > > > > > > > this. I user 7.3 beta1 (from cvs 05.09.2002) and > > > > > > > > > > autocommit off in postgresql.conf. Next program don't > > > > > > > > > > work . > > > > > > > > > > I am tried with compiled postgresql.jar form CVS and with > > > > > > > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > > > > > > > > > > > > > > > > > > > > What is wrong ? > > > > > > > > > > > > > > > > > > > > regards > > > > > > > > > > Haris Peco > > > > > > > > > > import java.io.*; > > > > > > > > > > import java.sql.*; > > > > > > > > > > import java.text.*; > > > > > > > > > > > > > > > > > > > > public class PrepStatTest > > > > > > > > > > { > > > > > > > > > > Connection db; > > > > > > > > > > String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > > > > > > > > > > String delid = "4"; > > > > > > > > > > public PrepStatTest() throws ClassNotFoundException, > > > > > > > > > > FileNotFoundException, IOException, SQLException > > > > > > > > > > { > > > > > > > > > > Class.forName("org.postgresql.Driver"); > > > > > > > > > > db = > > > > > > > > > > DriverManager.getConnection("jdbc:postgresql://spnew/snpe > > > > > > > > > >", "snpe", "snpe"); > > > > > > > > > > PreparedStatement st = db.prepareStatement(stat); > > > > > > > > > > st.setString(1, delid); > > > > > > > > > > int rowsDeleted = st.executeUpdate(); > > > > > > > > > > System.out.println("Rows deleted " + rowsDeleted); > > > > > > > > > > db.commit(); > > > > > > > > > > st.close(); > > > > > > > > > > db.close(); > > > > > > > > > > } > > > > > > > > > > > > > > > > > > > > public static void main(String args[]) > > > > > > > > > > { > > > > > > > > > > try > > > > > > > > > > { > > > > > > > > > > PrepStatTest test = new PrepStatTest(); > > > > > > > > > > } > > > > > > > > > > catch (Exception ex) > > > > > > > > > > { > > > > > > > > > > System.err.println("Exception caught.\n" + ex); > > > > > > > > > > ex.printStackTrace(); > > > > > > > > > > } > > > > > > > > > > } > > > > > > > > > > } > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > > > > broadcast)--------------------------- TIP 3: if > > > > > > > > > > posting/reading through Usenet, please send an > > > > > > > > > > appropriate subscribe-nomail command to > > > > > > > > > > majordomo@postgresql.org so that your message can get > > > > > > > > > > through to the mailing list cleanly > > > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > > broadcast)--------------------------- TIP 2: you can get off > > > > > > > > all lists at once with the unregister command (send > > > > > > > > "unregister YourEmailAddressHere" to > > > > > > > > majordomo@postgresql.org) > > > > > > > > > > > > > > ---------------------------(end of > > > > > > > broadcast)--------------------------- TIP 4: Don't 'kill -9' > > > > > > > the postmaster > > > > > > > > > > > > ---------------------------(end of > > > > > > broadcast)--------------------------- TIP 6: Have you searched > > > > > > our list archives? > > > > > > > > > > > > http://archives.postgresql.org -
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-06T17:37:15Z
Hello Barry, JDBC driver must find autocommit (off or on) and set autoCommit field when open connection. regards On Friday 06 September 2002 06:52 pm, Barry Lind wrote: > Haris, > > You can't use jdbc (and probably most other postgres clients) with > autocommit in postgresql.conf turned off. > > Hackers, > > How should client interfaces handle this new autocommit feature? Is it > best to just issue a set at the beginning of the connection to ensure > that it is always on? > > thanks, > --Barry > > snpe wrote: > >Hi Dave, > >That is same.Program work with and without quote but row don't deleted. > >Postgresql is 7.3 beta (from cvs) and parameter autocommit in > > postgresql.conf > > >is off (no auto commit). > >I am tried with db.autocommit(true) after getConnection, but no success > > > >I thin that is bug in JDBC > >PGSql 7.3 beta have new features autocommit on/off and JDBC driver > > don't work > > >with autocommit off > > > >Thanks > > > >P.S > >I am play ith Oracle JDeveloper 9i and Postgresql and I get error in > > prepared > > >statement like this error : > >(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement > >preparation. Statement: DELETE FROM org_ban WHERE "id"=? > > > >and pgsqlerror is : > >(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM > > org_ban WHERE > > >"id"=?] usage : {[? =] call <some_function> ([? [,?]*]) } > > > >I think that JDeveloper call CallableStatement for insert or delete > > (select > > >and update work fine), but I don't know how. > > > >On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > >>Remove the quotes around id, and let me know what happens > >> > >>Dave > >> > >>On Fri, 2002-09-06 at 10:52, snpe wrote: > >>>Hello Dave, > >>> There isn't any error.Program write 'Rows deleted 1', but row hasn't > >>>been deleted > >>> > >>>Thanks > >>>Haris Peco > >>> > >>>On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: > >>>>Harris, > >>>> > >>>>What error do you get? > >>>> > >>>>Also you don't need the quotes around id > >>>> > >>>>Dave > >>>> > >>>>On Fri, 2002-09-06 at 10:06, snpe wrote: > >>>>>Hello, > >>>>> I have simple table with column ID and values '4' in this. > >>>>>I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in > >>>>>postgresql.conf. Next program don't work . > >>>>>I am tried with compiled postgresql.jar form CVS and with > >>>>>pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org > >>>>> > >>>>>What is wrong ? > >>>>> > >>>>>regards > >>>>>Haris Peco > >>>>>import java.io.*; > >>>>>import java.sql.*; > >>>>>import java.text.*; > >>>>> > >>>>>public class PrepStatTest > >>>>>{ > >>>>> Connection db; > >>>>> String stat="DELETE FROM org_ban WHERE \"id\" = ?"; > >>>>> String delid = "4"; > >>>>> public PrepStatTest() throws ClassNotFoundException, > >>>>>FileNotFoundException, IOException, SQLException > >>>>> { > > Class.forName("org.postgresql.Driver"); > > db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", > > >>>>>"snpe", "snpe"); > > PreparedStatement st = db.prepareStatement(stat); > > >>>>> st.setString(1, delid); > >>>>> int rowsDeleted = st.executeUpdate(); > > System.out.println("Rows deleted " + rowsDeleted); > > db.commit(); > > st.close(); > > db.close(); > > >>>>> } > >>>>> > >>>>> public static void main(String args[]) > >>>>> { > > try > > { > > PrepStatTest test = new PrepStatTest(); > > } > > catch (Exception ex) > > { > > System.err.println("Exception caught.\n" + ex); > > ex.printStackTrace(); > > } > > >>>>> } > >>>>>} > >>>>> > >>>>> > >>>>>---------------------------(end of > >>>>>broadcast)--------------------------- TIP 3: if posting/reading > >>>>>through Usenet, please send an appropriate subscribe-nomail command > >>>>>to majordomo@postgresql.org so that your message can get through to > >>>>>the mailing list cleanly > >>> > >>>---------------------------(end of > >>> broadcast)--------------------------- TIP 2: you can get off all lists > >>> at once with the unregister command (send "unregister > >>> YourEmailAddressHere" to majordomo@postgresql.org) > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 4: Don't 'kill -9' the postmaster > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: if posting/reading through Usenet, please send an appropriate > >subscribe-nomail command to majordomo@postgresql.org so that your > >message can get through to the mailing list cleanly -
problem with new autocommit config parameter and jdbc
Barry Lind <barry@xythos.com> — 2002-09-06T23:30:27Z
Haris, You can't use jdbc (and probably most other postgres clients) with autocommit in postgresql.conf turned off. Hackers, How should client interfaces handle this new autocommit feature? Is it best to just issue a set at the beginning of the connection to ensure that it is always on? thanks, --Barry snpe wrote: >Hi Dave, >That is same.Program work with and without quote but row don't deleted. >Postgresql is 7.3 beta (from cvs) and parameter autocommit in postgresql.conf >is off (no auto commit). >I am tried with db.autocommit(true) after getConnection, but no success > >I thin that is bug in JDBC >PGSql 7.3 beta have new features autocommit on/off and JDBC driver don't work >with autocommit off > >Thanks > >P.S >I am play ith Oracle JDeveloper 9i and Postgresql and I get error in prepared >statement like this error : >(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement >preparation. Statement: DELETE FROM org_ban WHERE "id"=? > >and pgsqlerror is : >(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM org_ban WHERE >"id"=?] usage : {[? =] call <some_function> ([? [,?]*]) } > >I think that JDeveloper call CallableStatement for insert or delete (select >and update work fine), but I don't know how. > >On Friday 06 September 2002 04:35 pm, Dave Cramer wrote: > > >>Remove the quotes around id, and let me know what happens >> >>Dave >> >>On Fri, 2002-09-06 at 10:52, snpe wrote: >> >> >>>Hello Dave, >>> There isn't any error.Program write 'Rows deleted 1', but row hasn't >>>been deleted >>> >>>Thanks >>>Haris Peco >>> >>>On Friday 06 September 2002 04:05 pm, Dave Cramer wrote: >>> >>> >>>>Harris, >>>> >>>>What error do you get? >>>> >>>>Also you don't need the quotes around id >>>> >>>>Dave >>>> >>>>On Fri, 2002-09-06 at 10:06, snpe wrote: >>>> >>>> >>>>>Hello, >>>>> I have simple table with column ID and values '4' in this. >>>>>I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in >>>>>postgresql.conf. Next program don't work . >>>>>I am tried with compiled postgresql.jar form CVS and with >>>>>pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org >>>>> >>>>>What is wrong ? >>>>> >>>>>regards >>>>>Haris Peco >>>>>import java.io.*; >>>>>import java.sql.*; >>>>>import java.text.*; >>>>> >>>>>public class PrepStatTest >>>>>{ >>>>> Connection db; >>>>> String stat="DELETE FROM org_ban WHERE \"id\" = ?"; >>>>> String delid = "4"; >>>>> public PrepStatTest() throws ClassNotFoundException, >>>>>FileNotFoundException, IOException, SQLException >>>>> { >>>>> Class.forName("org.postgresql.Driver"); >>>>> db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", >>>>>"snpe", "snpe"); >>>>> PreparedStatement st = db.prepareStatement(stat); >>>>> st.setString(1, delid); >>>>> int rowsDeleted = st.executeUpdate(); >>>>> System.out.println("Rows deleted " + rowsDeleted); >>>>> db.commit(); >>>>> st.close(); >>>>> db.close(); >>>>> } >>>>> >>>>> public static void main(String args[]) >>>>> { >>>>> try >>>>> { >>>>> PrepStatTest test = new PrepStatTest(); >>>>> } >>>>> catch (Exception ex) >>>>> { >>>>> System.err.println("Exception caught.\n" + ex); >>>>> ex.printStackTrace(); >>>>> } >>>>> } >>>>>} >>>>> >>>>> >>>>>---------------------------(end of >>>>>broadcast)--------------------------- TIP 3: if posting/reading >>>>>through Usenet, please send an appropriate subscribe-nomail command >>>>>to majordomo@postgresql.org so that your message can get through to >>>>>the mailing list cleanly >>>>> >>>>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 2: you can get off all lists at once with the unregister command >>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >>> >>> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> >> > > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate >subscribe-nomail command to majordomo@postgresql.org so that your >message can get through to the mailing list cleanly > > > -
Re: problem with new autocommit config parameter and jdbc
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-07T00:55:17Z
Barry Lind wrote: > Haris, > > You can't use jdbc (and probably most other postgres clients) with > autocommit in postgresql.conf turned off. > > Hackers, > > How should client interfaces handle this new autocommit feature? Is it > best to just issue a set at the beginning of the connection to ensure > that it is always on? Yes, I thought that was the best fix for apps that can't deal with autocommit being off. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: [JDBC] problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-07T12:59:31Z
On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote: > Barry Lind wrote: > > Haris, > > > > You can't use jdbc (and probably most other postgres clients) with > > autocommit in postgresql.conf turned off. > > > > Hackers, > > > > How should client interfaces handle this new autocommit feature? Is it > > best to just issue a set at the beginning of the connection to ensure > > that it is always on? > > Yes, I thought that was the best fix for apps that can't deal with > autocommit being off. Can client get information from backend for autocommit (on or off) and that work like psql ?
-
Re: [JDBC] problem with new autocommit config parameter and
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-07T14:07:19Z
snpe wrote: > On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote: > > Barry Lind wrote: > > > Haris, > > > > > > You can't use jdbc (and probably most other postgres clients) with > > > autocommit in postgresql.conf turned off. > > > > > > Hackers, > > > > > > How should client interfaces handle this new autocommit feature? Is it > > > best to just issue a set at the beginning of the connection to ensure > > > that it is always on? > > > > Yes, I thought that was the best fix for apps that can't deal with > > autocommit being off. > Can client get information from backend for autocommit (on or off) and that > work like psql ? Sure, you can do SHOW autocommit. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: [JDBC] problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-07T18:57:55Z
On Saturday 07 September 2002 04:07 pm, Bruce Momjian wrote: > snpe wrote: > > On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote: > > > Barry Lind wrote: > > > > Haris, > > > > > > > > You can't use jdbc (and probably most other postgres clients) with > > > > autocommit in postgresql.conf turned off. > > > > > > > > Hackers, > > > > > > > > How should client interfaces handle this new autocommit feature? Is > > > > it best to just issue a set at the beginning of the connection to > > > > ensure that it is always on? > > > > > > Yes, I thought that was the best fix for apps that can't deal with > > > autocommit being off. > > > > Can client get information from backend for autocommit (on or off) and > > that work like psql ? > > Sure, you can do SHOW autocommit. I am interesting with JDBC driver. When I make connection in base I want that driver find autocommit mode (from postgresql.conf or call in backend) and set mode true or false thanks
-
Re: [JDBC] problem with new autocommit config parameter
Joe Conway <mail@joeconway.com> — 2002-09-07T19:12:36Z
snpe wrote: >>Sure, you can do SHOW autocommit. > > I am interesting with JDBC driver. > When I make connection in base I want that driver find autocommit mode > (from postgresql.conf or call in backend) and set mode true or false > You could make a call to current_setting in the backend. I don't know anything about the jdbc driver, but if it's written in C something like this should work: text *autocommit = DatumGetTextP(DirectFunctionCall1(current_setting, CStringGetDatum("autocommit"))); Would this work? Joe -
Re: [JDBC] problem with new autocommit config parameter
Haris Peco <snpe@snpe.co.yu> — 2002-09-07T20:09:45Z
On Saturday 07 September 2002 09:12 pm, Joe Conway wrote: > snpe wrote: > >>Sure, you can do SHOW autocommit. > > > > I am interesting with JDBC driver. > > When I make connection in base I want that driver find autocommit mode > > (from postgresql.conf or call in backend) and set mode true or false > > You could make a call to current_setting in the backend. > > I don't know anything about the jdbc driver, but if it's written in C > something like this should work: > > text *autocommit = DatumGetTextP(DirectFunctionCall1(current_setting, > CStringGetDatum("autocommit"))); > > Would this work? Yes.But I don't know like call in JDBC. -
Re: [JDBC] problem with new autocommit config parameter
Barry Lind <barry@xythos.com> — 2002-09-07T22:39:46Z
Yes it is possible, but according to the jdbc spec, a new connection in jdbc is always initialized to autocommit=true. So jdbc needs to ignore whatever the current server setting is and reset to autocommit=true. --Barry snpe wrote: > On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote: > >>Barry Lind wrote: >> >>>Haris, >>> >>>You can't use jdbc (and probably most other postgres clients) with >>>autocommit in postgresql.conf turned off. >>> >>>Hackers, >>> >>>How should client interfaces handle this new autocommit feature? Is it >>>best to just issue a set at the beginning of the connection to ensure >>>that it is always on? >> >>Yes, I thought that was the best fix for apps that can't deal with >>autocommit being off. > > Can client get information from backend for autocommit (on or off) and that > work like psql ? > > >
-
Re: problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-09T18:53:56Z
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Barry Lind wrote: >> How should client interfaces handle this new autocommit feature? Is it >> best to just issue a set at the beginning of the connection to ensure >> that it is always on? > Yes, I thought that was the best fix for apps that can't deal with > autocommit being off. If autocommit=off really seriously breaks JDBC then I don't think a simple SET command at the start of a session is going to do that much to improve robustness. What if the user issues another SET to turn it on? I'd suggest just documenting that it is broken and you can't use it, until such time as you can get it fixed. Band-aids that only partially cover the problem don't seem worth the effort to me. In general I think that autocommit=off is probably going to be very poorly supported in the 7.3 release. We can document it as being "work in progress, use at your own risk". regards, tom lane
-
Re: problem with new autocommit config parameter and jdbc
Rod Taylor <rbt@rbt.ca> — 2002-09-09T21:03:46Z
On Mon, 2002-09-09 at 17:04, snpe wrote: > I'm use 'autocommit=false' and have problem with psql > When any commnad is lost, then next commnad get error for transactions > (simple select command).BTW > > snpe> select * from org_ba; > ERROR: relation org_ba does not exists > snpe> select * from org_ban; > ERROR: current transactions is aborted, queries ignored until end of > transaction block > snpe> rollback; > ROLLBACK > snpe> select * from org_ban; Maybe I'm missing something, but isn't that the expected behaviour when autocommit is turned off? -- Rod Taylor
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-09T21:04:38Z
On Monday 09 September 2002 08:53 pm, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Barry Lind wrote: > >> How should client interfaces handle this new autocommit feature? Is it > >> best to just issue a set at the beginning of the connection to ensure > >> that it is always on? > > > > Yes, I thought that was the best fix for apps that can't deal with > > autocommit being off. > > If autocommit=off really seriously breaks JDBC then I don't think a > simple SET command at the start of a session is going to do that much > to improve robustness. What if the user issues another SET to turn it > on? > > I'd suggest just documenting that it is broken and you can't use it, > until such time as you can get it fixed. Band-aids that only partially > cover the problem don't seem worth the effort to me. > > In general I think that autocommit=off is probably going to be very > poorly supported in the 7.3 release. We can document it as being > "work in progress, use at your own risk". > I'm use 'autocommit=false' and have problem with psql When any commnad is lost, then next commnad get error for transactions (simple select command).BTW snpe> select * from org_ba; ERROR: relation org_ba does not exists snpe> select * from org_ban; ERROR: current transactions is aborted, queries ignored until end of transaction block snpe> rollback; ROLLBACK snpe> select * from org_ban; this command is ok. regards Haris Peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T01:05:00Z
On Tue, 10 Sep 2002, snpe wrote: > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: > > On Mon, 2002-09-09 at 17:04, snpe wrote: > > > I'm use 'autocommit=false' and have problem with psql > > > When any commnad is lost, then next commnad get error for transactions > > > (simple select command).BTW > > > > > > snpe> select * from org_ba; > > > ERROR: relation org_ba does not exists > > > snpe> select * from org_ban; > > > ERROR: current transactions is aborted, queries ignored until end of > > > transaction block > > > snpe> rollback; > > > ROLLBACK > > > snpe> select * from org_ban; > > > > Maybe I'm missing something, but isn't that the expected behaviour when > > autocommit is turned off? > I get this every time.When exists command with error next command don't work > without explicit rollback and commit (this is not for psql, this error get in > with JDeveloper - JDBC driver).When autocommit=ture all is fine It starts a transaction, failes the first command and goes into the error has occurred in this transaction state. Seems like reasonable behavior.
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T01:05:21Z
On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: > On Mon, 2002-09-09 at 17:04, snpe wrote: > > I'm use 'autocommit=false' and have problem with psql > > When any commnad is lost, then next commnad get error for transactions > > (simple select command).BTW > > > > snpe> select * from org_ba; > > ERROR: relation org_ba does not exists > > snpe> select * from org_ban; > > ERROR: current transactions is aborted, queries ignored until end of > > transaction block > > snpe> rollback; > > ROLLBACK > > snpe> select * from org_ban; > > Maybe I'm missing something, but isn't that the expected behaviour when > autocommit is turned off? I get this every time.When exists command with error next command don't work without explicit rollback and commit (this is not for psql, this error get in with JDeveloper - JDBC driver).When autocommit=ture all is fine haris peco
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T02:08:01Z
On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote: > On Tue, 10 Sep 2002, snpe wrote: > > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: > > > On Mon, 2002-09-09 at 17:04, snpe wrote: > > > > I'm use 'autocommit=false' and have problem with psql > > > > When any commnad is lost, then next commnad get error for > > > > transactions (simple select command).BTW > > > > > > > > snpe> select * from org_ba; > > > > ERROR: relation org_ba does not exists > > > > snpe> select * from org_ban; > > > > ERROR: current transactions is aborted, queries ignored until end of > > > > transaction block > > > > snpe> rollback; > > > > ROLLBACK > > > > snpe> select * from org_ban; > > > > > > Maybe I'm missing something, but isn't that the expected behaviour when > > > autocommit is turned off? > > > > I get this every time.When exists command with error next command don't > > work without explicit rollback and commit (this is not for psql, this > > error get in with JDeveloper - JDBC driver).When autocommit=ture all is > > fine > > It starts a transaction, failes the first command and goes into the > error has occurred in this transaction state. Seems like reasonable > behavior. Select command don't start transaction - it is not good Error command don't start transaction - nothing hapen, only typing error regards haris peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T02:16:47Z
On Tue, 10 Sep 2002, snpe wrote: > On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote: > > On Tue, 10 Sep 2002, snpe wrote: > > > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: > > > > On Mon, 2002-09-09 at 17:04, snpe wrote: > > > > > I'm use 'autocommit=false' and have problem with psql > > > > > When any commnad is lost, then next commnad get error for > > > > > transactions (simple select command).BTW > > > > > > > > > > snpe> select * from org_ba; > > > > > ERROR: relation org_ba does not exists > > > > > snpe> select * from org_ban; > > > > > ERROR: current transactions is aborted, queries ignored until end of > > > > > transaction block > > > > > snpe> rollback; > > > > > ROLLBACK > > > > > snpe> select * from org_ban; > > > > > > > > Maybe I'm missing something, but isn't that the expected behaviour when > > > > autocommit is turned off? > > > > > > I get this every time.When exists command with error next command don't > > > work without explicit rollback and commit (this is not for psql, this > > > error get in with JDeveloper - JDBC driver).When autocommit=ture all is > > > fine > > > > It starts a transaction, failes the first command and goes into the > > error has occurred in this transaction state. Seems like reasonable > > behavior. > Select command don't start transaction - it is not good I think you need more justification than "it is not good." If I do a sequence of select statements in autocommit=false, I'd expect the same consistancy as if I'd done begin; select ...; select ...; > Error command don't start transaction - nothing hapen, only typing error If you do an insert that violates a constraint, does that start an transaction or not? I think we have to choose before we start doing the statement not after.
-
Re: problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-10T02:27:03Z
snpe <snpe@snpe.co.yu> writes: > I'm use 'autocommit=false' and have problem with psql > When any commnad is lost, then next commnad get error for transactions > (simple select command).BTW > snpe> select * from org_ba; > ERROR: relation org_ba does not exists > snpe> select * from org_ban; > ERROR: current transactions is aborted, queries ignored until end of > transaction block Um, what's wrong with that? It seems to me that an application that is using autocommit=off will expect the first SELECT to start a transaction block. If the first SELECT fails, then subsequent commands *should* fail until you commit or rollback. Certainly if you did an explicit BEGIN before the first SELECT, the above is what you'd get --- why should implicit BEGIN work differently? regards, tom lane
-
Re: problem with new autocommit config parameter and jdbc
Curt Sampson <cjs@cynic.net> — 2002-09-10T02:51:21Z
On Mon, 9 Sep 2002, Tom Lane wrote: > snpe <snpe@snpe.co.yu> writes: > > > snpe> select * from org_ba; > > ERROR: relation org_ba does not exists > > snpe> select * from org_ban; > > ERROR: current transactions is aborted, queries ignored until end of > > transaction block > > Um, what's wrong with that? > > It seems to me that an application that is using autocommit=off will > expect the first SELECT to start a transaction block. Yup. In fact, the standard (at least, insofar as I have information relating to it), specifies that the first SELECT statement above *must* start a transaction. From Date's _A Guide to the SQL Standard_ (Fourth Edition): An SQL-transaction is initiated when the relevant SQL-agent executes a "transaction-initiating" SQL Statement (see below) and the SQL-agent does not already have an SQL-transaction in progress. ... The following SQL statements are _not_ transaction-initiating: CONNECT SET CONNECTION DISCONNECT SET SESSION AUTHORIZATION SET CATALOG SET SCHEMA SET NAMES SET TIME ZONE SET TRANSACTION SET CONSTRAINTS COMMIT ROLLBACK GET DIAGNOSTICS Nor, of course, are the nonexecutable statements DECLARE CURSOR, DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE SECTIONS, and WHENEVER. So SELECT ought always to initiate a transaction, if one is not already in progress. If auto-commit is enabled, of course, that statement may be committed immediately after execution, if it doesn't fail. As far as the JDBC driver goes, I'm not too sure of the issues here, but it should certainly be ensuring that autocommit is enabled, as per the JDBC specification, when a new connection is created. I see no reason this couldn't be done with a "SET AUTOCOMMIT TO OFF" or whatever, if that's necessary to override a possible configuration file setting. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: [JDBC] problem with new autocommit config parameter
Curt Sampson <cjs@cynic.net> — 2002-09-10T02:59:55Z
On Mon, 9 Sep 2002, Tom Lane wrote: > If autocommit=off really seriously breaks JDBC then I don't think a > simple SET command at the start of a session is going to do that much > to improve robustness. What if the user issues another SET to turn it > on? You mean, to turn it off again? The driver should catch this, in theory. In practice we could probably live with saying, "Don't use SET AUTOCOMMIT; use the methods on the Connection class instead." Probably the driver should be changed for 7.3 just to use the server's SET AUTOCOMMIT functionality.... cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: [JDBC] problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-10T03:27:13Z
Curt Sampson <cjs@cynic.net> writes: > Probably the driver should be changed for 7.3 just to use the server's > SET AUTOCOMMIT functionality.... That should happen eventually, IMHO, but I am not going to tell the JDBC developers that they must make it happen for 7.3. They've already got a pile of much-higher-priority things to fix for 7.3, like schema compatibility and dropped-column handling. My feeling about the original complaint is very simple: setting server autocommit to off is not supported with JDBC (nor is it fully supported with any other of our frontend clients, right at this instant, though that may improve somewhat before 7.3 release). If you don't like it, tough; contribute the required fixes or stop complaining. Someone else will fix it when they get around to it, but there are bigger problems to deal with first. Autocommit is only a work-in-progress today, not something that we promise will do anything useful for anybody. regards, tom lane
-
Re: problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-10T13:40:51Z
Curt Sampson <cjs@cynic.net> writes: > From Date's _A Guide to the SQL Standard_ (Fourth Edition): > ... > The following SQL statements are _not_ transaction-initiating: > CONNECT > SET CONNECTION > DISCONNECT > SET SESSION AUTHORIZATION > SET CATALOG > SET SCHEMA > SET NAMES > SET TIME ZONE > SET TRANSACTION > SET CONSTRAINTS > COMMIT > ROLLBACK > GET DIAGNOSTICS Hm. This brings up a thought I've been turning over for the past couple days. As of CVS tip, SET commands *do* initiate transactions if you have autocommit off. By your reading of Date, this is not spec compliant for certain SET variables: a SET not already within a transaction should not start a transaction block, at least for the variables mentioned above. It occurs to me that it'd be reasonable to make it act that way for all SET variables. An example of how this would simplify life: consider the problem of a client that wants to ensure autocommit is on. A simple SET autocommit TO on; doesn't work at the moment: if autocommit is off, then you'll need to issue a COMMIT as well to get out of the implicitly started transaction. But you don't want to just issue a COMMIT, because you'll get a nasty ugly WARNING message on stderr if indeed autocommit was on already. The only warning-free way to issue a SET right now if you are uncertain about autocommit status is BEGIN; SET .... ; COMMIT; Blech. But if SET doesn't start a transaction then you can still just do SET. This avoids some changes we'll otherwise have to make in libpq startup, among other places. Does anyone see any cases where it's important for SET to start a transaction? (Of course, if you are already *in* a transaction, the SET will be part of that transaction. The question is whether we want SET to trigger an implicit BEGIN or not.) > Nor, of course, are the nonexecutable statements DECLARE CURSOR, > DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE > SECTIONS, and WHENEVER. Hmm. I think the spec's notion of DECLARE must be different from ours. Our implementation of DECLARE CURSOR both declares and opens the cursor, and as such it *must* be transaction-initiating; else it's useless. regards, tom lane
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T13:43:24Z
On Tuesday 10 September 2002 04:16 am, Stephan Szabo wrote: > On Tue, 10 Sep 2002, snpe wrote: > > On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote: > > > On Tue, 10 Sep 2002, snpe wrote: > > > > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote: > > > > > On Mon, 2002-09-09 at 17:04, snpe wrote: > > > > > > I'm use 'autocommit=false' and have problem with psql > > > > > > When any commnad is lost, then next commnad get error for > > > > > > transactions (simple select command).BTW > > > > > > > > > > > > snpe> select * from org_ba; > > > > > > ERROR: relation org_ba does not exists > > > > > > snpe> select * from org_ban; > > > > > > ERROR: current transactions is aborted, queries ignored until end > > > > > > of transaction block > > > > > > snpe> rollback; > > > > > > ROLLBACK > > > > > > snpe> select * from org_ban; > > > > > > > > > > Maybe I'm missing something, but isn't that the expected behaviour > > > > > when autocommit is turned off? > > > > > > > > I get this every time.When exists command with error next command > > > > don't work without explicit rollback and commit (this is not for > > > > psql, this error get in with JDeveloper - JDBC driver).When > > > > autocommit=ture all is fine > > > > > > It starts a transaction, failes the first command and goes into the > > > error has occurred in this transaction state. Seems like reasonable > > > behavior. > > > > Select command don't start transaction - it is not good > > I think you need more justification than "it is not good." If I do a > sequence of select statements in autocommit=false, I'd expect the same > consistancy as if I'd done > begin; > select ...; > select ...; > Ok.You start transaction explicit and this is ok. But simple SELECT don't start transaction. > > Error command don't start transaction - nothing hapen, only typing error > > If you do an insert that violates a constraint, does that start an > transaction or not? I think we have to choose before we start doing the > statement not after. This is typeing error.Nothing happen.That is not transaction. I don't know that is possible, but before start transaction we need parsing command and select or any error don't start transaction This is problem for every client (I know for JDBC) regards Haris Peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T15:33:16Z
> > > > It starts a transaction, failes the first command and goes into the > > > > error has occurred in this transaction state. Seems like reasonable > > > > behavior. > > > > > > Select command don't start transaction - it is not good > > > > I think you need more justification than "it is not good." If I do a > > sequence of select statements in autocommit=false, I'd expect the same > > consistancy as if I'd done > > begin; > > select ...; > > select ...; > > > Ok.You start transaction explicit and this is ok. > But simple SELECT don't start transaction. Actually someone post a bit from Date's book that implies it does. And, that's still not an justification, it's just a restating of same position. I don't see any reason why the two should be different from a data consistency standpoint, there might be one, but you haven't given any reasons. > > > Error command don't start transaction - nothing hapen, only typing error > > > > If you do an insert that violates a constraint, does that start an > > transaction or not? I think we have to choose before we start doing the > > statement not after. > This is typeing error.Nothing happen.That is not transaction. > I don't know that is possible, but before start transaction we need parsing > command and select or any error don't start transaction Why not? AFAICT it should, the transaction is initiated a statement is run and it fails. Now maybe we shouldn't be going into the wierd disabled statement state, but that's a different argument entirely.
-
Re: [JDBC] problem with new autocommit config parameter
Barry Lind <barry@xythos.com> — 2002-09-10T16:36:28Z
I am waiting for this thread to conclude before deciding exactly what to do for the jdbc driver for 7.3. While using the 'set autocommit true' syntax is nice when talking to a 7.3 server, the jdbc driver also needs to be backwardly compatible with 7.2 and 7.1 servers. So it may just be easier to continue with the current way of doing things, even in the 7.3 case. thanks, --Barry Curt Sampson wrote: > On Mon, 9 Sep 2002, Tom Lane wrote: > > >>If autocommit=off really seriously breaks JDBC then I don't think a >>simple SET command at the start of a session is going to do that much >>to improve robustness. What if the user issues another SET to turn it >>on? > > > You mean, to turn it off again? The driver should catch this, in theory. > > In practice we could probably live with saying, "Don't use SET > AUTOCOMMIT; use the methods on the Connection class instead." > > Probably the driver should be changed for 7.3 just to use the server's > SET AUTOCOMMIT functionality.... > > cjs
-
Re: [JDBC] problem with new autocommit config parameter and
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-10T17:42:36Z
Tom Lane wrote: > An example of how this would simplify life: consider the problem of > a client that wants to ensure autocommit is on. A simple > SET autocommit TO on; > doesn't work at the moment: if autocommit is off, then you'll need > to issue a COMMIT as well to get out of the implicitly started > transaction. But you don't want to just issue a COMMIT, because > you'll get a nasty ugly WARNING message on stderr if indeed autocommit > was on already. The only warning-free way to issue a SET right now > if you are uncertain about autocommit status is > BEGIN; SET .... ; COMMIT; > Blech. But if SET doesn't start a transaction then you can still > just do SET. This avoids some changes we'll otherwise have to make > in libpq startup, among other places. > > Does anyone see any cases where it's important for SET to start > a transaction? (Of course, if you are already *in* a transaction, > the SET will be part of that transaction. The question is whether > we want SET to trigger an implicit BEGIN or not.) Uh, well, because we now have SET's rollback in an aborted transaction, there is an issue of whether the SET is part of the transaction or not. Seems it has to be for consistency with our rollback behavior. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: problem with new autocommit config parameter and jdbc
scott.marlowe <scott.marlowe@ihs.com> — 2002-09-10T17:46:17Z
On Tue, 10 Sep 2002, Stephan Szabo wrote: > > > > > It starts a transaction, failes the first command and goes into the > > > > > error has occurred in this transaction state. Seems like reasonable > > > > > behavior. > > > > > > > > Select command don't start transaction - it is not good > > > > > > I think you need more justification than "it is not good." If I do a > > > sequence of select statements in autocommit=false, I'd expect the same > > > consistancy as if I'd done > > > begin; > > > select ...; > > > select ...; > > > > > Ok.You start transaction explicit and this is ok. > > But simple SELECT don't start transaction. > > Actually someone post a bit from Date's book that implies it does. > And, that's still not an justification, it's just a restating of same > position. I don't see any reason why the two should be different from > a data consistency standpoint, there might be one, but you haven't > given any reasons. What if it's a select for update? IF that failed because of a timout on a lock, shouldn't the transaction fail? Or a select into? Either of those should make a transaction fail, and they're just selects.
-
Re: [JDBC] problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-10T18:45:34Z
Bruce Momjian <pgman@candle.pha.pa.us> writes: > Tom Lane wrote: >> Does anyone see any cases where it's important for SET to start >> a transaction? (Of course, if you are already *in* a transaction, >> the SET will be part of that transaction. The question is whether >> we want SET to trigger an implicit BEGIN or not.) > Uh, well, because we now have SET's rollback in an aborted transaction, > there is an issue of whether the SET is part of the transaction or not. > Seems it has to be for consistency with our rollback behavior. Yeah, it must be part of the transaction unless we want to reopen the SET-rollback can of worms (which I surely don't want to). However, a SET issued outside any pre-existing transaction block could form a self-contained transaction without any logical difficulty, even in autocommit-off mode. The question is whether that's more or less convenient, or standards-conforming, than what we have. An alternative that I'd really rather not consider is making SET's behavior dependent on exactly which variable is being set ... regards, tom lane
-
Re: [JDBC] problem with new autocommit config parameter and
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-10T18:49:03Z
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > Tom Lane wrote: > >> Does anyone see any cases where it's important for SET to start > >> a transaction? (Of course, if you are already *in* a transaction, > >> the SET will be part of that transaction. The question is whether > >> we want SET to trigger an implicit BEGIN or not.) > > > Uh, well, because we now have SET's rollback in an aborted transaction, > > there is an issue of whether the SET is part of the transaction or not. > > Seems it has to be for consistency with our rollback behavior. > > Yeah, it must be part of the transaction unless we want to reopen the > SET-rollback can of worms (which I surely don't want to). > > However, a SET issued outside any pre-existing transaction block could > form a self-contained transaction without any logical difficulty, even > in autocommit-off mode. The question is whether that's more or less > convenient, or standards-conforming, than what we have. That seems messy. What you are saying is that if autocommit is off, then in: SET x=1; UPDATE ... SET y=2; ROLLBACK; that the x=1 doesn't get rolled back bu the y=2 does? I can't see any good logic for that. > An alternative that I'd really rather not consider is making SET's > behavior dependent on exactly which variable is being set ... Agreed. We discussed that in the SET rollback case and found it was more trouble that it was worth. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T19:31:09Z
On Tue, 10 Sep 2002, scott.marlowe wrote: > On Tue, 10 Sep 2002, Stephan Szabo wrote: > > > > > > > It starts a transaction, failes the first command and goes into the > > > > > > error has occurred in this transaction state. Seems like reasonable > > > > > > behavior. > > > > > > > > > > Select command don't start transaction - it is not good > > > > > > > > I think you need more justification than "it is not good." If I do a > > > > sequence of select statements in autocommit=false, I'd expect the same > > > > consistancy as if I'd done > > > > begin; > > > > select ...; > > > > select ...; > > > > > > > Ok.You start transaction explicit and this is ok. > > > But simple SELECT don't start transaction. > > > > Actually someone post a bit from Date's book that implies it does. > > And, that's still not an justification, it's just a restating of same > > position. I don't see any reason why the two should be different from > > a data consistency standpoint, there might be one, but you haven't > > given any reasons. > > What if it's a select for update? IF that failed because of a timout on a > lock, shouldn't the transaction fail? Or a select into? Either of those > should make a transaction fail, and they're just selects. Yes, but I think it should still work the same as if it had failed in an explicit transaction if autocommit is false (or was that directed at someone else).
-
Re: problem with new autocommit config parameter and jdbc
scott.marlowe <scott.marlowe@ihs.com> — 2002-09-10T19:50:44Z
On Tue, 10 Sep 2002, Stephan Szabo wrote: > On Tue, 10 Sep 2002, scott.marlowe wrote: > > > On Tue, 10 Sep 2002, Stephan Szabo wrote: > > > > > > > > > It starts a transaction, failes the first command and goes into the > > > > > > > error has occurred in this transaction state. Seems like reasonable > > > > > > > behavior. > > > > > > > > > > > > Select command don't start transaction - it is not good > > > > > > > > > > I think you need more justification than "it is not good." If I do a > > > > > sequence of select statements in autocommit=false, I'd expect the same > > > > > consistancy as if I'd done > > > > > begin; > > > > > select ...; > > > > > select ...; > > > > > > > > > Ok.You start transaction explicit and this is ok. > > > > But simple SELECT don't start transaction. > > > > > > Actually someone post a bit from Date's book that implies it does. > > > And, that's still not an justification, it's just a restating of same > > > position. I don't see any reason why the two should be different from > > > a data consistency standpoint, there might be one, but you haven't > > > given any reasons. > > > > What if it's a select for update? IF that failed because of a timout on a > > lock, shouldn't the transaction fail? Or a select into? Either of those > > should make a transaction fail, and they're just selects. > > Yes, but I think it should still work the same as if it had failed in an > explicit transaction if autocommit is false (or was that directed at > someone else). Sorry, I was agreeing with you, and disagreeing with the guy who was saying that selects shouldn't start a transaction. Should have mentioned that. :-)
-
Re: [JDBC] problem with new autocommit config parameter and jdbc
Tom Lane <tgl@sss.pgh.pa.us> — 2002-09-10T19:55:57Z
Bruce Momjian <pgman@candle.pha.pa.us> writes: > That seems messy. What you are saying is that if autocommit is off, > then in: > SET x=1; > UPDATE ... > SET y=2; > ROLLBACK; > that the x=1 doesn't get rolled back bu the y=2 does? Yes, if you weren't in a transaction at the start. > I can't see any good logic for that. How about "the SQL spec requires it"? Date seems to think it does, at least for some variables (of course we have lots of variables that are not in the spec). I can't find anything very clear in the SQL92 or SQL99 documents, and I'm not at home at the moment to look at my copy of Date, but if Curt's reading is correct then we have spec precedent for acting this way. regards, tom lane
-
Re: [JDBC] problem with new autocommit config parameter and
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-10T20:00:26Z
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > That seems messy. What you are saying is that if autocommit is off, > > then in: > > > SET x=1; > > UPDATE ... > > SET y=2; > > ROLLBACK; > > > that the x=1 doesn't get rolled back bu the y=2 does? > > Yes, if you weren't in a transaction at the start. > > > I can't see any good logic for that. > > How about "the SQL spec requires it"? Date seems to think it does, > at least for some variables (of course we have lots of variables > that are not in the spec). > > I can't find anything very clear in the SQL92 or SQL99 documents, > and I'm not at home at the moment to look at my copy of Date, but > if Curt's reading is correct then we have spec precedent for acting > this way. Spec or not, it looks pretty weird so I would question following the spec on this one. Do we want to say "With autocommit off, SET will be in it's own transaction if it appears before any non-SET command", and "SETs are rolled back except if autocommit off and they appear before any non-SET"? I sure don't. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: [JDBC] problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T20:49:38Z
On Tuesday 10 September 2002 09:55 pm, Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > > That seems messy. What you are saying is that if autocommit is off, > > then in: > > > > SET x=1; > > UPDATE ... > > SET y=2; > > ROLLBACK; > > > > that the x=1 doesn't get rolled back bu the y=2 does? > > Yes, if you weren't in a transaction at the start. > > > I can't see any good logic for that. > > How about "the SQL spec requires it"? Date seems to think it does, > at least for some variables (of course we have lots of variables > that are not in the spec). > > I can't find anything very clear in the SQL92 or SQL99 documents, > and I'm not at home at the moment to look at my copy of Date, but > if Curt's reading is correct then we have spec precedent for acting > this way. I know what Oracle do (default mode autocommit off except JDBC) : only DML and DDL command start transaction and DDL command end transaction. There is another problem: if select start transaction why error - I will continue transaction. Why invalid command start transaction ? regards haris peco
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T20:49:40Z
On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: > On Tue, 10 Sep 2002, Stephan Szabo wrote: > > > > > > It starts a transaction, failes the first command and goes into > > > > > > the error has occurred in this transaction state. Seems like > > > > > > reasonable behavior. > > > > > > > > > > Select command don't start transaction - it is not good > > > > > > > > I think you need more justification than "it is not good." If I do a > > > > sequence of select statements in autocommit=false, I'd expect the > > > > same consistancy as if I'd done > > > > begin; > > > > select ...; > > > > select ...; > > > > > > Ok.You start transaction explicit and this is ok. > > > But simple SELECT don't start transaction. > > > > Actually someone post a bit from Date's book that implies it does. > > And, that's still not an justification, it's just a restating of same > > position. I don't see any reason why the two should be different from > > a data consistency standpoint, there might be one, but you haven't > > given any reasons. > > What if it's a select for update? IF that failed because of a timout on a > lock, shouldn't the transaction fail? Or a select into? Either of those > should make a transaction fail, and they're just selects. Ok.Any lock or update,delete, insert (and all ddl command) start transaction (select for update, too), but simple select no.Select don't change data and no transaction - this process cannot lost consistency (any command with error too). And if transaction start, so what ... I will (maybe) continue transaction (I don't end transaction), but I get error. and I must end transaction I think that we must parse command, choose if 'start transaction' and start transaction or no. regards Haris Peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T21:50:52Z
On Tue, 10 Sep 2002, snpe wrote: > On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: > > What if it's a select for update? IF that failed because of a timout on a > > lock, shouldn't the transaction fail? Or a select into? Either of those > > should make a transaction fail, and they're just selects. > Ok.Any lock or update,delete, insert (and all ddl command) start transaction > (select for update, too), but simple select no.Select don't change data and no > transaction - this process cannot lost consistency (any command with error > too). At least in serializable isolation level you'll probably get different results if a transaction commits between those two selects based on whether a transaction is started or not. Should two serializable selects in the same session see the same snapshot when autocommit is off?
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-10T23:25:10Z
On Wed, 11 Sep 2002, snpe wrote: > On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote: > > On Tue, 10 Sep 2002, snpe wrote: > > > On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: > > > > What if it's a select for update? IF that failed because of a timout > > > > on a lock, shouldn't the transaction fail? Or a select into? Either > > > > of those should make a transaction fail, and they're just selects. > > > > > > Ok.Any lock or update,delete, insert (and all ddl command) start > > > transaction (select for update, too), but simple select no.Select don't > > > change data and no transaction - this process cannot lost consistency > > > (any command with error too). > > > > At least in serializable isolation level you'll probably get different > > results if a transaction commits between those two selects based on > > whether a transaction is started or not. Should two serializable selects > > in the same session see the same snapshot when autocommit is off? > It is session, not transaction.My select don't change data and this is not > transaction. We're going around in circles. Does it matter if data is changed? I don't think so, since at least in serializable isolation level the snapshot that is seen depends on whether you're in a transaction or not, and given autocommit=off I believe that you should get a consistent snapshot between them. If you believe it should matter, you need to give a reason. I don't think it's a spec reason given that my sql92 spec draft says: "The following SQL-statements are transaction initiating SQL- statements, i.e., if there is no current transaction, and a statement of this class is executed, a transaction is initiated: ... o <select statement: single row> o <direct select statement: multiple rows>" unless it changed. There might be a compatibility reason, if so, with what and is it stronger than reasons to start a transaction. There might be another logical reason, if so, what is it and why does it matter? > My abother question, agian : why error (bad typing) start transaction ? That depends. Given the way the spec is worded, it says nothing about other statements, so we need to decide those ourselves. I don't see anything that implies that a select statement that errors would be any different than a select statement that doesn't as far as starting a transaction goes in my sql92 spec draft. If you were to type in foo as a command, I could see a case that maybe that shouldn't be transaction initiating, but afair that wasn't the case you had, you had a select command against an invalid table name. -
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T23:30:56Z
On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote: > On Tue, 10 Sep 2002, snpe wrote: > > On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: > > > What if it's a select for update? IF that failed because of a timout > > > on a lock, shouldn't the transaction fail? Or a select into? Either > > > of those should make a transaction fail, and they're just selects. > > > > Ok.Any lock or update,delete, insert (and all ddl command) start > > transaction (select for update, too), but simple select no.Select don't > > change data and no transaction - this process cannot lost consistency > > (any command with error too). > > At least in serializable isolation level you'll probably get different > results if a transaction commits between those two selects based on > whether a transaction is started or not. Should two serializable selects > in the same session see the same snapshot when autocommit is off? It is session, not transaction.My select don't change data and this is not transaction. My abother question, agian : why error (bad typing) start transaction ? regards haris peco
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-10T23:57:31Z
On Wednesday 11 September 2002 01:25 am, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > On Tuesday 10 September 2002 11:50 pm, Stephan Szabo wrote: > > > On Tue, 10 Sep 2002, snpe wrote: > > > > On Tuesday 10 September 2002 07:46 pm, scott.marlowe wrote: > > > > > What if it's a select for update? IF that failed because of a > > > > > timout on a lock, shouldn't the transaction fail? Or a select > > > > > into? Either of those should make a transaction fail, and they're > > > > > just selects. > > > > > > > > Ok.Any lock or update,delete, insert (and all ddl command) start > > > > transaction (select for update, too), but simple select no.Select > > > > don't change data and no transaction - this process cannot lost > > > > consistency (any command with error too). > > > > > > At least in serializable isolation level you'll probably get different > > > results if a transaction commits between those two selects based on > > > whether a transaction is started or not. Should two serializable > > > selects in the same session see the same snapshot when autocommit is > > > off? > > > > It is session, not transaction.My select don't change data and this is > > not transaction. > > We're going around in circles. > > Does it matter if data is changed? I don't think so, since at least in > serializable isolation level the snapshot that is seen depends on whether > you're in a transaction or not, and given autocommit=off I believe that > you should get a consistent snapshot between them. > > If you believe it should matter, you need to give a reason. I don't > think it's a spec reason given that my sql92 spec draft says: > > "The following SQL-statements are transaction initiating SQL- > statements, i.e., if there is no current transaction, and a > statement of this class is executed, a transaction is initiated: > ... > o <select statement: single row> > > o <direct select statement: multiple rows>" > unless it changed. > > There might be a compatibility reason, if so, with what and is it stronger > than reasons to start a transaction. > > There might be another logical reason, if so, what is it and why does > it matter? > > > My abother question, agian : why error (bad typing) start transaction ? > > That depends. Given the way the spec is worded, it says nothing about > other statements, so we need to decide those ourselves. I don't see > anything that implies that a select statement that errors would be > any different than a select statement that doesn't as far as starting > a transaction goes in my sql92 spec draft. If you were to type in > foo as a command, I could see a case that maybe that shouldn't be > transaction initiating, but afair that wasn't the case you had, you > had a select command against an invalid table name. yes, we're going around in circles. Ok.I agreed (I think because Oracle do different) Transaction start I type invalid command I correct command I get error Why.If is it transactin, why I get error I want continue. I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) It is not matter for me transaction or not.I get error for correct command after invalid I am sorry if I am confused.English is not my language. regards Haris Peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T00:09:08Z
On Wed, 11 Sep 2002, snpe wrote: > yes, we're going around in circles. > > Ok.I agreed (I think because Oracle do different) > Transaction start > I type invalid command > I correct command > I get error > > Why.If is it transactin, why I get error > I want continue. > I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) Right, that's a separate issue (I alluded to it earlier, but wasn't sure that's what you were interested in). PostgreSQL treats all errors as unrecoverable. It may be a little loose about immediately rolling back due to the fact that historically autocommit was on and it seemed better to not go into autocommit mode after the error. I doubt that 7.3 is going to change that behavior, but a case might be made that when autocommit is off the error immediately causes a rollback and new transaction will start upon the next statement (that would normally start a transaction). At some point in the future, you'll probably be able to do nested transactions or savepoints or error recovery and this will all be moot. > It is not matter for me transaction or not.I get error for correct command > after invalid
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T01:07:00Z
On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > yes, we're going around in circles. > > > > Ok.I agreed (I think because Oracle do different) > > Transaction start > > I type invalid command > > I correct command > > I get error > > > > Why.If is it transactin, why I get error > > I want continue. > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) > > Right, that's a separate issue (I alluded to it earlier, but wasn't sure > that's what you were interested in). PostgreSQL treats all errors as > unrecoverable. It may be a little loose about immediately rolling back > due to the fact that historically autocommit was on and it seemed better > to not go into autocommit mode after the error. > > I doubt that 7.3 is going to change that behavior, but a case might be > made that when autocommit is off the error immediately causes a rollback > and new transaction will start upon the next statement (that would > normally start a transaction). > Why rollback.This is error (typing error).Nothing happen. I think that we need clear set : what is start transaction ? I think that transaction start with change data in database (what don't change data this start not transaction. Oracle dot this and I think that is correct)) P.S when I can find SQL 99 specification ? regards
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T01:14:22Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > On Wed, 11 Sep 2002, snpe wrote: > > > yes, we're going around in circles. > > > > > > Ok.I agreed (I think because Oracle do different) > > > Transaction start > > > I type invalid command > > > I correct command > > > I get error > > > > > > Why.If is it transactin, why I get error > > > I want continue. > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't sure > > that's what you were interested in). PostgreSQL treats all errors as > > unrecoverable. It may be a little loose about immediately rolling back > > due to the fact that historically autocommit was on and it seemed better > > to not go into autocommit mode after the error. > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > made that when autocommit is off the error immediately causes a rollback > > and new transaction will start upon the next statement (that would > > normally start a transaction). > > > > Why rollback.This is error (typing error).Nothing happen. Postgresql currently has no real notion of a recoverable error. In the case of the error you had, probably nothing bad would happen if it continued, but what if that was a unique constraint violation? Continuing would currently probably let you see the table in an invalid state. > I think that we need clear set : what is start transaction ? > I think that transaction start with change data in database > (what don't change data this start not transaction. > Oracle dot this and I think that is correct)) I disagree because I think that two serializable select statements in autocommit=off (without a commit or rollback of course) should see the same snapshot. I'm trying to find something either way in a pdf copy of sql99. The multiple row select has gotten hidden somewhere, so it's possible that it's not, but all of opening a cursor, fetching from a cursor and the single row select syntax are labeled as transaction initiating.
-
Re: problem with new autocommit config parameter and jdbc
Curt Sampson <cjs@cynic.net> — 2002-09-11T01:44:39Z
On Tue, 10 Sep 2002, Tom Lane wrote: > As of CVS tip, SET commands *do* initiate transactions > if you have autocommit off. By your reading of Date, this is not > spec compliant for certain SET variables: a SET not already within > a transaction should not start a transaction block, at least for the > variables mentioned above. It occurs to me that it'd be reasonable > to make it act that way for all SET variables. I agree. SET variables are normally related to the behaviour of a session, not information stored in the database. And your autocommit example shows why having them start a transaction is a problem. But there were some issues with rolling back and SET commands, weren't there? I remember a long discussion about this that I'm not sure I want to go back to. :-) > > Nor, of course, are the nonexecutable statements DECLARE CURSOR, > > DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE > > SECTIONS, and WHENEVER. > > Hmm. I think the spec's notion of DECLARE must be different from ours. > Our implementation of DECLARE CURSOR both declares and opens the cursor, > and as such it *must* be transaction-initiating; else it's useless. Well, I'm not going to go chase it down right now, but ISTR that DECLAREing a cursor just allocates a variable name or the storage for it or something like that; it doesn't actually create an active cursor. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: [JDBC] problem with new autocommit config parameter
Curt Sampson <cjs@cynic.net> — 2002-09-11T01:53:09Z
On Tue, 10 Sep 2002, Barry Lind wrote: > I am waiting for this thread to conclude before deciding exactly what to > do for the jdbc driver for 7.3. While using the 'set autocommit true' > syntax is nice when talking to a 7.3 server, the jdbc driver also needs > to be backwardly compatible with 7.2 and 7.1 servers. Can you not check the server's version on connect? It would be ideal if the JDBC driver, without modification, ran all tests properly against 7.3, 7.2 and 7.1. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: [JDBC] problem with new autocommit config parameter
Curt Sampson <cjs@cynic.net> — 2002-09-11T01:57:51Z
On Tue, 10 Sep 2002, Bruce Momjian wrote: > Do we want to say "With autocommit off, SET will be in it's own > transaction if it appears before any non-SET command", and "SETs are > rolled back except if autocommit off and they appear before any > non-SET"? Not really, I don't think. But I'm starting to wonder if we should re-think all SET commands being rolled back if a transaction fails. Some don't seem to make sense, such as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: [JDBC] problem with new autocommit config parameter
Bruce Momjian <pgman@candle.pha.pa.us> — 2002-09-11T02:12:31Z
Curt Sampson wrote: > On Tue, 10 Sep 2002, Bruce Momjian wrote: > > > Do we want to say "With autocommit off, SET will be in it's own > > transaction if it appears before any non-SET command", and "SETs are > > rolled back except if autocommit off and they appear before any > > non-SET"? > > Not really, I don't think. > > But I'm starting to wonder if we should re-think all SET commands being > rolled back if a transaction fails. Some don't seem to make sense, such > as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back. Yes, but the question is whether it is better to be consistent and roll them all back, or to pick and choose which ones to roll back. Consistency is nice. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
-
Re: problem with new autocommit config parameter and jdbc
Rod Taylor <rbt@rbt.ca> — 2002-09-11T02:17:53Z
On Tue, 2002-09-10 at 21:44, Curt Sampson wrote: > But there were some issues with rolling back and SET commands, > weren't there? I remember a long discussion about this that I'm > not sure I want to go back to. :-) So.. Unless explicitly requested, a SET command should have immediate effect? The other constrictive value I can think of is search_path. -- Must be transaction safe BEGIN; CREATE SCHEMA <newschema>; SET search_path = <newschema>; ROLLBACK; CREATE TABLE... -- This should be ok BEGIN; SET autocommit = on; INSERT ... COMMIT; -- SET takes place on commit, as it was an explicit transaction -- This is requested behavior SET autocommit = off; SET autocommit = on; INSERT... -- immediate effect, since autocommit is on -- This gets interesting be ok as the schema must exist SET autocommit = off; CREATE SCHEMA <newschema>; SET search_path = <newschema>; -- implicit commit here? ROLLBACK; CREATE TABLE ... -- search_path must roll back or schema must have been created -- Similar to the above SET autocommit = off; CREATE TABLE ... SET autocommit = on; -- implicit commit here? ROLLBACK; -- Does this rollback anything? -- Was CREATE TABLE committed with the second SET statement? > Well, I'm not going to go chase it down right now, but ISTR that > DECLAREing a cursor just allocates a variable name or the storage for it > or something like that; it doesn't actually create an active cursor. Indeed, this is how the cursor is able to cross transactions. It is closed at transaction commit, and re-created in next use. 4.29: For every <declare cursor> in an SQL-client module, a cursor is effectively created when an SQLtransaction (see Subclause 4.32, SQL-transactions ) referencing the SQL-client module is initiated. -- Rod Taylor
-
Re: [JDBC] problem with new autocommit config parameter
Barry Lind <barry@xythos.com> — 2002-09-11T02:48:51Z
Curt, Yes I can check the server version on connect. In fact that is what the driver already does. However I can't check the version and then based on the version call set autocommit true in one round trip to the server. Since many people don't use connection pools, I am reluctant to add the overhead of an extra roundtrip to the database to set a variable that for most people will already be set to true. It would be ideal if I could in one hit to the database determine the server version and conditionally call set autocommit based on the version at the same time. thanks, --Barry Curt Sampson wrote: > On Tue, 10 Sep 2002, Barry Lind wrote: > > >>I am waiting for this thread to conclude before deciding exactly what to >>do for the jdbc driver for 7.3. While using the 'set autocommit true' >>syntax is nice when talking to a 7.3 server, the jdbc driver also needs >>to be backwardly compatible with 7.2 and 7.1 servers. > > > Can you not check the server's version on connect? > > It would be ideal if the JDBC driver, without modification, ran > all tests properly against 7.3, 7.2 and 7.1. > > cjs
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T02:58:09Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > On Wed, 11 Sep 2002, snpe wrote: > > > yes, we're going around in circles. > > > > > > Ok.I agreed (I think because Oracle do different) > > > Transaction start > > > I type invalid command > > > I correct command > > > I get error > > > > > > Why.If is it transactin, why I get error > > > I want continue. > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL Server) > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't sure > > that's what you were interested in). PostgreSQL treats all errors as > > unrecoverable. It may be a little loose about immediately rolling back > > due to the fact that historically autocommit was on and it seemed better > > to not go into autocommit mode after the error. > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > made that when autocommit is off the error immediately causes a rollback > > and new transaction will start upon the next statement (that would > > normally start a transaction). > > > > Why rollback.This is error (typing error).Nothing happen. > I think that we need clear set : what is start transaction ? > I think that transaction start with change data in database > (what don't change data this start not transaction. Another interesting case for a select is, what about select func(x) from table; Does func() have any side effects that might change data? At what point do we decide that the statement needs a transaction?
-
Re: [JDBC] problem with new autocommit config parameter
Curt Sampson <cjs@cynic.net> — 2002-09-11T03:06:31Z
On Tue, 10 Sep 2002, Barry Lind wrote: > Yes I can check the server version on connect. In fact that is what the > driver already does. However I can't check the version and then based > on the version call set autocommit true in one round trip to the server. > Since many people don't use connection pools, I am reluctant to add > the overhead of an extra roundtrip to the database to set a variable > that for most people will already be set to true. It would be ideal if > I could in one hit to the database determine the server version and > conditionally call set autocommit based on the version at the same time. Hmm. I don't think that there's any real way to avoid a second round trip now, but one thing we might do with 7.3 would be to add a standard stored procedure that will deal with setting appropriate variables and suchlike, and returning the version number and any other information that the JDBC driver needs. (Maybe it can return a key/value table.) That way, once we desupport 7.2 in the far future, we can reduce this to one round trip. Or perhaps we we could try to execute that stored procedure and, if it fails, create it. (Or, if creating it fails, do things the hard way.) That way the first connection you make where the SP is not there you have the overhead of adding it, but all connections after that can use it. (I assume you'd grant all rights to it to the general public.) And it could return its own version so that newer drivers could upgrade it if necessary. Or maybe just have a differently-named one for each version of the driver. This is a bit kludgy, but also sort of elegant, if you think about it.... On the other hand, perhaps we should just live with two round trips. So long as we've got command batching at some point, we can get the version, and then send all the setup commands we need as a single batch after that. cjs -- Curt Sampson <cjs@cynic.net> +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC -
Re: problem with new autocommit config parameter and jdbc
Rod Taylor <rbt@rbt.ca> — 2002-09-11T12:38:44Z
> > > Why rollback.This is error (typing error).Nothing happen. > > > I think that we need clear set : what is start transaction ? > > > I think that transaction start with change data in database > > > (what don't change data this start not transaction. > > > > Another interesting case for a select is, what about > > select func(x) from table; > > Does func() have any side effects that might change data? > > At what point do we decide that the statement needs a > > transaction? > Function in select list mustn't change any data. > What if function change data in from clause ? Why can't the function change data? I've done this one a number of times through views to log the user pulling out information from the system, and what it was at the time (time sensitive data). -- Rod Taylor
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T12:43:03Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 04:58 am, Stephan Szabo wrote: > > On Wed, 11 Sep 2002, snpe wrote: > > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > > > On Wed, 11 Sep 2002, snpe wrote: > > > > > yes, we're going around in circles. > > > > > > > > > > Ok.I agreed (I think because Oracle do different) > > > > > Transaction start > > > > > I type invalid command > > > > > I correct command > > > > > I get error > > > > > > > > > > Why.If is it transactin, why I get error > > > > > I want continue. > > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL > > > > > Server) > > > > > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't > > > > sure that's what you were interested in). PostgreSQL treats all errors > > > > as unrecoverable. It may be a little loose about immediately rolling > > > > back due to the fact that historically autocommit was on and it seemed > > > > better to not go into autocommit mode after the error. > > > > > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > > > made that when autocommit is off the error immediately causes a > > > > rollback and new transaction will start upon the next statement (that > > > > would normally start a transaction). > > > > > > Why rollback.This is error (typing error).Nothing happen. > > > I think that we need clear set : what is start transaction ? > > > I think that transaction start with change data in database > > > (what don't change data this start not transaction. > > > > Another interesting case for a select is, what about > > select func(x) from table; > > Does func() have any side effects that might change data? > > At what point do we decide that the statement needs a > > transaction? > Function in select list mustn't change any data. > What if function change data in from clause ? There is no such restriction. The behavior is not necessarily well defined in all cases, but postgresql certainly doesn't require that the functions not change data especially given that postgresql takes: select func(); as the way to call to func(); Example session from 7.3 just pre-beta included below. ---- sszabo=# create table b(a int); CREATE TABLE sszabo=# create table a(a int); CREATE TABLE sszabo=# create function f(int) returns int as 'insert into b values ($1); select $1;' language 'sql'; CREATE FUNCTION sszabo=# insert into a values (1); INSERT 17010 1 sszabo=# select f(a) from a; f --- 1 (1 row) sszabo=# select * from b; a --- 1 (1 row)
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T12:55:32Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 03:14 am, Stephan Szabo wrote: > > On Wed, 11 Sep 2002, snpe wrote: > > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > > > On Wed, 11 Sep 2002, snpe wrote: > > > > > yes, we're going around in circles. > > > > > > > > > > Ok.I agreed (I think because Oracle do different) > > > > > Transaction start > > > > > I type invalid command > > > > > I correct command > > > > > I get error > > > > > > > > > > Why.If is it transactin, why I get error > > > > > I want continue. > > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL > > > > > Server) > > > > > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't > > > > sure that's what you were interested in). PostgreSQL treats all errors > > > > as unrecoverable. It may be a little loose about immediately rolling > > > > back due to the fact that historically autocommit was on and it seemed > > > > better to not go into autocommit mode after the error. > > > > > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > > > made that when autocommit is off the error immediately causes a > > > > rollback and new transaction will start upon the next statement (that > > > > would normally start a transaction). > > > > > > Why rollback.This is error (typing error).Nothing happen. > > > > Postgresql currently has no real notion of a recoverable error. > > In the case of the error you had, probably nothing bad would happen > > if it continued, but what if that was a unique constraint violation? > > Continuing would currently probably let you see the table in an > > invalid state. > > > If decision (transaction or not) is after parser (before execute) this isn't > problem. > I don't know when postgresql make decision, but that is best after parser. > I parser find error simple return error and nothing happen Are you saying that it's okay for: insert into nonexistant values (3); and insert into existant values (3); where 3 is invalid for existant to work differently? I think that'd be tough to get past some people, but you might want to write a proposal for why it should act that way. (Don't expect anything for 7.3, but 7.4's devel will start sometime.) > > > I think that we need clear set : what is start transaction ? > > > I think that transaction start with change data in database > > > (what don't change data this start not transaction. > > > Oracle dot this and I think that is correct)) > > > > I disagree because I think that two serializable select statements > > in autocommit=off (without a commit or rollback of course) should > > see the same snapshot. > > > Question ? > All select in one transaction return same data - no matter if any change and > commit data ? It depends on the isolation level of the transaction I believe. This sequence in read committed (in postgresql) and serializable give different results. T1: begin; T1: select * from a; T2: begin; T2: insert into a values (3); T2: commit; T1: select * from a; In serializable mode, you can't get "non-repeatable read" effects: SQL-transaction T1 reads a row. SQL-transaction T2 then modifies or deletes that row and performs a COMMIT. If T1 then attempts to reread the row, it may receive the modified value of discover that the row has been deleted.
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T12:56:09Z
On Wednesday 11 September 2002 04:58 am, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > > On Wed, 11 Sep 2002, snpe wrote: > > > > yes, we're going around in circles. > > > > > > > > Ok.I agreed (I think because Oracle do different) > > > > Transaction start > > > > I type invalid command > > > > I correct command > > > > I get error > > > > > > > > Why.If is it transactin, why I get error > > > > I want continue. > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL > > > > Server) > > > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't > > > sure that's what you were interested in). PostgreSQL treats all errors > > > as unrecoverable. It may be a little loose about immediately rolling > > > back due to the fact that historically autocommit was on and it seemed > > > better to not go into autocommit mode after the error. > > > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > > made that when autocommit is off the error immediately causes a > > > rollback and new transaction will start upon the next statement (that > > > would normally start a transaction). > > > > Why rollback.This is error (typing error).Nothing happen. > > I think that we need clear set : what is start transaction ? > > I think that transaction start with change data in database > > (what don't change data this start not transaction. > > Another interesting case for a select is, what about > select func(x) from table; > Does func() have any side effects that might change data? > At what point do we decide that the statement needs a > transaction? Function in select list mustn't change any data. What if function change data in from clause ?
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T13:02:23Z
On Wednesday 11 September 2002 03:14 am, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > > On Wed, 11 Sep 2002, snpe wrote: > > > > yes, we're going around in circles. > > > > > > > > Ok.I agreed (I think because Oracle do different) > > > > Transaction start > > > > I type invalid command > > > > I correct command > > > > I get error > > > > > > > > Why.If is it transactin, why I get error > > > > I want continue. > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL > > > > Server) > > > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't > > > sure that's what you were interested in). PostgreSQL treats all errors > > > as unrecoverable. It may be a little loose about immediately rolling > > > back due to the fact that historically autocommit was on and it seemed > > > better to not go into autocommit mode after the error. > > > > > > I doubt that 7.3 is going to change that behavior, but a case might be > > > made that when autocommit is off the error immediately causes a > > > rollback and new transaction will start upon the next statement (that > > > would normally start a transaction). > > > > Why rollback.This is error (typing error).Nothing happen. > > Postgresql currently has no real notion of a recoverable error. > In the case of the error you had, probably nothing bad would happen > if it continued, but what if that was a unique constraint violation? > Continuing would currently probably let you see the table in an > invalid state. > If decision (transaction or not) is after parser (before execute) this isn't problem. I don't know when postgresql make decision, but that is best after parser. I parser find error simple return error and nothing happen > > I think that we need clear set : what is start transaction ? > > I think that transaction start with change data in database > > (what don't change data this start not transaction. > > Oracle dot this and I think that is correct)) > > I disagree because I think that two serializable select statements > in autocommit=off (without a commit or rollback of course) should > see the same snapshot. > Question ? All select in one transaction return same data - no matter if any change and commit data ? > I'm trying to find something either way in a pdf copy of sql99. > The multiple row select has gotten hidden somewhere, so it's possible > that it's not, but all of opening a cursor, fetching from a cursor > and the single row select syntax are labeled as transaction initiating. Can I find sql99 spec anywhere ? Thanks
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T13:06:57Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 02:38 pm, Rod Taylor wrote: > > > > > Why rollback.This is error (typing error).Nothing happen. > > > > > I think that we need clear set : what is start transaction ? > > > > > I think that transaction start with change data in database > > > > > (what don't change data this start not transaction. > > > > > > > > Another interesting case for a select is, what about > > > > select func(x) from table; > > > > Does func() have any side effects that might change data? > > > > At what point do we decide that the statement needs a > > > > transaction? > > > > > > Function in select list mustn't change any data. > > > What if function change data in from clause ? > > > > Why can't the function change data? I've done this one a number of > > times through views to log the user pulling out information from the > > system, and what it was at the time (time sensitive data). > Scenario : > Func change data in table in form clause > I fetch 3 (after row 1 and 2) and then change row 1 > What result expect ? Just because the behavior is sometimes undefined by the spec doesn't mean the construct should be disallowed. Grouped character string columns also could have implementation-dependent behavior (which never needs to be specified), but we don't disallow that either.
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T13:13:20Z
On Wednesday 11 September 2002 02:38 pm, Rod Taylor wrote: > > > > Why rollback.This is error (typing error).Nothing happen. > > > > I think that we need clear set : what is start transaction ? > > > > I think that transaction start with change data in database > > > > (what don't change data this start not transaction. > > > > > > Another interesting case for a select is, what about > > > select func(x) from table; > > > Does func() have any side effects that might change data? > > > At what point do we decide that the statement needs a > > > transaction? > > > > Function in select list mustn't change any data. > > What if function change data in from clause ? > > Why can't the function change data? I've done this one a number of > times through views to log the user pulling out information from the > system, and what it was at the time (time sensitive data). Scenario : Func change data in table in form clause I fetch 3 (after row 1 and 2) and then change row 1 What result expect ?
-
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T13:45:54Z
On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > On Wednesday 11 September 2002 03:14 am, Stephan Szabo wrote: > > > On Wed, 11 Sep 2002, snpe wrote: > > > > On Wednesday 11 September 2002 02:09 am, Stephan Szabo wrote: > > > > > On Wed, 11 Sep 2002, snpe wrote: > > > > > > yes, we're going around in circles. > > > > > > > > > > > > Ok.I agreed (I think because Oracle do different) > > > > > > Transaction start > > > > > > I type invalid command > > > > > > I correct command > > > > > > I get error > > > > > > > > > > > > Why.If is it transactin, why I get error > > > > > > I want continue. > > > > > > I am see this error with JDeveloper (work with Oracle, DB2 an SQL > > > > > > Server) > > > > > > > > > > Right, that's a separate issue (I alluded to it earlier, but wasn't > > > > > sure that's what you were interested in). PostgreSQL treats all > > > > > errors as unrecoverable. It may be a little loose about > > > > > immediately rolling back due to the fact that historically > > > > > autocommit was on and it seemed better to not go into autocommit > > > > > mode after the error. > > > > > > > > > > I doubt that 7.3 is going to change that behavior, but a case might > > > > > be made that when autocommit is off the error immediately causes a > > > > > rollback and new transaction will start upon the next statement > > > > > (that would normally start a transaction). > > > > > > > > Why rollback.This is error (typing error).Nothing happen. > > > > > > Postgresql currently has no real notion of a recoverable error. > > > In the case of the error you had, probably nothing bad would happen > > > if it continued, but what if that was a unique constraint violation? > > > Continuing would currently probably let you see the table in an > > > invalid state. > > > > If decision (transaction or not) is after parser (before execute) this > > isn't problem. > > I don't know when postgresql make decision, but that is best after > > parser. I parser find error simple return error and nothing happen > > Are you saying that it's okay for: > insert into nonexistant values (3); > and > insert into existant values (3); > where 3 is invalid for existant to work > differently? > I think that'd be tough to get past some people, but you might > want to write a proposal for why it should act that way. (Don't > expect anything for 7.3, but 7.4's devel will start sometime.) > I don't understand all, but when I tell 'error' I think "syntax error" If error is contraint error again nothin change, only error return > > > > I think that we need clear set : what is start transaction ? > > > > I think that transaction start with change data in database > > > > (what don't change data this start not transaction. > > > > Oracle dot this and I think that is correct)) > > > > > > I disagree because I think that two serializable select statements > > > in autocommit=off (without a commit or rollback of course) should > > > see the same snapshot. > > > > Question ? > > All select in one transaction return same data - no matter if any change > > and commit data ? > > It depends on the isolation level of the transaction I believe. > This sequence in read committed (in postgresql) and serializable give > different results. > > T1: begin; > T1: select * from a; > T2: begin; > T2: insert into a values (3); > T2: commit; > T1: select * from a; > > In serializable mode, you can't get "non-repeatable read" effects: > SQL-transaction T1 reads a row. SQL-transaction T2 then modifies > or deletes that row and performs a COMMIT. If T1 then attempts to > reread the row, it may receive the modified value of discover that the > row has been deleted. If serialization strict connect with transaction then ok. haris peco
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T16:11:19Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote: > > On Wed, 11 Sep 2002, snpe wrote: > > > > > > If decision (transaction or not) is after parser (before execute) this > > > isn't problem. > > > I don't know when postgresql make decision, but that is best after > > > parser. I parser find error simple return error and nothing happen > > > > Are you saying that it's okay for: > > insert into nonexistant values (3); > > and > > insert into existant values (3); > > where 3 is invalid for existant to work > > differently? > > I think that'd be tough to get past some people, but you might > > want to write a proposal for why it should act that way. (Don't > > expect anything for 7.3, but 7.4's devel will start sometime.) > > > I don't understand all, but when I tell 'error' I think "syntax error" > If error is contraint error again nothin change, only error return I don't understand what you mean here. Are you saying that both of those queries should not start transactions? Then that wouldn't be starting between the parser and execute since you won't know that the row violates a constraint until execution time. > > > > I disagree because I think that two serializable select statements > > > > in autocommit=off (without a commit or rollback of course) should > > > > see the same snapshot. > > > > > > Question ? > > > All select in one transaction return same data - no matter if any change > > > and commit data ? > > > > It depends on the isolation level of the transaction I believe. > > This sequence in read committed (in postgresql) and serializable give > > different results. > > > > T1: begin; > > T1: select * from a; > > T2: begin; > > T2: insert into a values (3); > > T2: commit; > > T1: select * from a; > > > > In serializable mode, you can't get "non-repeatable read" effects: > > SQL-transaction T1 reads a row. SQL-transaction T2 then modifies > > or deletes that row and performs a COMMIT. If T1 then attempts to > > reread the row, it may receive the modified value of discover that the > > row has been deleted. > If serialization strict connect with transaction then ok. I again am not sure I understand, are you saying that under serializable select should start a transaction but it shouldn't under read committed? That seems like a bad idea to me, either it should or it shouldn't in my opinion. Perhaps it'd be better if you wrote up what you think it should do in all these cases and then we could look at them as a whole. (Cases I can see right now are, select under serializable, select under read committed, garbage command, select to non existant table, insert to non existant table, insert that fails due to unique constraint, insert that fails due to exception raised by a before trigger, insert that fails due to exception raised by an after trigger, insert that does nothing due to before trigger, update that fails due to any of those after some rows have already successfully been modified and probably some others).
-
Patch for getBestRowIdentifier (for testing with Oracle JDeveloper)
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T20:38:08Z
Please appl --- AbstractJdbc1DatabaseMetaData.java Wed Sep 11 22:21:25 2002 +++ AbstractJdbc1DatabaseMetaData.java.orig Wed Sep 11 22:20:36 2002 @@ -2381,44 +2381,21 @@ // Implementation note: This is required for Borland's JBuilder to work public java.sql.ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException { - if (connection.haveMinimumServerVersion("7.3")) { - StringBuffer sql = new StringBuffer(512); - sql.append("SELECT " + - scope + " as SCOPE," + - "a.attname as COLUMN_NAME," + - "a.atttypid as DATA_TYPE," + - "t.typname as TYPE_NAME," + - "t.typlen as COLUMN_SIZE," + - "0::int4 as BUFFER_LENGTH," + - "0::int4 as DECIMAL_DIGITS," + - "0::int4 as PSEUDO_COLUMN " + - "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," + - "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a " + - "WHERE bc.relkind = 'r' " + - "AND t.oid=a.atttypid " + - "AND upper(bc.relname) = upper('" + table + "') " + - "AND i.indrelid = bc.oid " + - "AND i.indexrelid = ic.oid " + - "AND ic.oid = a.attrelid " + - "AND i.indisprimary='t' "); - return connection.createStatement().executeQuery(sql.toString()); - } else { - // for now, this returns an empty result set. - Field f[] = new Field[8]; - ResultSet r; // ResultSet for the SQL query that we need to do - Vector v = new Vector(); // The new ResultSet tuple stuff + // for now, this returns an empty result set. + Field f[] = new Field[8]; + ResultSet r; // ResultSet for the SQL query that we need to do + Vector v = new Vector(); // The new ResultSet tuple stuff - f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); - f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); - f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); - f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); - f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); - f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); - f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); - f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); + f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); + f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); + f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); + f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); + f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); + f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); + f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); + f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); - return connection.getResultSet(null, f, v, "OK", 1); - } + return connection.getResultSet(null, f, v, "OK", 1); } /* -
Re: Patch for getBestRowIdentifier (for testing with Oracle JDeveloper)
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T20:52:55Z
I'am sorry (reverse *java and *orig) correct patch --- AbstractJdbc1DatabaseMetaData.java.orig Wed Sep 11 22:20:36 2002 +++ AbstractJdbc1DatabaseMetaData.java Wed Sep 11 22:50:37 2002 @@ -2381,21 +2381,44 @@ // Implementation note: This is required for Borland's JBuilder to work public java.sql.ResultSet getBestRowIdentifier(String catalog, String schema, String table, int scope, boolean nullable) throws SQLException { - // for now, this returns an empty result set. - Field f[] = new Field[8]; - ResultSet r; // ResultSet for the SQL query that we need to do - Vector v = new Vector(); // The new ResultSet tuple stuff + if (connection.haveMinimumServerVersion("7.3")) { + StringBuffer sql = new StringBuffer(512); + sql.append("SELECT " + + scope + " as SCOPE," + + "a.attname as COLUMN_NAME," + + "a.atttypid as DATA_TYPE," + + "t.typname as TYPE_NAME," + + "t.typlen as COLUMN_SIZE," + + "0::int4 as BUFFER_LENGTH," + + "0::int4 as DECIMAL_DIGITS," + + "0::int4 as PSEUDO_COLUMN " + + "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," + + "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a " + + "WHERE bc.relkind = 'r' " + + "AND t.oid=a.atttypid " + + "AND upper(bc.relname) = upper('" + table + "') " + + "AND i.indrelid = bc.oid " + + "AND i.indexrelid = ic.oid " + + "AND ic.oid = a.attrelid " + + "AND i.indisprimary='t' "); + return connection.createStatement().executeQuery(sql.toString()); + } else { + // for now, this returns an empty result set. + Field f[] = new Field[8]; + ResultSet r; // ResultSet for the SQL query that we need to do + Vector v = new Vector(); // The new ResultSet tuple stuff - f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); - f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); - f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); - f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); - f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); - f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); - f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); - f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); + f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); + f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); + f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); + f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); + f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); + f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); + f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); + f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); - return connection.getResultSet(null, f, v, "OK", 1); + return connection.getResultSet(null, f, v, "OK", 1); + } } /* -
Re: problem with new autocommit config parameter and jdbc
Haris Peco <snpe@snpe.co.yu> — 2002-09-11T21:33:56Z
On Wednesday 11 September 2002 06:11 pm, Stephan Szabo wrote: > On Wed, 11 Sep 2002, snpe wrote: > > On Wednesday 11 September 2002 02:55 pm, Stephan Szabo wrote: > > > On Wed, 11 Sep 2002, snpe wrote: > > > > If decision (transaction or not) is after parser (before execute) > > > > this isn't problem. > > > > I don't know when postgresql make decision, but that is best after > > > > parser. I parser find error simple return error and nothing happen > > > > > > Are you saying that it's okay for: > > > insert into nonexistant values (3); > > > and > > > insert into existant values (3); > > > where 3 is invalid for existant to work > > > differently? > > > I think that'd be tough to get past some people, but you might > > > want to write a proposal for why it should act that way. (Don't > > > expect anything for 7.3, but 7.4's devel will start sometime.) > > > > I don't understand all, but when I tell 'error' I think "syntax error" > > If error is contraint error again nothin change, only error return > > I don't understand what you mean here. Are you saying that both of > those queries should not start transactions? Then that wouldn't > be starting between the parser and execute since you won't know that > the row violates a constraint until execution time. > > > > > > I disagree because I think that two serializable select statements > > > > > in autocommit=off (without a commit or rollback of course) should > > > > > see the same snapshot. > > > > > > > > Question ? > > > > All select in one transaction return same data - no matter if any > > > > change and commit data ? > > > > > > It depends on the isolation level of the transaction I believe. > > > This sequence in read committed (in postgresql) and serializable give > > > different results. > > > > > > T1: begin; > > > T1: select * from a; > > > T2: begin; > > > T2: insert into a values (3); > > > T2: commit; > > > T1: select * from a; > > > > > > In serializable mode, you can't get "non-repeatable read" effects: > > > SQL-transaction T1 reads a row. SQL-transaction T2 then modifies > > > or deletes that row and performs a COMMIT. If T1 then attempts to > > > reread the row, it may receive the modified value of discover that the > > > row has been deleted. > > > > If serialization strict connect with transaction then ok. > > I again am not sure I understand, are you saying that under serializable > select should start a transaction but it shouldn't under read committed? > That seems like a bad idea to me, either it should or it shouldn't in > my opinion. > > Perhaps it'd be better if you wrote up what you think it should do in > all these cases and then we could look at them as a whole. > (Cases I can see right now are, select under serializable, select under > read committed, garbage command, select to non existant table, > insert to non existant table, insert that fails due to unique constraint, > insert that fails due to exception raised by a before trigger, > insert that fails due to exception raised by an after trigger, > insert that does nothing due to before trigger, update that fails > due to any of those after some rows have already successfully been > modified and probably some others). One question first ? What mean ? ERROR: current transaction is aborted, queries ignored until end of transaction block I am tried next (autocommit=true in postgresql.conf) 1. begin; 2. select * from tab; query work 3. show t; -- force stupid syntax error 4. select * from tab; ERROR: current transaction is aborted, queries ignored until end of transaction block 5.end; 6. select * from tab; query work I must rollback or commit transaction when I make stupid syntax error. This is same with autocommit=false It is maybe ok, I don't know. For rest is ok (if level serializable select start transaction) Thanks
-
Re: problem with new autocommit config parameter and jdbc
Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-09-11T22:03:48Z
On Wed, 11 Sep 2002, snpe wrote: > On Wednesday 11 September 2002 06:11 pm, Stephan Szabo wrote: > > > I again am not sure I understand, are you saying that under serializable > > select should start a transaction but it shouldn't under read committed? > > That seems like a bad idea to me, either it should or it shouldn't in > > my opinion. > > > > Perhaps it'd be better if you wrote up what you think it should do in > > all these cases and then we could look at them as a whole. > > (Cases I can see right now are, select under serializable, select under > > read committed, garbage command, select to non existant table, > > insert to non existant table, insert that fails due to unique constraint, > > insert that fails due to exception raised by a before trigger, > > insert that fails due to exception raised by an after trigger, > > insert that does nothing due to before trigger, update that fails > > due to any of those after some rows have already successfully been > > modified and probably some others). > > One question first ? > > What mean ? > ERROR: current transaction is aborted, queries ignored until end of > transaction block > I am tried next (autocommit=true in postgresql.conf) The transaction has encountered an unrecoverable error (remember, all errors are currently considered unrecoverable) and the transaction is in a potentially unsafe state. > 1. begin; > 2. select * from tab; > query work > 3. show t; -- force stupid syntax error > 4. select * from tab; > ERROR: current transaction is aborted, queries ignored until end of > transaction block > 5.end; > 6. select * from tab; > query work > > I must rollback or commit transaction when I make stupid syntax error. Note that even with end you get effectively a rollback in this case since the transaction as a whole ended in an error state. > This is same with autocommit=false > It is maybe ok, I don't know. Well, at least until we have savepoints or nested transactions, there's only a limited amount of freedom in the implementation. > For rest is ok (if level serializable select start transaction) Like I said above, having the transaction starting of select being dependent on the isolation level variable sounds like a bad idea. In addition that still doesn't deal with select statements with side effects.
-
Re: Patch for getBestRowIdentifier (for testing with Oracle
Kris Jurka <books@ejurka.com> — 2002-09-12T17:02:28Z
I will add a version of this patch to my work on making DatabaseMetaData schema aware. Kris Jurka On Wed, 11 Sep 2002, snpe wrote: > I'am sorry (reverse *java and *orig) > > correct patch > --- AbstractJdbc1DatabaseMetaData.java.orig Wed Sep 11 22:20:36 2002 > +++ AbstractJdbc1DatabaseMetaData.java Wed Sep 11 22:50:37 2002 > @@ -2381,21 +2381,44 @@ > // Implementation note: This is required for Borland's JBuilder to work > public java.sql.ResultSet getBestRowIdentifier(String catalog, String > schema, String table, int scope, boolean nullable) throws SQLException > { > - // for now, this returns an empty result set. > - Field f[] = new Field[8]; > - ResultSet r; // ResultSet for the SQL query that we need to do > - Vector v = new Vector(); // The new ResultSet tuple stuff > + if (connection.haveMinimumServerVersion("7.3")) { > + StringBuffer sql = new StringBuffer(512); > + sql.append("SELECT " + > + scope + " as SCOPE," + > + "a.attname as COLUMN_NAME," + > + "a.atttypid as DATA_TYPE," + > + "t.typname as TYPE_NAME," + > + "t.typlen as COLUMN_SIZE," + > + "0::int4 as BUFFER_LENGTH," + > + "0::int4 as DECIMAL_DIGITS," + > + "0::int4 as PSEUDO_COLUMN " + > + "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," + > + "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a > " + > + "WHERE bc.relkind = 'r' " + > + "AND t.oid=a.atttypid " + > + "AND upper(bc.relname) = upper('" + table + "') " + > + "AND i.indrelid = bc.oid " + > + "AND i.indexrelid = ic.oid " + > + "AND ic.oid = a.attrelid " + > + "AND i.indisprimary='t' "); > + return connection.createStatement().executeQuery(sql.toString()); > + } else { > + // for now, this returns an empty result set. > + Field f[] = new Field[8]; > + ResultSet r; // ResultSet for the SQL query that we need to do > + Vector v = new Vector(); // The new ResultSet tuple stuff > > - f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); > - f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); > - f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); > - f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); > - f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); > - f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); > - f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); > - f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); > + f[0] = new Field(connection, "SCOPE", iInt2Oid, 2); > + f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE); > + f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2); > + f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE); > + f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4); > + f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4); > + f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2); > + f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2); > > - return connection.getResultSet(null, f, v, "OK", 1); > + return connection.getResultSet(null, f, v, "OK", 1); > + } > } > > /* > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >