Thread: setFetchSize question

Hello all,

I just noticed something and I thought I'd ask:

When reading large chunks of data in the 7.4 JDBC client I would code
something like this:

conn.setAutoCommit(false);
st.setFetchDirection(ResultSet.FETCH_FORWARD);
st.setFetchSize(1000);
st.executeQuery("select * from foo");

which would result in queries to the postgresql server like this:

DECLARE JDBC_CURS_588 CURSOR FOR select * from foo; fetch forward 1000
from JDBC_CURS_588;

with subsequent fetch forward queries being executed as the full result
set is pulled in.

In the 8.0 driver I don't see this happening.   I see only the "select *
from foo" query in the postgresql server log.

Is the query result still being sent in batch chunks of 1000 rows, but
using a different mechanism?

Thanks!

-- Alan

Funny, I'm trying to do the same thing. I think the problem is that V3
protocol (bind/execute) , Postgresql does not log the portal command
just the SQL statement. I'm busy trying to find the link in the archives
to this problem ..

On Fri, 2005-02-25 at 11:12, Alan Stange wrote:
> Hello all,
>
> I just noticed something and I thought I'd ask:
>
> When reading large chunks of data in the 7.4 JDBC client I would code
> something like this:
>
> conn.setAutoCommit(false);
> st.setFetchDirection(ResultSet.FETCH_FORWARD);
> st.setFetchSize(1000);
> st.executeQuery("select * from foo");
>
> which would result in queries to the postgresql server like this:
>
> DECLARE JDBC_CURS_588 CURSOR FOR select * from foo; fetch forward 1000
> from JDBC_CURS_588;
>
> with subsequent fetch forward queries being executed as the full result
> set is pulled in.
>
> In the 8.0 driver I don't see this happening.   I see only the "select *
> from foo" query in the postgresql server log.
>
> Is the query result still being sent in batch chunks of 1000 rows, but
> using a different mechanism?
>
> Thanks!
>
> -- Alan
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
--
Dave Smith
CANdata Systems Ltd
416-493-9020

On Fri, 25 Feb 2005, Alan Stange wrote:

> conn.setAutoCommit(false);
> st.setFetchDirection(ResultSet.FETCH_FORWARD);
> st.setFetchSize(1000);
> st.executeQuery("select * from foo");
>
> which would result in queries to the postgresql server like this:
>
> DECLARE JDBC_CURS_588 CURSOR FOR select * from foo; fetch forward 1000
> from JDBC_CURS_588;
>
> In the 8.0 driver I don't see this happening.   I see only the "select *
> from foo" query in the postgresql server log.
>
> Is the query result still being sent in batch chunks of 1000 rows, but
> using a different mechanism?
>

Yes, it is still being sent in batches, but it uses the V3 protocol
ability to partially execute portals.  An Execute message takes a
maximum number of rows to return parameter, which is the fetch size.
Debugging what the driver is actually doing can be done by adding
?loglevel=2 to the URL which will log all the frontend/backend messages
sent/received.

Kris Jurka

Dave Smith wrote:
> Funny, I'm trying to do the same thing. I think the problem is that V3
> protocol (bind/execute) , Postgresql does not log the portal command
> just the SQL statement. I'm busy trying to find the link in the archives
> to this problem ..

Yeah, V3 introduces some logging issues. Only the initial Parse is
logged, but a) just because we ran a Parse doesn't actually mean the
query has been run and b) a single Parse might be followed by multiple
Bind/Execute pairs that actually run the query multiple times (and those
Bind/Executes might happen hours after the Parse!). So statement logging
no longer gives you a good idea of what queries are being executed.

Fixing this on the server side is on my todo list but not too high at
the moment.

The portal-execution thing is similar -- for a single query execution,
there might be multiple Executes sent for the same portal (with a
max-rows limit in place). That's less of a logging issue since it's
logically the same query running across all of those Executes, we're
just asking for the results one bit at a time.

-O