Re: [JDBC] [HACKERS] problem with new autocommit config parameter
Dear PostgreSQL people, Sorry for jumping into this conversation in the middle. Autocommit is very important, as appservers may turn it on or off at will in order to support EJB transactions (being able to set them up, roll them back, commit them, etc. by using the JDBC API). If it is broken, then all EJB apps using PostgreSQL may be broken also. ...This frightens me a little. Could somebody please explain? Sincerely, Daryl. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Monday, September 09, 2002 2:54 PM To: Bruce Momjian Cc: Barry Lind; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
On Tuesday 10 September 2002 09:55 pm, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
Daryl, The problem is an incompatiblity between a new server autocommit feature and the existing jdbc autocommit feature. The problem manifests itself when you turn autocommit off on the server (which is new functionality in 7.3). If you leave autocommit turned on on the server (which is the way the server has always worked until 7.3) the jdbc driver correctly handles issuing the correct begin/commit/rollback commands to support autocommit functionality in the jdbc driver. Autocommit will work with jdbc in 7.3 (and it does now as long as you leave autocommit set on in the postgresql.conf file). We are just need to decide what to do in this one corner case. thanks, --Barry Daryl Beattie wrote: Dear PostgreSQL people, Sorry for jumping into this conversation in the middle. Autocommit is very important, as appservers may turn it on or off at will in order to support EJB transactions (being able to set them up, roll them back, commit them, etc. by using the JDBC API). If it is broken, then all EJB apps using PostgreSQL may be broken also. ...This frightens me a little. Could somebody please explain? Sincerely, Daryl. -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: Monday, September 09, 2002 2:54 PM To: Bruce Momjian Cc: Barry Lind; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc Bruce Momjian [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 [EMAIL PROTECTED] +81 90 7737 2974 http://www.netbsd.org Don't you know, in this new Dark Age, we're all light. --XTC ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
Curt Sampson [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc
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 ? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [JDBC] [HACKERS] problem with new autocommit config parameter and
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 [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [JDBC] [HACKERS] problem with new autocommit config parameter
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 ? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
