Dave Crooke <dcrooke@xxxxxxxxx> wrote:
> a. the fact that Statement.executeQuery("select * from
> huge_table") works out of the box with every one of those
> databases, but results in java.langOutOfMemory with PG without
> special setup. Again, this is to the letter of the standard, it's
> just not very user friendly.
The way I read it, it's *allowed* by the standard, but not
*required* by the standard. I agree it's not very friendly
behavior. I made some noise about it early in my use of PostgreSQL,
but let it go once I had it covered for my own shop. I agree it's a
barrier to conversion -- it often comes up here with new PostgreSQL
users, and who knows how many people give up on PostgreSQL without
coming here when they hit it?
It's not just an issue in JDBC, either; it's generally the default
in PostgreSQL interfaces. That seems to be by design, with the
rationale that it prevents returning some part of a result set and
then throwing an error. Anyone coming from another database
probably already handles that, so they won't tend to be impressed by
that argument, but it would be hard to change that as a default
behavior in PostgreSQL without breaking a lot of existing code for
PostgreSQL users at this point. :-(
> b. The fact that with enterprise grade commercital databases, you
> can mix reads and writes on the same Connection, whereas with PG
> Connection.commit() kills open cursors.
Well, I know that with Sybase ASE (and therefore it's probably also
true of Microsoft SQL Server, since last I saw they both use TDS
protocol), unless you're using a cursor, if you execute another
statement through JDBC on the same connection which has a pending
ResultSet, it reads the rest of the ResultSet into RAM (the behavior
you don't like), before executing the new statement. So at least
for those databases you can't really claim *both* a and b as points.
Oops -- I just noticed you said "enterprise grade". ;-)
> The fact that I've been using JDBC for 12 years with half a dozen
> database products, in blissful ignorance of these fine
> distinctions in the standard until I had to deal with them with
> PG, is kinda what my point is :-)
OK, point taken.
> I understand the reasons for some of these limitations, but by no
> means all of them.
Well, one of the cool things about open source is that users have
the opportunity to "scratch their own itches". The JDBC
implementation is 100% Java, so if changing something there would be
helpful to you, you can do so. If you're careful about it, you may
be able to contribute it back to the community to save others the
pain. If you want to take a shot at some of this, I'd be willing to
help a bit. If nothing else, the attempt may give you better
perspective on the reasons for some of the limitations. ;-)
>> (1) Needing to setFetchSize to avoid materializing the entire
>> result set in RAM on the client.
>
> I don't understand the rationale for why PG, unlike every other
> database, doesn't make this a sensible default, e.g, 10,000 rows
I took a bit of a look at this, years ago. My recollection is that,
based on the nature of the data stream, you would need to do
something similar to databases using TDS -- you could read as you go
as long as no other statement is executed on the connection; but
you'd need to add code to recognize the exceptional circumstance and
suck the rest of the result set down the wire to RAM should it be
necessary to "clear the way" for another statement.
If you give it a shot, you might want to see whether it's possible
to avoid an irritating implementation artifact of the TDS JDBC
drivers: if you close a ResultSet or a Statement with an open
ResultSet without first invoking Statement.cancel, they would suck
back the rest of the results (and ignore them) -- making for a big
delay sometimes on a close invocation. As I recall, the
justification was that for executions involving multiple result
sets, they needed to do this to get at the next one cleanly;
although some forms of execute don't support multiple results, and
it doesn't do you a lot of good on Statement close, so you'd think
these could have been optimized.
> I find Oracle's JDBC implmentation to be both user friendly and
> (largely) standards compliant.
Where there are issues with usability or standards compliance with
PostgreSQL, especially for something which works well for you in
other products, I hope you raise them on these lists. Perhaps there
are already ways to deal with them, perhaps we need to better
document something, and perhaps some change can be made to
accommodate the issue. Even if no action is taken at the time it is
helpful to the project, because the number of people raising an
issue is often taken into consideration when deciding whether to
change something. Also, someone running into the issue later may
find the discussion on a search and gain helpful information.
> I hope this can be taken in the amicable spirit of gentlemanly
> debate in which it is offered, and in the context that we all want
> to see PG grow and continue to succeed.
Sure -- and I hope my posts haven't been taken in any other light.
-Kevin
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
