Re: Limit vs setMaxRows issue

Hi,

Thanks for the helpful replies (thanks also to Oliver Jowett).

As it seems to me to be the case that a setMaxRows call actually limits
the data you can access to that specific number of rows and it is
impossible to ever get more rows, it seems to me to be a waste of time
and a loss of performance if the backend does not know this and prepares
the result as if everything will (eventually) be returned.

I am not suggesting that the driver parse queries and add a LIMIT clause
itself. This would make the driver exceedingly complex, it would
duplicate logic in the driver that is already in postgres itself and
cause an extra maintenance nightmare. Furthermore it would probably
introduce many new bugs, cause lots of work, and all for functionality
(i.e., limit the resultset to n rows) that *already exists* in postgres
itself.

So I guess it is indeed a feature request then; that the backend
protocol supports limiting the resultset without having to alter the
query, and that this limit is indeed a hard limit [i will never ask for
more rows] (instead of a soft limit [i might ask for the other rows]).
Considering how all the *functionality* at least is already implemented,
this should not be too much work, I imagine.

The reason I would like to see this feature (instead of adding the LIMIT
manually) is for cross database compatibility (which is the essence of
JDBC). Basically, setMaxRows is portable, LIMIT is not. Since I am part
of a team developing a cross-database application in which performance
is often important, this feature is quite important to us. Currently
postgres is slow for us on simple index queries on large data sets (1.8
seconds for the first 100 primary keys only of a table of 43000 rows);
and unfortunately, these kinds of queries are very common in our
application.

Regards,
Sebastiaan

Kris Jurka wrote:
>
>
> On Wed, 21 Jun 2006, Sebastiaan van Erk wrote:
>
>> I'm not 100% sure of what JDBC says about setMaxRows (it's kind of
>> ambiguous in the java doc, as usual), but as far as I can tell, if
>> you call setMaxRows on the prepared statement there is no way in to
>> ever retrieve more than that number of rows. If this is indeed the
>> case, it seems to me that currently there is a mismatch between the
>> JDBC api and the postgresql api, and JDBC should somehow tell
>> postgres that this is a hard limit and it should not plan for a
>> second batch.
>>
>> Therefore, my question is: is this a bug? It is not feasable for me
>> to add LIMIT clauses to all the SQL queries in my code, so if this IS
>> a bug, I hope it can be fixed. If it is NOT a bug, is there an
>> alternative workaround that does not involve changing all of my sql
>> statements?
>>
>
> I'm not sure how you would like the driver to tell the server that it
> doesn't want more than setMaxRows rows. The defined API for this is
> using LIMIT in your sql query. The driver cannot do this for you (at
> least without parsing your query) because the query may already have a
> LIMIT or it may be something like an INSERT into a VIEW that has a DO
> INSTEAD SELECT rule on it. If you're suggesting that we extended the
> frontend/backend protocol to include this extra information than
> that's definitely a feature request, not a bug report.
>
> Kris Jurka