Thread: JDBC gripe list
Just got back from pgeast. Seems everyone seems to have a gripe about the JDBC driver. My sense is that for basic read/write/update/delete the driver works just fine. However I'd like to compile a list of everyone's gripe. Dave
Dave Cramer wrote: > Just got back from pgeast. Seems everyone seems to have a gripe about > the JDBC driver. > > My sense is that for basic read/write/update/delete the driver works > just fine. However I'd like to compile a list of everyone's gripe. > > Dave Perhaps some elaboration on the types of gripes Dave would be more informative on what's going on. The only comment I would have to say I guess is the notice in the last year or so is an expansion of consistent contributors at the database server project. The same does not appear to be happening with the JDBC. Looks like reports are coming in and source modification request, but no real additional consistent people to help out in the day to day activities. It appears that Kris is the only one left to guardian the JDBC through from release to release. Perhaps my assessment is wrong, but when a project gets so big then it takes almost one or two people full time, or that number of man hours, to insure it stays on track with all the basic upkeep. Dana M. Proctor MyJSQLView Project Manager
On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote: > Just got back from pgeast. Seems everyone seems to have a gripe about > the JDBC driver. > > My sense is that for basic read/write/update/delete the driver works > just fine. However I'd like to compile a list of everyone's gripe. Funny that, I just got back from a different conference where people were griping about it also. Didn't get a detailed list but numbers of less impressed people all active Postgres users and supporters. JDBC4 was mentioned. Will look to collect more info. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Thu, Mar 24, 2011 at 9:38 PM, dmp <danap@ttc-cmc.net> wrote: > Dave Cramer wrote: >> >> Just got back from pgeast. Seems everyone seems to have a gripe about >> the JDBC driver. >> >> My sense is that for basic read/write/update/delete the driver works >> just fine. However I'd like to compile a list of everyone's gripe. >> >> Dave > > Perhaps some elaboration on the types of gripes Dave would be more > informative on what's going on. > > The only comment I would have to say I guess is the notice in the last > year or so is an expansion of consistent contributors at the database > server project. The same does not appear to be happening with the JDBC. > Looks like reports are coming in and source modification request, but > no real additional consistent people to help out in the day to day > activities. It appears that Kris is the only one left to guardian the > JDBC through from release to release. Perhaps my assessment is wrong, > but when a project gets so big then it takes almost one or two people > full time, or that number of man hours, to insure it stays on track > with all the basic upkeep. > > Dana M. Proctor > MyJSQLView Project Manager > There are 3 committers on the JDBC project. Currently Kris has been shouldering the load, but both Oliver and I are still here. As far as the types of gripes I heard, there was nothing really specific except perhaps statement timeout. There was one comment about "when were we going to get a good JDBC driver". So this is an attempt to build a list so that we can see the magnitude Dave
On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote: >> Just got back from pgeast. Seems everyone seems to have a gripe about >> the JDBC driver. >> >> My sense is that for basic read/write/update/delete the driver works >> just fine. However I'd like to compile a list of everyone's gripe. > > Funny that, I just got back from a different conference where people > were griping about it also. > > Didn't get a detailed list but numbers of less impressed people all > active Postgres users and supporters. > > JDBC4 was mentioned. So thinking about this. JDBC4 does nothing to really add to the 4 basic operations. What I am thinking is that many other tools require JDBC4 and as a result ours doesn't work well ? > > Will look to collect more info. Thanks! Dave http://www.credativ.ca
On 25 March 2011 23:28, Dave Cramer <pg@fastcrypt.com> wrote: > There are 3 committers on the JDBC project. Currently Kris has been > shouldering the load, but both Oliver and I are still here. FWIW I don't really have any time to work on the driver these days unfortunately - I try to answer the occasional email, but I don't have the time to do much beyond that. Oliver
Scott, Thanks I actually think this one is tractable. Dave On Fri, Mar 25, 2011 at 9:46 AM, Mackay, Scott <scott.mackay@progeny.net> wrote: > Unless it has changed, my only real gripe is the lack of batch insert capabilities. We have to do some ugly things sincewe cannot get IDs returned from batch inserts. > > -Scott > > > -----Original Message----- > From: pgsql-jdbc-owner@postgresql.org on behalf of Dave Cramer > Sent: Fri 3/25/2011 6:30 AM > To: Simon Riggs > Cc: List > Subject: (nwl) Re: [JDBC] JDBC gripe list > > On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote: >> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote: >>> Just got back from pgeast. Seems everyone seems to have a gripe about >>> the JDBC driver. >>> >>> My sense is that for basic read/write/update/delete the driver works >>> just fine. However I'd like to compile a list of everyone's gripe. >> >> Funny that, I just got back from a different conference where people >> were griping about it also. >> >> Didn't get a detailed list but numbers of less impressed people all >> active Postgres users and supporters. >> >> JDBC4 was mentioned. > > So thinking about this. JDBC4 does nothing to really add to the 4 > basic operations. What I am thinking is that many other tools require > JDBC4 and as a result ours doesn't work well ? > >> >> Will look to collect more info. > > Thanks! > > Dave > http://www.credativ.ca > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc > >
Unless it has changed, my only real gripe is the lack of batch insert capabilities. We have to do some ugly things sincewe cannot get IDs returned from batch inserts. -Scott -----Original Message----- From: pgsql-jdbc-owner@postgresql.org on behalf of Dave Cramer Sent: Fri 3/25/2011 6:30 AM To: Simon Riggs Cc: List Subject: (nwl) Re: [JDBC] JDBC gripe list On Fri, Mar 25, 2011 at 4:07 AM, Simon Riggs <simon@2ndquadrant.com> wrote: > On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer <pg@fastcrypt.com> wrote: >> Just got back from pgeast. Seems everyone seems to have a gripe about >> the JDBC driver. >> >> My sense is that for basic read/write/update/delete the driver works >> just fine. However I'd like to compile a list of everyone's gripe. > > Funny that, I just got back from a different conference where people > were griping about it also. > > Didn't get a detailed list but numbers of less impressed people all > active Postgres users and supporters. > > JDBC4 was mentioned. So thinking about this. JDBC4 does nothing to really add to the 4 basic operations. What I am thinking is that many other tools require JDBC4 and as a result ours doesn't work well ? > > Will look to collect more info. Thanks! Dave http://www.credativ.ca -- Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-jdbc
Hello, ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com> > My sense is that for basic read/write/update/delete the driver works > just fine. However I'd like to compile a list of everyone's gripe. Thank you for giving us an excellent JDBC driver. I've been learning PostgreSQL JDBC driver and PostgreSQL internals these days. Please let me add some items. 1. Better JDBC4 support I'd like the driver to support more JDBC4 features, regardless of whether they are actually useful. Just lack of support could lead to underevaluation when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB provides better JDBC4 support). I love PostgreSQL and don't want my colleagues to underevaluate PostgreSQL. 2. Ability to run regression tests against the driver located in an arbitorary place The current regression test builds the driver and uses it. I want to run the regression test against an installed driver like "make installcheck" of PostgreSQL. With that feature, I can make sure that the installation media contains a correct driver and we performed packaging procedure with no problem. Regards Maumu
On Fri, Mar 25, 2011 at 4:37 PM, MauMau <maumau307@gmail.com> wrote: > Hello, > > ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com> >> >> My sense is that for basic read/write/update/delete the driver works >> just fine. However I'd like to compile a list of everyone's gripe. > > Thank you for giving us an excellent JDBC driver. I've been learning > PostgreSQL JDBC driver and PostgreSQL internals these days. Please let me > add some items. > > 1. Better JDBC4 support > I'd like the driver to support more JDBC4 features, regardless of whether > they are actually useful. Just lack of support could lead to underevaluation > when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB > provides better JDBC4 support). I love PostgreSQL and don't want my > colleagues to underevaluate PostgreSQL. So looking at some of the stuff in JDBC4 one simple thing is that many times we throw not implemented when we should be throwing SqlFeatureNotSupported. There are some things that seem intractable such as getRowID I guess we could try to return oid if it were there. > > 2. Ability to run regression tests against the driver located in an > arbitorary place > The current regression test builds the driver and uses it. I want to run the > regression test against an installed driver like "make installcheck" of > PostgreSQL. With that feature, I can make sure that the installation media > contains a correct driver and we performed packaging procedure with no > problem. This is in the ant build file and probably fairly easy to do. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca > > Regards > Maumu > >
On 26/03/11 14:35, Dave Cramer wrote: > On Fri, Mar 25, 2011 at 4:37 PM, MauMau<maumau307@gmail.com> wrote: >> 1. Better JDBC4 support >> I'd like the driver to support more JDBC4 features, regardless of whether >> they are actually useful. Just lack of support could lead to underevaluation >> when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB >> provides better JDBC4 support). I love PostgreSQL and don't want my >> colleagues to underevaluate PostgreSQL. > So looking at some of the stuff in JDBC4 one simple thing is that many > times we throw not implemented when we should be throwing > SqlFeatureNotSupported. That may very well help, I did a grep last month of all the places where the not implemented exception is as a way to work out what work could be done (see http://archives.postgresql.org/pgsql-jdbc/2011-02/msg00087.php). Though I've only had a little time to look since, there were some features that I thought just weren't possible with Postgres so perhaps it's worth discussing what features can be supported and need developing and which methods should be changed to throw the better exception. One feature that I noticed that isn't implemented is named parameters on prepared statements. From a cursory glance I believe that it is possible to that with Postgres, but I note that it's never been implemented for JDBC 2 and upwards. Unless someone tells me it's technically not possible I'd be prepared to invest some time over the coming weeks to add that feature. Regards, -- Mike Fowler Registered Linux user: 379787
On Sat, Mar 26, 2011 at 5:35 PM, Mike Fowler <mike@mlfowler.com> wrote: > On 26/03/11 14:35, Dave Cramer wrote: >> >> On Fri, Mar 25, 2011 at 4:37 PM, MauMau<maumau307@gmail.com> wrote: >>> >>> 1. Better JDBC4 support >>> I'd like the driver to support more JDBC4 features, regardless of whether >>> they are actually useful. Just lack of support could lead to >>> underevaluation >>> when compared to other databases such as Oracle and HSQLDB (IIRC, HSQLDB >>> provides better JDBC4 support). I love PostgreSQL and don't want my >>> colleagues to underevaluate PostgreSQL. >> >> So looking at some of the stuff in JDBC4 one simple thing is that many >> times we throw not implemented when we should be throwing >> SqlFeatureNotSupported. > > That may very well help, I did a grep last month of all the places where the > not implemented exception is as a way to work out what work could be done > (see http://archives.postgresql.org/pgsql-jdbc/2011-02/msg00087.php). Though > I've only had a little time to look since, there were some features that I > thought just weren't possible with Postgres so perhaps it's worth discussing > what features can be supported and need developing and which methods should > be changed to throw the better exception. > > One feature that I noticed that isn't implemented is named parameters on > prepared statements. From a cursory glance I believe that it is possible to > that with Postgres, but I note that it's never been implemented for JDBC 2 > and upwards. Unless someone tells me it's technically not possible I'd be > prepared to invest some time over the coming weeks to add that feature. > > Regards, > > -- > Mike Fowler > Registered Linux user: 379787 Thanks Mike, that would be great! Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Dave Cramer wrote: > My sense is that for basic read/write/update/delete the driver > works just fine. However I'd like to compile a list of everyone's > gripe. At the top of my list is the need to use a cursor to avoid materializing the entire result set in heap during execution of an execute method. Pulling data off the wire should be done in ResultSet.next() method. Yes I know this is not a trivial change, and yes I know that it means that you can get errors during the next() method which currently happen during execute. There are several reasons this can improve performance, as well as eliminating a commonly reported problem with OutOfMemoryError exceptions when people don't know about the issue or accidentally miss one of the requirements. -Kevin
On 28 March 2011 02:31, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dave Cramer wrote: > >> My sense is that for basic read/write/update/delete the driver >> works just fine. However I'd like to compile a list of everyone's >> gripe. > > At the top of my list is the need to use a cursor to avoid > materializing the entire result set in heap during execution of an > execute method. Pulling data off the wire should be done in > ResultSet.next() method. Do you mean something beyond the batching you can do by setting a suitable fetchsize? Or do you mean you want to lift the restrictions on when that batching can actually be used? Or something else? Oliver
Adding to the list is statement timeout. I seem to recall the issue is we do not want to spawn a thread for each statement which seems to be the only way to do this. Alternatively we could use timers, but I presume they just spawn threads as well. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, Mar 24, 2011 at 8:35 PM, Dave Cramer <pg@fastcrypt.com> wrote: > Just got back from pgeast. Seems everyone seems to have a gripe about > the JDBC driver. > > My sense is that for basic read/write/update/delete the driver works > just fine. However I'd like to compile a list of everyone's gripe. > > Dave >
Oliver Jowett wrote on 27.03.2011 16:15: >> At the top of my list is the need to use a cursor to avoid >> materializing the entire result set in heap during execution of an >> execute method. Pulling data off the wire should be done in >> ResultSet.next() method. > > Do you mean something beyond the batching you can do by setting a > suitable fetchsize? > Or do you mean you want to lift the restrictions on when that batching > can actually be used? > Or something else? > I agree with Dave here. Having a default that prevent the entire result to be loaded into the client memory does make a lot of sense to me. Regards Thomas
> Oliver Jowett wrote: > Kevin Grittner wrote: >> Dave Cramer wrote: >> >>> My sense is that for basic read/write/update/delete the driver >>> works just fine. However I'd like to compile a list of everyone's >>> gripe. >> >> At the top of my list is the need to use a cursor to avoid >> materializing the entire result set in heap during execution of an >> execute method. Pulling data off the wire should be done in >> ResultSet.next() method. > > Do you mean something beyond the batching you can do by setting a > suitable fetchsize? > Or do you mean you want to lift the restrictions on when that > batching can actually be used? I'm talking about doing this even when a cursor isn't used, no fetchSize is set, and without the restrictions (like autoCommit being off and the query string being a single statement). Unfortunately I didn't see the other thread on this topic before I responded to this thread, so we should probably let the point die on this thread. -Kevin
> Dave Cramer wrote: > > Adding to the list is statement timeout. I seem to recall the issue > is we do not want to spawn a thread for each statement which seems > to be the only way to do this. Alternatively we could use timers, > but I presume they just spawn threads as well. No, see the java.util.Timer docs: http://download.oracle.com/javase/6/docs/api/java/util/Timer.html Each Timer object uses one thread and can handle thousands of pending timers. If you're using JDBC you're on a JVM, and those all know how to multi-thread gracefully. A daemon thread for a group of timers should be no big deal. We had a patch submitted for this, which looked to me like it was pretty close to committable. I raised some concerns and was intending to look at it again, but haven't gotten the requisite round tuits yet. -Kevin
Hello, I read the JDBC driver's source code, documentation, and the project web site to compile the missing features and wanted improvements. Attached file is the list of things I noticed. Some of the items may be on the project web site. I hope this will be a bit useful. The driver version I investigated is postgresql-jdbc-9.0-801. I'm interested in discussing which features can be feasible with the current PostgreSQL server and which features require new functionality of the server. However, as I'm relatively new to PostgreSQL, I may fail to keep up with your level of discussions. Regards MauMau
Attachment
MauMau, That's quite a list! Thanks. So in general I'm having trouble understanding things where the implementation says we can do whatever we want you have a requirement ? For example getGeneratedKeys from a batch statement? What is it you are looking for ? AFAICT we are implementing the spec here. The fact that mysql does something else is irrelevant. Please note this is just a talking point since I do believe it is possible for us to return the generated keys here. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
Hello, Dave, From: "Dave Cramer" <pg@fastcrypt.com> > So in general I'm having trouble understanding things where the > implementation says we can do whatever we want you have a requirement > ? For example getGeneratedKeys from a batch statement? What is it you > are looking for ? AFAICT we are implementing the spec here. The fact > that mysql does something else is irrelevant. Please note this is just > a talking point since I do believe it is possible for us to return the > generated keys here. > > > Dave Cramer Thank you for your quick reply. I'm sorry, but please let me reconsider and tell my opinion tomorrow. I want to practice my presentation for tomorrow's important meeting. MauMau
On 03/25/2011 04:07 PM, Simon Riggs wrote: > On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer<pg@fastcrypt.com> wrote: >> Just got back from pgeast. Seems everyone seems to have a gripe about >> the JDBC driver. >> >> My sense is that for basic read/write/update/delete the driver works >> just fine. However I'd like to compile a list of everyone's gripe. > > Funny that, I just got back from a different conference where people > were griping about it also. > > Didn't get a detailed list but numbers of less impressed people all > active Postgres users and supporters. Personally, I think the JDBC driver is fine. It's not perfect, but I hardly feel justified "demanding" a perfect driver when I haven't contributed anything to its creation. People appear to be very happy to complain, but rather less willing to knuckle down and help! I'm not using the JDBC driver heavily and generally find that it does the job quite well. Also, a least I put in the effort to fix one issue - the core code's handling of client certs requests when multiple certs are in the Keystore. Not that anyone seems to _use_ X.509 client certificates... The only actual issues I've run into, none of which were exactly showstoppers though all of which cost me significant time finding workarounds, are: - (biggest problem) The JDBC driver interacts poorly with PostgreSQL 8.3 and above's removal of implicit casts when using extended Pg types, user-defined types, etc. It's often desirable to represent a type in Java as String, BigDecimal, etc, though it's backed by a domain or a custom type on the Pg side. The most painful time I've had with this was working with Pg's xml type, which I needed to represent Java-side as a string. This is ... painful ... with PgJDBC, especially if you're using a midlayer like JPA2 so you can't use setObject(...) directly. When it's a user-defined type you can define implicit casts, but it's IMO unreasonable to have to go messing with the system catalog just to be able to convert from xml <-> string for example. Try writing a simple test program that handles the 'xml' type from Hibernate or JPA to see what I mean. IMO the server shouldn't be being so strict about implicit conversions *from* string to certain string-like types like xml. Since that's apparently not going to change, it'd be wonderful if the JDBC driver had a way to detect the destination types and send data with the right type when passed a string representation of the type. - Inability to reliably receive notifications without risking blocking. It's not possible to check for notifications w/o risking blocking on SSL connections, and there's no way to ask the driver if the connection is SSL or request that it only check for notifications if it can do so safely without blocking indefinitely. Needs a new notification checking method that, if it cannot guarantee that it's using a non-ssl connection, sends a minimum client/server round trip and reads the response. - Notifications, warnings, notices, etc can flood the receive buffer, leading to deadlock if progress requires that more data be sent before a read is attempted. This has been widely discussed, and the general consensus appears to be that a worker thread for the driver would be required in order to provide a general and reliable solution. -- Craig Ringer
On 03/27/2011 11:14 PM, Dave Cramer wrote: > Adding to the list is statement timeout. I seem to recall the issue is > we do not want to spawn a thread for each statement which seems to be > the only way to do this. Alternatively we could use timers, but I > presume they just spawn threads as well. Timers can use one shared thread for all timers, either a generic timer thread provided by the JVM or (IMO better in this case) one dedicated to that timer pool. I think it'd be an ideal way to do it personally - the JDBC driver has several issues that'd be solvable by adding a single thread to use for various timers, etc. If there are concerns about the driver spawning a thread, it wouldn't be too tricky to make timer-requiring features conditional on a connection param, so if no connections that were going to use timer-based features were made, no thread would be spawned. Personally I don't think a single shared thread is worth worrying about, though. Have you *seen* the thread lists in a modern Java app? Threads are extremely low cost when idle, and are already heavily used throughout Java and the JVM. -- Craig Ringer
29.03.11 05:18, Craig Ringer написав(ла): > On 03/27/2011 11:14 PM, Dave Cramer wrote: >> Adding to the list is statement timeout. I seem to recall the issue is >> we do not want to spawn a thread for each statement which seems to be >> the only way to do this. Alternatively we could use timers, but I >> presume they just spawn threads as well. > > Timers can use one shared thread for all timers, either a generic > timer thread provided by the JVM or (IMO better in this case) one > dedicated to that timer pool. I think it'd be an ideal way to do it > personally - the JDBC driver has several issues that'd be solvable by > adding a single thread to use for various timers, etc. > > If there are concerns about the driver spawning a thread, it wouldn't > be too tricky to make timer-requiring features conditional on a > connection param, so if no connections that were going to use > timer-based features were made, no thread would be spawned. Personally > I don't think a single shared thread is worth worrying about, though. > Have you *seen* the thread lists in a modern Java app? Threads are > extremely low cost when idle, and are already heavily used throughout > Java and the JVM. Please note that you will get into problems with classloaders and different containers. E.g. running a thread from classloader will prevent this classloader to be GCd. Tomcat handles this (with a lot of warnings), but it is not recommended practice. See http://www.theserverside.com/discussions/thread.tss?thread_id=44353 or http://stackoverflow.com/questions/533783/why-spawning-threads-in-j2ee-container-is-discouraged Best regards, Vitalii Tymchyshyn
On 29/03/2011 4:36 PM, Vitalii Tymchyshyn wrote: > Please note that you will get into problems with classloaders and > different containers. E.g. running a thread from classloader will > prevent this classloader to be GCd. Tomcat handles this (with a lot of > warnings), but it is not recommended practice. See > http://www.theserverside.com/discussions/thread.tss?thread_id=44353 or > http://stackoverflow.com/questions/533783/why-spawning-threads-in-j2ee-container-is-discouraged Good point. When running under a container, it'd be best to get a thread pool from the container via JNDI or using resource injection. that should be fairly easily handled using a connection param. -- Craig Ringer Tech-related writing at http://soapyfrogs.blogspot.com/
29.03.11 05:18, Craig Ringer написав(ла): > On 03/27/2011 11:14 PM, Dave Cramer wrote: > > Timers can use one shared thread for all timers, either a generic > timer thread provided by the JVM or (IMO better in this case) one > dedicated to that timer pool. I think it'd be an ideal way to do it > personally - the JDBC driver has several issues that'd be solvable by > adding a single thread to use for various timers, etc. > > If there are concerns about the driver spawning a thread, it wouldn't > be too tricky to make timer-requiring features conditional on a > connection param, so if no connections that were going to use > timer-based features were made, no thread would be spawned. Personally > I don't think a single shared thread is worth worrying about, though. > Have you *seen* the thread lists in a modern Java app? Threads are > extremely low cost when idle, and are already heavily used throughout > Java and the JVM. BTW: May be a wrapper over Socket can be created that will allow "hand-made" timers with socket channel selectors? In this case no new thread will be required. Best regards, Vitalii Tymchyshyn
Vitalii Tymchyshyn <tivv00@gmail.com> wrote: > Please note that you will get into problems with classloaders and > different containers. E.g. running a thread from classloader will > prevent this classloader to be GCd. Would this be a problem if closing the last open connection in the driver instance caused a call to Timer.cancel()? -Kevin
29.03.11 17:11, Kevin Grittner написав(ла): > Vitalii Tymchyshyn<tivv00@gmail.com> wrote: > >> Please note that you will get into problems with classloaders and >> different containers. E.g. running a thread from classloader will >> prevent this classloader to be GCd. > > Would this be a problem if closing the last open connection in the > driver instance caused a call to Timer.cancel()? I think this may help. There are a lot of tricks and confusion with classloaders and I am not an expert in it. Some more things to check are: 1) Thread creation may be prohibited by SecurityManager. I'd expect J2EE containers prohibit such a thing since EJBs are prohibited to create it's own threads. 2) Postgresql driver may be located in "global" classloader, but used from "local" one. I am not sure, which classloader will new thread receive. If it will be "local" one, this will mean global driver will hold reference to classloader (application) from which it were used for the first time. "Clever" container may kill such a thread when application is shut down. Best regards, Vitalii Tymchyshyn
Hello,
----- Original Message -----
From: "Dave Cramer" <pg@fastcrypt.com>
> So in general I'm having trouble understanding things where the
> implementation says we can do whatever we want you have a requirement
> ? For example getGeneratedKeys from a batch statement? What is it you
> are looking for ? AFAICT we are implementing the spec here. The fact
> that mysql does something else is irrelevant. Please note this is just
> a talking point since I do believe it is possible for us to return the
> generated keys here.
I think the most common use case is, or possibly the only real use case is,
to get auto-generated primary keys when you insert multiple rows at once
into a table which has a sequence. An example is shown in "14.1.4
PreparedStatement Objects" of JDBC 4.0 specification. The following code is
a slightly modified version of that example to use auto-generated keys.
CODE EXAMPLE 14-2 Creating and executing a batch of prepared statements
(slightly modified)
--------------------------------------------------
String[] cols = {"emp_id"};
// turn off autocommit
con.setAutoCommit(false);
PreparedStatement stmt = con.prepareStatement(
"INSERT INTO employees VALUES (?, ?)", cols);
stmt.setInt(1, 2000);
stmt.setString(2, "Kelly Kaufmann");
stmt.addBatch();
stmt.setInt(1, 3000);
stmt.setString(2, "Bill Barnes");
stmt.addBatch();
// submit the batch for execution
int[] updateCounts = stmt.executeBatch();
--------------------------------------------------
Though the table definition is not shown in the spec, assume the one below:
CREATE TABLE employees (
emp_id SERIAL PRIMARY KEY,
salary INT,
name VARCHAR(20)
);
Then, pstmt.getGeneratedKeys() is expected to return a ResultSet that
contains two rows which consist only of emp_id column. The order of those
two rows should correspond to the order of employee records added in the
batch with addBatch().
My opinion is that the users should explicitly specify the columns they want
to be returned. I don't find it useful to specify
Statement.RETURN_GENERATED_KEYS, because the JDBC spec says that JDBC
drivers decide which columns are returned (i.e. implementation defined
behavior). Please keep this in mind when reading the next.
So which columns should be returned when you pass
Statement.RETURN_GENERATED_KEYS instead of "cols" to
Connection.prepareStatement() in the above example code? What if the table
does not have an auto-generated primary key?
I think it would be reasonable to return "ctid" system column when the user
specifies Statement.RETURN_GENERATED_KEYS, because he/she does not show any
intention about what he/she wants. For example, Oracle returns ROWID in this
case, as described as follows in the manual:
--------------------------------------------------
The getGeneratedKeys() method enables you to retrieve the auto-generated key
fields. The auto-generated keys are returned as a ResultSet object.
If key columns are not explicitly indicated, then Oracle JDBC drivers cannot
identify which columns need to be retrieved. When a column name or column
index array is used, Oracle JDBC drivers can identify which columns contain
auto-generated keys that you want to retrieve. However, when the
Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers
cannot identify these columns. When the integer flag is used to indicate
that auto-generated keys are to be returned, the ROWID pseudo column is
returned as key. The ROWID can be then fetched from the ResultSet object and
can be used to retrieved other columns.
--------------------------------------------------
What do you think?
Regards
MauMau
Vitalii Tymchyshyn <tivv00@gmail.com> wrote: > 1) Thread creation may be prohibited by SecurityManager. I'd > expect J2EE containers prohibit such a thing since EJBs are > prohibited to create it's own threads. I've never used EJBs, as the description of the technology sounded awful to me up front. If they prohibit this, it would be a problem. Can the security policy be adjusted to allow this specific exception without too much pain? > 2) Postgresql driver may be located in "global" classloader, but > used from "local" one. I am not sure, which classloader will new > thread receive. If it will be "local" one, this will mean global > driver will hold reference to classloader (application) from which > it were used for the first time. I don't think we would have a problem here if we cancel the Timer when the last connection closes. My recollection from working on our own framework is that an object is blocked from garbage collection as long as there is a chain of references to it from an active thread where all references in that chain are stronger than a WeakReference. A Timer's thread obviously needs strong references both its own classloader and all objects queued for execution. Since Timer.cancel() gracefully stops its worker thread, it would no longer prevent cleanup. -Kevin
addBatch()/executeBatch() is broken under autocommit=true. Every statement is clearly supposed to be independant. Example: 5 insert statements, let's say the 2th and the 4th are on duplicate of primary key. All 3 others should still beperformed but they aren't. This breaks our application. We migrated from mysql, and BOOM...
On Tue, Mar 29, 2011 at 11:21 AM, MauMau <maumau307@gmail.com> wrote:
> Hello,
>
>
> ----- Original Message ----- From: "Dave Cramer" <pg@fastcrypt.com>
>>
>> So in general I'm having trouble understanding things where the
>> implementation says we can do whatever we want you have a requirement
>> ? For example getGeneratedKeys from a batch statement? What is it you
>> are looking for ? AFAICT we are implementing the spec here. The fact
>> that mysql does something else is irrelevant. Please note this is just
>> a talking point since I do believe it is possible for us to return the
>> generated keys here.
>
> I think the most common use case is, or possibly the only real use case is,
> to get auto-generated primary keys when you insert multiple rows at once
> into a table which has a sequence. An example is shown in "14.1.4
> PreparedStatement Objects" of JDBC 4.0 specification. The following code is
> a slightly modified version of that example to use auto-generated keys.
>
> CODE EXAMPLE 14-2 Creating and executing a batch of prepared statements
> (slightly modified)
> --------------------------------------------------
> String[] cols = {"emp_id"};
>
> // turn off autocommit
> con.setAutoCommit(false);
>
> PreparedStatement stmt = con.prepareStatement(
> "INSERT INTO employees VALUES (?, ?)", cols);
>
> stmt.setInt(1, 2000);
> stmt.setString(2, "Kelly Kaufmann");
> stmt.addBatch();
>
> stmt.setInt(1, 3000);
> stmt.setString(2, "Bill Barnes");
> stmt.addBatch();
>
> // submit the batch for execution
> int[] updateCounts = stmt.executeBatch();
> --------------------------------------------------
>
>
> Though the table definition is not shown in the spec, assume the one below:
>
> CREATE TABLE employees (
> emp_id SERIAL PRIMARY KEY,
> salary INT,
> name VARCHAR(20)
> );
>
> Then, pstmt.getGeneratedKeys() is expected to return a ResultSet that
> contains two rows which consist only of emp_id column. The order of those
> two rows should correspond to the order of employee records added in the
> batch with addBatch().
>
> My opinion is that the users should explicitly specify the columns they want
> to be returned. I don't find it useful to specify
> Statement.RETURN_GENERATED_KEYS, because the JDBC spec says that JDBC
> drivers decide which columns are returned (i.e. implementation defined
> behavior). Please keep this in mind when reading the next.
>
> So which columns should be returned when you pass
> Statement.RETURN_GENERATED_KEYS instead of "cols" to
> Connection.prepareStatement() in the above example code? What if the table
> does not have an auto-generated primary key?
>
> I think it would be reasonable to return "ctid" system column when the user
> specifies Statement.RETURN_GENERATED_KEYS, because he/she does not show any
> intention about what he/she wants. For example, Oracle returns ROWID in this
> case, as described as follows in the manual:
>
> --------------------------------------------------
> The getGeneratedKeys() method enables you to retrieve the auto-generated key
> fields. The auto-generated keys are returned as a ResultSet object.
> If key columns are not explicitly indicated, then Oracle JDBC drivers cannot
> identify which columns need to be retrieved. When a column name or column
> index array is used, Oracle JDBC drivers can identify which columns contain
> auto-generated keys that you want to retrieve. However, when the
> Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers
> cannot identify these columns. When the integer flag is used to indicate
> that auto-generated keys are to be returned, the ROWID pseudo column is
> returned as key. The ROWID can be then fetched from the ResultSet object and
> can be used to retrieved other columns.
> --------------------------------------------------
>
> What do you think?
>
> Regards
> MauMau
>
Well we already have a workable solution to this, we simply return all
the columns in this case
Dave
And I would add, the batch is considered a single transaction even though autocommit is true, which causes deadlock whenyou set a batch of updates in an order that will cause a deadlock. Example: make a batch of updates for pk ID=1 to 10 while you make a 2nd batch from id=10 to 1. To increase the chance ofsuccess with a deadlock, you can toss in a pg_sleep() call to slow things down. You will see a deadlock even though you never intended to use a lock. As you might guess, this break our application ported from mysql... The workaround is very bad: either sorting updates batch by PK which is costly, and sometimes not possible unless you totallyknow the semantic of the sql you are attempting, or interlacing a hack "COMMIT" as a sql statement in the batch, whichis not legal since there was no "BEGIN" (but triggers something in postgres). --- On Tue, 3/29/11, Quartz <quartz12h@yahoo.com> wrote: > From: Quartz <quartz12h@yahoo.com> > Subject: Re: [JDBC] JDBC gripe list > To: pgsql-jdbc@postgresql.org > Received: Tuesday, March 29, 2011, 3:29 PM > addBatch()/executeBatch() is broken > under autocommit=true. > > Every statement is clearly supposed to be independant. > Example: 5 insert statements, let's say the 2th and the 4th > are on duplicate of primary key. All 3 others should still > be performed but they aren't. > > This breaks our application. We migrated from mysql, and > BOOM... > >
On Tue, Mar 29, 2011 at 3:29 PM, Quartz <quartz12h@yahoo.com> wrote: > addBatch()/executeBatch() is broken under autocommit=true. > > Every statement is clearly supposed to be independant. > Example: 5 insert statements, let's say the 2th and the 4th are on duplicate of primary key. All 3 others should stillbe performed but they aren't. > > This breaks our application. We migrated from mysql, and BOOM... > I would think the concept of execute batch would infer that they should all commit or none should. This line from the API seems to infer that "Submits a batch of commands to the database for execution and if all commands execute successfully, returns an array of update counts." Where it states "if all commands execute successfully" implies a transaction in the postgresql world. Dave > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
| Keep reading: http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29 "If one of the commands in a batch update fails to execute properly, this method throws a BatchUpdateException, and a JDBC driver may or may not continue to process the remaining commands in the batch. However, the driver's behavior must be consistent with a particular DBMS, either always continuing to process commands or never continuing to process commands. If the driver continues processing after a failure, the array returned by the method BatchUpdateException.getUpdateCounts will contain as many elements as there are commands in the batch, and at least one of the elements will be the following[...]" "The possible implementations and return values have been modified in the Java 2 SDK, Standard Edition, version 1.3 to accommodate the option of continuing to proccess commands in a batch update after a BatchUpdateException obejct has been thrown. " For those who believe the driver is fine when breaking the batch after the 1st error, then in my example of 5 inserts with pk id=1 to 5 (with id 2 and 4 already present) the table won't contain at least the id=1 (which didn't fail). The postgres driver is partly at fault, but the server is too, for making a transaction whene there is none. Try to get any of the 2 sides to work on it and they just throw the problem the other way: "[server guys]: oh its a driver issue then!..." "[driver guys]: oh, its a server issue then!..." Nothing gets done, and we are stuck. --- On Tue, 3/29/11, Dave Cramer <pg@fastcrypt.com> wrote: > From: Dave Cramer <pg@fastcrypt.com> > Subject: Re: [JDBC] JDBC gripe list > To: "Quartz" <quartz12h@yahoo.com> > Cc: pgsql-jdbc@postgresql.org > Received: Tuesday, March 29, 2011, 4:17 PM > On Tue, Mar 29, 2011 at 3:29 PM, > Quartz <quartz12h@yahoo.com> > wrote: > > addBatch()/executeBatch() is broken under > autocommit=true. > > > > Every statement is clearly supposed to be > independant. > > Example: 5 insert statements, let's say the 2th and > the 4th are on duplicate of primary key. All 3 others should > still be performed but they aren't. > > > > This breaks our application. We migrated from mysql, > and BOOM... > > > > I would think the concept of execute batch would infer that > they > should all commit or none should. This line from the API > seems to > infer that > > "Submits a batch of commands to the database for execution > and if all > commands execute successfully, returns an array of update > counts." > > Where it states "if all commands execute successfully" > implies a > transaction in the postgresql world. > > Dave > > > > > -- > > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-jdbc > > > |
Quartz <quartz12h@yahoo.com> wrote: > "... the driver's behavior must be consistent with a particular > DBMS, either always continuing to process commands or never > continuing to > process commands.,," > "The possible implementations and return values have been modified > in the Java 2 SDK, Standard Edition, version 1.3 to accommodate > the option of continuing to proccess commands in a batch update > after a BatchUpdateException obejct has been thrown. " Unfortunately, the combination of these two requirements with the fact that the PostgreSQL driver supports JDK 1.2 or higher means that this option must not be allowed. We'd have to require a Java version from this century to support that. Maybe it's time to think about that, but we would need to commit to that before we could move to the behavior you want. -Kevin
On 03/29/11 1:49 PM, Kevin Grittner wrote: > Unfortunately, the combination of these two requirements with the > fact that the PostgreSQL driver supports JDK 1.2 or higher means > that this option must not be allowed. We'd have to require a Java > version from this century to support that. Maybe it's time to think > about that, but we would need to commit to that before we could move > to the behavior you want. indeed, I personally think there's ZERO point in supporting anything older than JDK 1.4 going forward. In the java world, 1.2 is akin to PG continuing to support 7.2
For those who believe the driver is fine when breaking the batch after the 1st error, then in my example of 5 inserts with pk id=1 to 5 (with id 2 and 4 already present) the table won't contain at least the id=1 (which didn't fail).
The postgres driver is partly at fault, but the server is too, for making a transaction whene there is none. Try to get any of the 2 sides to work on it and they just throw the problem the other way:
"[server guys]: oh its a driver issue then!..."
"[driver guys]: oh, its a server issue then!..."
Nothing gets done, and we are stuck.
Well in the postgresql world every statement is a transaction. That being said the concept of batch processing in postgres is that it would be done in a transaction otherwise what is the point ? If you agree with that then in the postgres world it would be natural for all of it to fail. At least thats how I would expect postgres to act.
Dave
John R Pierce <pierce@hogranch.com> wrote: > In the java world, 1.2 is akin to PG continuing to support 7.2 PostgreSQL 7.2 was released 2002-02-04. Java 1.2 was released 1998-12-08, which puts it before PG 6.4.1. The feature being requested became available in Java in 2000-05-08, the very same day that PG 7.0 was released. -Kevin
Dave Cramer <pg@fastcrypt.com> wrote: > it would be done in a transaction otherwise what is the point ? That's a fair question. A batch will almost always run faster if done as a single transaction which either runs in its entirety or fails in its entirety. What is the reason for wanting to use batch processing if not for speed? I think we need to see a reasonable use case from someone advocating this change in order to justify it. (Note: "It works in MySQL" won't cut it.) -Kevin
On Tue, Mar 29, 2011 at 5:32 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > Dave Cramer <pg@fastcrypt.com> wrote: > >> it would be done in a transaction otherwise what is the point ? > > That's a fair question. A batch will almost always run faster if > done as a single transaction which either runs in its entirety or > fails in its entirety. What is the reason for wanting to use batch > processing if not for speed? I think we need to see a reasonable > use case from someone advocating this change in order to justify it. > (Note: "It works in MySQL" won't cut it.) > And I think this brings up an important point. In general the driver does what makes sense to do in postgres where given the flexibility in the spec. Sometimes this is not what the rest of the world does. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
I thought I would add my comments regarding Timer and TimerTask.
The following comments are taken from the Java docs for Timer.
<JavaDoc snipet...>
Corresponding to each <tt>Timer</tt> object is a single background
thread that is used to execute all of the timer's tasks, sequentially.
Timer tasks should complete quickly. If a timer task takes excessive time
to complete, it "hogs" the timer's task execution thread. This can, in
turn, delay the execution of subsequent tasks, which may "bunch up" and
execute in rapid succession when (and if) the offending task finally
completes.
</JavaDoc snipet...>
What this means is that if your TimerTask execution time is unknown or not
quantifiable then you cannot reliably use a TimerTask without creating a
worker thread to do the work, otherwise you break the reliability of the
time between events. Effectively you need a second thread to do the work!
In short you are better to create your own single thread to manage such
events.
I already have a lot of experience with a modified PosgreSQL 7.4 driver and
notification threads. Our company has been using it since 2005.
The most reliable approach we came up with is:
1) Modify the driver such that you can synchronize separately on
PGStream.pg_input
and PGStream.pg_output.
We did this such that to synchronize on pg_input you should first
synchronize on pg_output. This is a programming procedure that cannot be
enforced by the language or compilers. This procedure ensures no synchronize
deadlocks. The only code that does not adhere to this procedure is the
notification thread.
2) Create a single notification thread.
Basic processing logic is as follows;
i) Start process loop.
ii) Synchronize on PGStream.pg_input.
iii) stream.Mark(1).
iv) stream.ReceiveChar().
v) is Character notification? Yes - get notification data, No - reset
stream.
vi) exit synchronize on PGStream.pg_input.
vii) If have notification data - do fire notification event, else wait x
time.
viii) restart process loop.
If you would like me to modify the current driver to show how this works I
am willing to do so.
However it still does not solve the thread instantiation problem.
Regards
Donald
----- Original Message -----
From: "Kevin Grittner" <Kevin.Grittner@wicourts.gov>
To: "Vitalii Tymchyshyn" <tivv00@gmail.com>
Cc: "Dave Cramer" <pg@fastcrypt.com>; "List" <pgsql-jdbc@postgresql.org>;
"Craig Ringer" <craig@postnewspapers.com.au>
Sent: Tuesday, March 29, 2011 4:55 PM
Subject: Re: [JDBC] JDBC gripe list
> Vitalii Tymchyshyn <tivv00@gmail.com> wrote:
>
>> 1) Thread creation may be prohibited by SecurityManager. I'd
>> expect J2EE containers prohibit such a thing since EJBs are
>> prohibited to create it's own threads.
>
> I've never used EJBs, as the description of the technology sounded
> awful to me up front. If they prohibit this, it would be a problem.
> Can the security policy be adjusted to allow this specific
> exception without too much pain?
>
>> 2) Postgresql driver may be located in "global" classloader, but
>> used from "local" one. I am not sure, which classloader will new
>> thread receive. If it will be "local" one, this will mean global
>> driver will hold reference to classloader (application) from which
>> it were used for the first time.
>
> I don't think we would have a problem here if we cancel the Timer
> when the last connection closes. My recollection from working on
> our own framework is that an object is blocked from garbage
> collection as long as there is a chain of references to it from an
> active thread where all references in that chain are stronger than a
> WeakReference. A Timer's thread obviously needs strong references
> both its own classloader and all objects queued for execution.
> Since Timer.cancel() gracefully stops its worker thread, it would no
> longer prevent cleanup.
>
On 30 March 2011 09:34, Quartz <quartz12h@yahoo.com> wrote:
Keep reading:
http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29
Keep reading:
"The commit occurs when the statement completes. The time when the statement completes depends on the type of SQL Statement: [...] For CallableStatement objects or for statements that return multiple results, the statement is complete when all of the associated result sets have been closed, and all update counts and output parameters have been retrieved."
Also, the spec says you should always turn off autocommit before batch updates, because the commit behavior in this case is implementation-defined.
Both of these points were raised the last time we discussed this on the list, but I didn't see a reply from you addressing them: http://archives.postgresql.org/pgsql-jdbc/2011-01/msg00055.php. Could you respond to the points in that post if you disagree?
Otherwise, I don't think it's very useful to rehash old issues as if they hadn't been discussed before.
Oliver
"Donald Fraser" <postgres@kiwi-fraser.net> wrote: > I already have a lot of experience with a modified PosgreSQL 7.4 > driver and notification threads. Our company has been using it > since 2005. Are you talking about modifying the driver to generate and deliver events when there is a notification (as in PostgreSQL LISTEN/NOTIFY) on a connection? If so, that's an interesting feature and it would be interesting to see a patch. It's not what we've been discussing on this thread, though, regarding Timer and threads. We're talking about implementing this: http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29 Have you implemented something for that? I don't think your concerns about timer events bunching up really applies a whole lot to this -- these would be one-shot timers; we're not talking about using an interval. -Kevin
On Tue, 29 Mar 2011, Kevin Grittner wrote: > Unfortunately, the combination of these two requirements with the > fact that the PostgreSQL driver supports JDK 1.2 or higher means > that this option must not be allowed. Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release. http://jdbc.postgresql.org/download.html#supported Kris Jurka
On Fri, 25 Mar 2011, Dave Cramer wrote: > There are 3 committers on the JDBC project. Currently Kris has been > shouldering the load, but both Oliver and I are still here. > Having commit privileges and actually committing aren't exactly the same thing. Your last commit was over three years ago. Oliver's was more than five. The mailing list discussions that you contribute to are certainly valuable, but let's not pretend this is a thriving development community here... Kris Jurka
On 03/29/2011 11:55 PM, Kevin Grittner wrote: > Vitalii Tymchyshyn<tivv00@gmail.com> wrote: > If they prohibit this, it would be a problem. > Can the security policy be adjusted to allow this specific > exception without too much pain? It's probably not a good idea to directly create threads from within a container anyway. "Ideally" (from the container app purist and container designer's point of view, not reality) you ask the container for a thread from a managed pool, either by requesting one from JNDI or through @Resource injection. Of course, the pre-defined pools are vendor-specific (argh!) so an additional connection param specifying the jndi name of the connection pool to use would probably be necessary. >> 1) Thread creation may be prohibited by SecurityManager. I'd >> expect J2EE containers prohibit such a thing since EJBs are >> prohibited to create it's own threads. > > I've never used EJBs, as the description of the technology sounded > awful to me up front. EJBs prior to EJB3.1 were indeed awful. With EJB3.1 and CDI/Weld, EJBs and containerized applications are starting to become quite nice. Unfortunately all the dependency injection stuff is still excitingly buggy and the container configuration mechanisms are both vendor specific and rather stupid, limiting the utility of the whole thing a bit. Give it another five to ten years at Java's almost C++-esque rate of development these days and it'll be really nice to use :S ( Stupid configuration mechanism example: JAAS separates the concept of "role" from "user" and "group". Roles are application-defined and are mapped onto container-defined local users and groups. Great idea, except that the role-to-user/group mapping is defined in a vendor-specific xml deployment descriptor THAT IS BUNDLED INSIDE THE APPLICATION! The app has no way of knowing about the users and groups of any given deployment site, so the whole role/user/group separation is rendered useless in one incredible master stroke of pain. At least in Glassfish there's no way to override the bundled descriptor (glassfish-web.xml) during deployment or edit the role mappings after deployment. ) -- Craig Ringer
On 30 March 2011 12:43, Kris Jurka <books@ejurka.com> wrote: > > > On Tue, 29 Mar 2011, Kevin Grittner wrote: > >> Unfortunately, the combination of these two requirements with the >> fact that the PostgreSQL driver supports JDK 1.2 or higher means >> that this option must not be allowed. > > Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release. > > http://jdbc.postgresql.org/download.html#supported What do you think about dropping support for 1.4 and requiring 1.5? * the issues with generics in JDBC interfaces would hopefully go away * many of the backwards compatibility issues with older JDBC versions (e.g. BIT vs BOOLEAN) go away * we can use generics in the driver implementation * we can use java.util.concurrent * many 3rd party libraries become useful (e.g. Netty) - there aren't many things that support 1.4 left I have a hard time dealing with the driver code these days because first, I have to forget half of the language tools I usually use .. Oliver
On Tue, Mar 29, 2011 at 9:09 PM, Oliver Jowett <oliver@opencloud.com> wrote: > On 30 March 2011 12:43, Kris Jurka <books@ejurka.com> wrote: >> >> >> On Tue, 29 Mar 2011, Kevin Grittner wrote: >> >>> Unfortunately, the combination of these two requirements with the >>> fact that the PostgreSQL driver supports JDK 1.2 or higher means >>> that this option must not be allowed. >> >> Support for JDK1.2 and 1.3 was dropped starting with the 8.4 release. >> >> http://jdbc.postgresql.org/download.html#supported > > What do you think about dropping support for 1.4 and requiring 1.5? > > * the issues with generics in JDBC interfaces would hopefully go away > * many of the backwards compatibility issues with older JDBC versions > (e.g. BIT vs BOOLEAN) go away > * we can use generics in the driver implementation > * we can use java.util.concurrent > * many 3rd party libraries become useful (e.g. Netty) - there aren't > many things that support 1.4 left > > I have a hard time dealing with the driver code these days because > first, I have to forget half of the language tools I usually use .. > > Oliver > Is there any way to get statistics on downloads of jars ? Might be useful to see how many times those drivers get downloaded ? Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca
On Tue, 29 Mar 2011, Dave Cramer wrote: > Is there any way to get statistics on downloads of jars ? Might be > useful to see how many times those drivers get downloaded ? > I only have FTP access to the website, so I don't have any access to the logs. I have no problem dropping support for 1.4, it's been dead for long enough. This relatively recent survey may be informative: http://www.theserverside.com/discussions/thread.tss?thread_id=61645 Question four: At work, what JVM do you target compilation for? Java 6 won again, with 247 responses (58%); Java 1.4 got 33 (8%), Java 5 got 146 (34%). One person said they used Retroweaver for clients with 1.4. Kris Jurka
On Tue, Mar 29, 2011 at 1:05 AM, Craig Ringer <craig@postnewspapers.com.au> wrote: > On 03/25/2011 04:07 PM, Simon Riggs wrote: >> >> On Fri, Mar 25, 2011 at 12:35 AM, Dave Cramer<pg@fastcrypt.com> wrote: >>> >>> Just got back from pgeast. Seems everyone seems to have a gripe about >>> the JDBC driver. >>> >>> My sense is that for basic read/write/update/delete the driver works >>> just fine. However I'd like to compile a list of everyone's gripe. >> >> Funny that, I just got back from a different conference where people >> were griping about it also. >> >> Didn't get a detailed list but numbers of less impressed people all >> active Postgres users and supporters. > > Personally, I think the JDBC driver is fine. It's not perfect, but I hardly > feel justified "demanding" a perfect driver when I haven't contributed > anything to its creation. People appear to be very happy to complain, but > rather less willing to knuckle down and help! I was passing on feedback from users, not my own thoughts. We should at least give people the benefit of the doubt before we condemn their willingess to contribute. I remain hopeful there are people out there with a genuine desire to assist. The first step is to come up with a reasonable list of tasks that need work. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
----- Original Message ----- Sent: Wednesday, March 30, 2011 12:22 AM Subject: Re: [JDBC] JDBC gripe list > "Donald Fraser" wrote: > >> I already have a lot of experience with a modified PosgreSQL 7.4 >> driver and notification threads. Our company has been using it >> since 2005. > > Are you talking about modifying the driver to generate and deliver > events when there is a notification (as in PostgreSQL LISTEN/NOTIFY) > on a connection? If so, that's an interesting feature and it would > be interesting to see a patch. Yes. I will work on producing a patch for review. > It's not what we've been discussing on this thread, though, > regarding Timer and threads. We're talking about implementing this: > > http://download.oracle.com/javase/6/docs/api/java/sql/Statement.html#setQueryTimeout%28int%29 > > Have you implemented something for that? I don't think your > concerns about timer events bunching up really applies a whole lot > to this -- these would be one-shot timers; we're not talking about > using an interval. Agreed, one-shot timers would not be a problem. Apologies, some how (probably the time of night I was reading the thread) and all the different subjects within this topic, I miss-read the thread with regards to Timer. We have implemented timeouts, however it was done outside the driver and heavily intergrated with other code and uses 1.5's concurrent thread API. Regards Donald
> > it would be done in a transaction otherwise what is > the point ? > > That's a fair question. A batch will almost always > run faster if > done as a single transaction which either runs in its > entirety or > fails in its entirety. What is the reason for wanting > to use batch > processing if not for speed? I think we need to see a > reasonable > use case from someone advocating this change in order to > justify it. > (Note: "It works in MySQL" won't cut it.) > > -Kevin > I wrote earlier that the batch is independant form the will to make it a transaction for a few reasons: a) you can't control the row locks involved in the batch, and you could end up in a deadlock. This is frequent when you justintended to pile up statements for another thread to call executebatch. b) you just want to run a playlist of independant SQLs not related to each other and you don't expect some failures to preventthe others from running. The batch works independantly from a transaction. I can't stress this differently. Maybe you have never used it this way,or learned it with a biased perspective that batches is only for transaction. I hope I gave a fresh perspective to makea better driver.
Another gripe: "InValid" method "IsValid" method in JDBC spec is not functional (simply throws an exception). http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#isValid(int) This means users have to write their own routine to test if the connection is still successfully running. Example, run aquery like "SELECT 1 = 1;" where you ignore the results -- you just look to see if errors occurred. Spec: ----- Returns true if the connection has not been closed and is still valid. The driver shall submit a query on the connectionor use some other mechanism that positively verifies the connection is still valid when this method is called. ----- Current code in "AbstractJdbc4Connection.java" of "postgresql-jdbc-9.0-801.src": ----- public boolean isValid(int timeout) throws SQLException { checkClosed(); throw org.postgresql.Driver.notImplemented(this.getClass(), "isValid(int)"); } ----- --Basil Bourque
On 31 March 2011 08:51, Basil Bourque <basil.list@me.com> wrote: > Another gripe: "InValid" method > > "IsValid" method in JDBC spec is not functional (simply throws an exception). > http://download.oracle.com/javase/6/docs/api/java/sql/Connection.html#isValid(int) This would be another JDBC4 thing that hasn't been implemented yet. Seems simple enough to add. Oliver
Hello, what i think that it would be nice to have is being able to couple users in the app server with postgresql users. Currently most people define an app server (usually super) user to access the database, and the connections in the pool are created with this db user connecting to the db. Numerous times I encountered situations where I would like to link entries from the web/app server access log (where app server usernames are logged) with entries from the postgresql log, where only the app server db user is logged. In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp) caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts through the source, then idntify some apps as candidated for having been run, and then try to find from the web/app server access logs who those people are. This does not work nice and is not accurate. What would be ideal is to have the app/web server users get connections from the pool authenticated against their app server users. There are ways to do that, I have implemented this in our installation in a test system. However, the problem is that the pool is per user, the connections are named and each pool can have e.g. max connections. This does not scale well, since it requires max_connections >= MAX_CONNECTIONS_PER_USER_DEFINED_IN_APP_SERVER * MAX_APP_SERVER_CONCURRENT_USERS From what i played a little bit with SET SESSION AUTHORIZATION, SET ROLE, those did not seem to affect the actual user (%u) in logging in postgresql logs. The aim was to have one common pool with "unnamed" connections and assign them to a user after/on geting the connection. If that was possible, and if JDBC supported that, then this problem would be easily solved. I know pgsql JDBC driver is the last part of the puzzle, but i just thought to mention it here since the context seems right. Στις Friday 25 March 2011 02:35:48 ο/η Dave Cramer έγραψε: > Just got back from pgeast. Seems everyone seems to have a gripe about > the JDBC driver. > > My sense is that for basic read/write/update/delete the driver works > just fine. However I'd like to compile a list of everyone's gripe. > > Dave > -- Achilleas Mantzios
Achilleas Mantzios, 31.03.2011 09:29: > Currently most people define an app server (usually super) user to access the database, > and the connections in the pool are created with this db user connecting to the db. > Numerous times I encountered situations where I would like to link entries > from the web/app server access log (where app server usernames are logged) > with entries from the postgresql log, where only the app server db user is logged. > In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp) > caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts > through the source, then idntify some apps as candidated for having been run, > and then try to find from the web/app server access logs who those people are. > This does not work nice and is not accurate. > If you are on 9.0 and have control over the connection initialization in the pool, then using 9.0's "application_name" mightbe a solution to this. If you can configure the pool to run SET application_name = 'app_user_name' when a connection is taken out of the pool, then this name can be part of the log message in the PostgreSQL logfile. I don't think the standard pools that are available in Java (C3P0 or DBCP) can use a "dynamic" statement like this (as itrequires injecting a value that changes during time), but maybe there is a way... Regards Thomas
Στις Thursday 31 March 2011 10:42:50 ο/η Thomas Kellerer έγραψε: > Achilleas Mantzios, 31.03.2011 09:29: > > Currently most people define an app server (usually super) user to access the database, > > and the connections in the pool are created with this db user connecting to the db. > > Numerous times I encountered situations where I would like to link entries > > from the web/app server access log (where app server usernames are logged) > > with entries from the postgresql log, where only the app server db user is logged. > > In order to to this corelation, or even worse to *prove* that app server user (lets call him Uapp) > > caused or did not cause this erroneous/malicious DB statement, one has to grep for SQL stmts > > through the source, then idntify some apps as candidated for having been run, > > and then try to find from the web/app server access logs who those people are. > > This does not work nice and is not accurate. > > > If you are on 9.0 and have control over the connection initialization in the pool, then using 9.0's "application_name"might be a solution to this. > > If you can configure the pool to run > > SET application_name = 'app_user_name' > > when a connection is taken out of the pool, then this name can be part of the log message in the PostgreSQL logfile. > Yes, sure, thanx for sharing this. One could indeed do this by hacking/subclassing the relevant pool classes in the app server. But that would still be a work around. I dont know why SET application ='' is reflected in the log files, but SET ROLE is not. Is it intentional ? Anyways this question should be targeted to the backend guys rather than here. > I don't think the standard pools that are available in Java (C3P0 or DBCP) can use a "dynamic" statement like this (asit requires injecting a value that changes during time), but maybe there is a way... > > Regards > Thomas > > -- Achilleas Mantzios
On 31 March 2011 20:29, Achilleas Mantzios <achill@matrix.gatewaynet.com> wrote: > From what i played a little bit with SET SESSION AUTHORIZATION, SET ROLE, > those did not seem to affect the actual user (%u) in logging in postgresql logs. > The aim was to have one common pool with "unnamed" connections and assign them to a user > after/on geting the connection. > If that was possible, and if JDBC supported that, then this problem would be easily solved. Not sure why %u isn't changing, but if it's mostly logging you are worried about, have you tried "SET application_name" and %a as an alternative way of getting the app-level user info into the logs? Oliver
Achilleas Mantzios, 31.03.2011 09:58: >> If you are on 9.0 and have control over the connection >> initialization in the pool, then using 9.0's "application_name" >> might be a solution to this. >> >> If you can configure the pool to run >> >> SET application_name = 'app_user_name' >> >> when a connection is taken out of the pool, then this name can be >> part of the log message in the PostgreSQL logfile. >> > > Yes, sure, thanx for sharing this. One could indeed do this by > hacking/subclassing the relevant pool classes in the app server. But > that would still be a work around. I dont know why SET application > ='' is reflected in the log files, but SET ROLE is not. Is it > intentional ? Anyways this question should be targeted to the backend > guys rather than here. The actual SET application_name is not logged directly, but you can change the log configuration to include the name thatis set with that statement. Regards Thomas
Στις Thursday 31 March 2011 13:06:04 ο/η Thomas Kellerer έγραψε: > Achilleas Mantzios, 31.03.2011 09:58: > >> If you are on 9.0 and have control over the connection > >> initialization in the pool, then using 9.0's "application_name" > >> might be a solution to this. > >> > >> If you can configure the pool to run > >> > >> SET application_name = 'app_user_name' > >> > >> when a connection is taken out of the pool, then this name can be > >> part of the log message in the PostgreSQL logfile. > >> > > > > Yes, sure, thanx for sharing this. One could indeed do this by > > hacking/subclassing the relevant pool classes in the app server. But > > that would still be a work around. I dont know why SET application > > ='' is reflected in the log files, but SET ROLE is not. Is it > > intentional ? Anyways this question should be targeted to the backend > > guys rather than here. > > The actual SET application_name is not logged directly, but you can change the log configuration to include the name thatis set with that statement. > You mean log_line_prefix parameter. Ok but a log_line_prefix = '%d %a %u %p %c %m ' while it prints correctly %a (application) (as set by SET application_name), it does not print correctly %u (user) (as set by SET ROLE). > Regards > Thomas > > > > -- Achilleas Mantzios
> > Achilleas Mantzios, 31.03.2011 09:58: > > >> If you are on 9.0 and have control over the connection > > >> initialization in the pool, then using 9.0's "application_name" > > >> might be a solution to this. > > >> > > >> If you can configure the pool to run > > >> > > >> SET application_name = 'app_user_name' > > >> > > >> when a connection is taken out of the pool, then this name can be > > >> part of the log message in the PostgreSQL logfile. > > >> > > > > > > Yes, sure, thanx for sharing this. One could indeed do this by > > > hacking/subclassing the relevant pool classes in the app server. But > > > that would still be a work around. I dont know why SET application > > > ='' is reflected in the log files, but SET ROLE is not. Is it > > > intentional ? Anyways this question should be targeted to the backend > > > guys rather than here. > > > > The actual SET application_name is not logged directly, but you can change the log configuration to include the namethat is set with that statement. > > > > You mean log_line_prefix parameter. Ok but a > log_line_prefix = '%d %a %u %p %c %m ' > while it prints correctly %a (application) (as set by SET application_name), > it does not print correctly %u (user) (as set by SET ROLE). Hello, I would not say that %u return an incorrect user as the info stay relevant after changing the connection role, but an additional parameter to log the current role would be indeed interesting. regards, Marc Mamin
On Thu, 31 Mar 2011, Marc Mamin wrote: > >>> Achilleas Mantzios, 31.03.2011 09:58: >> You mean log_line_prefix parameter. Ok but a >> log_line_prefix = '%d %a %u %p %c %m ' >> while it prints correctly %a (application) (as set by SET application_name), >> it does not print correctly %u (user) (as set by SET ROLE). > > I would not say that %u return an incorrect user as the info stay > relevant after changing the connection role, but an additional parameter > to log the current role would be indeed interesting. > A patch was worked on for the server for 9.1 to implement this, but it didn't make it in, perhaps for 9.2... https://commitfest.postgresql.org/action/patch_view?id=524 Kris Jurka
Στις Thursday 31 March 2011 17:47:36 ο/η Kris Jurka έγραψε: > > On Thu, 31 Mar 2011, Marc Mamin wrote: > > > > >>> Achilleas Mantzios, 31.03.2011 09:58: > >> You mean log_line_prefix parameter. Ok but a > >> log_line_prefix = '%d %a %u %p %c %m ' > >> while it prints correctly %a (application) (as set by SET application_name), > >> it does not print correctly %u (user) (as set by SET ROLE). > > > > I would not say that %u return an incorrect user as the info stay > > relevant after changing the connection role, but an additional parameter > > to log the current role would be indeed interesting. > > > > A patch was worked on for the server for 9.1 to implement this, but it > didn't make it in, perhaps for 9.2... > > https://commitfest.postgresql.org/action/patch_view?id=524 Cool, thanks Kris. > > Kris Jurka > -- Achilleas Mantzios
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> Sunday 27 March 2011 18:55:58 > > Dave Cramer wrote: > > > > Adding to the list is statement timeout. I seem to recall the issue > > is we do not want to spawn a thread for each statement which seems > > to be the only way to do this. Alternatively we could use timers, > > but I presume they just spawn threads as well. > > No, see the java.util.Timer docs: > > http://download.oracle.com/javase/6/docs/api/java/util/Timer.html > > Each Timer object uses one thread and can handle thousands of pending > timers. If you're using JDBC you're on a JVM, and those all know how > to multi-thread gracefully. A daemon thread for a group of timers > should be no big deal. > > We had a patch submitted for this, which looked to me like it was > pretty close to committable. I raised some concerns and was > intending to look at it again, but haven't gotten the requisite round > tuits yet. > > -Kevin If I good catched thread. I experimented with cursors, it's dead corner. Actually, if you have fetch size setted then cursors are used, but PG doesn't support scrollable & updatable cursors. From other hand about background fetching, You will need to fetch some good amount of rows, because of, mainly, network latency. Let's assume your latency is 2ms, and I'm looking for row witch id or something (number value) is 2, Java construct if (rs.getInt(1) == 2) takes less then 2 ms. Query will take much longer. If you ask for 10 rows, query time is at least 20ms - too bad. If you wan't to add positioned updates, then it's same problem you may move only forward. The fetch size is for databases that supports, hmm, prefetach, when you may download next xxx rows without moving cursor. I think PG can do it, but only in PgSQL. Regards, Radek