Re: [JDBC] Pipelining executions to postgresql server

>>>> On 2014-11-01 14:04:05 +0000, Mikko Tiihonen wrote:
>>>>> I created a proof of concecpt patch for postgresql JDBC driver that
>>>>> allows the caller to do pipelining of requests within a
>>>>> transaction. The pipelining here means same as for HTTP: the client
>>>>> can send the next execution already before waiting for the response of
>>>>> the previous request to be fully processed.
>>>>
>>>> Slightly confused here. To my knowledge the jdbc driver already employs
>>>> some pipelining? There's some conditions where it's disabled (IIRC
>>>> RETURNING for DML is one of them), but otherwise it's available.
>>>>
>>>> I'm very far from a pgjdbc expert, but that's what I gathered from the
>>>> code when investigating issues a while back and from my colleague Craig.
>>>
>>> Most DB interfaces make the server operations look synchronous.
>>
>> You IIRC can use jdbc's batch interface.
>
> Yes, there is a limited batch interface for inserts and updates. But for
> example when using prepared statements you can only do batches of same
> statement (with different parameters of course).

Hi Mikko,

I am very interested in this.

Indeed JDBC makes you wait for N round-trip delays. If my transaction
needs to to INSERT rows to tables A and B and UPDATE rows in tables C and
D (and then COMMIT), then I'm looking at 4-5 round trips, even with
executeBatch which as you mentioned is made to "look synchronous". If DB
is localhost it's OK, but gets painful if DB is across a LAN hop, and
unusable across a WAN.

As you've observed, there is no corresponding limitation in the FE/BE
protocol, if we can delay sending Sync until after we've sent all the
overlapping Binds and Executes.

I looked over your patch. Your list of ResultHandlerHolders seems to be
the right direction, but as Tom Lane mentioned there may need to be some
way to ensure the statements are all in the same transaction.

Off the top of my head I've sketched out an interface below (using
PGStatement & PGConnection so we don't have to downcast quite as far).
After the "sync" call you could call the original Statement.executeQuery
but it would return immediately. If you attempted to re-use a Statement
(or make additional addBatch calls) between the async() and sync() calls
then you would get an IllegalStateException. This avoids the need for a
Future, and avoids the client having to loop/sleep until done.

/**
* This interface defines the public PostgreSQL extensions to
* java.sql.Statement. All Statements constructed by the PostgreSQL
* driver implement PGStatement.
*/
public interface PGStatement
{
// ...

/** Like {(at)link PreparedStatement#executeQuery()} but no results until {(at)link PGConnection#sync}. */
void asyncExecuteQuery();

/** Like {(at)link PreparedStatement#executeUpdate()} but no results until {(at)link PGConnection#sync}. */
void asyncExecuteUpdate();

/** Like {(at)link Statement#executeBatch()} but no results until {(at)link PGConnection#sync}. */
void asyncExecuteBatch();
}

/**
* This interface defines the public PostgreSQL extensions to
* java.sql.Connection. All Connections returned by the PostgreSQL driver
* implement PGConnection.
*/
public interface PGConnection
{
// ...

/** Wait for all the asynchronous statements to complete. */
void sync();

/** Commit the current transaction, and wait for all the asynchronous statements to complete. */
void commitAndSync();
}