java - When does the PostgreSQL JDBC driver fetch rows after executing a query?

Question

When does the PostgreSQL JDBC driver version 9.2-1002 fetch rows from the server after executing a query? Does it fetch the rows immediately after query execution (after the client application invokes PreparedStatement.executeQuery()) or after the client application first invokes ResultSet.next() to retrieve a row from the result set? Does this depend on the value of the statement fetch size?

Answer 1

As the following program demonstrates, PreparedStatement.executeQuery() always retrieves rows in the result set from the server. The program also demonstrates how statement fetch size impacts row retrieval. In the case where the statement has the default fetch size of zero, executeQuery() retrieves all rows from the server and ResultSet.next() retrieves and returns the next row from memory, not from the server. (The program may even close the connection after executing the query and next() can still iterate over all rows.) In the case where fetch size is non-zero, executeQuery() retrieves the first batch of rows, the number of which equals the fetch size, and ResultSet.next() again returns the next row from memory until it consumes all rows in the current batch, at which point it retrieves the next batch of rows from the server. This pattern repeats until ResultSet.next() retrieves an empty batch from the server (one that contains zero rows).

SQL

-- Create table "test" and insert 2,000,000 integers from 1 up to 2,000,000.
WITH RECURSIVE t(n) AS
(
  VALUES (1)
  UNION ALL
  SELECT n+1
  FROM t
  WHERE n < 2000000
)
SELECT n as value
INTO test
FROM t;

Java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Properties;

public class Start
{
    public static void main( String[] args ) throws InterruptedException, SQLException
    {
        try
        {
            Class.forName( "org.postgresql.Driver" );
        }
        catch ( ClassNotFoundException e )
        {
            System.out.println( "Where is your JDBC Driver?" );
            e.printStackTrace();
            return;
        }

        System.out.println( "Registered JDBC driver" );
        Connection connection = null;

        try
        {
            final String databaseUrl = "jdbc:postgresql://localhost:5483/postgres";
            final Properties properties = new Properties();
            connection = DriverManager.getConnection( databaseUrl, properties );
            connection.setAutoCommit(false);
            Statement statement = connection.createStatement();

            // Default fetch size of 0 does not create a cursor.
            // Method executeQuery will retrieve all rows in result set.
            statement.setFetchSize( 0 );

            // Fetch size of 1 creates a cursor with batch size of 1.
            // Method executeQuery will retrieve only 1 row in the result set.
            //statement.setFetchSize( 1 );

            System.out.println( new Date() + ": Before execute query" );
            ResultSet result =
                statement.executeQuery( "select * from test" );
            System.out.println( new Date() + ": After execute query" );
            System.out.println( new Date() + ": Sleep for 5 s" );
            Thread.sleep( 5000 );
            System.out.println( new Date() + ": Before process result set" );
            while ( result.next() );
            System.out.println( new Date() + ": After process result set" );
            result.close();
            statement.close();
        }
        catch ( SQLException e )
        {
            System.out.println( "Connection failed!" );
            e.printStackTrace();
            return;
        }
        finally
        {
            if ( connection != null )
                connection.close();
        }
    }
}