Re: More thoughts about FE/BE protocol

Tom,

This SYNC message reminds me to ask you one thing about the FE/BE
protocol redesign. One of the features of the jdbc spec is a "batch"
interface. When an application needs to do a lot of the same thing (i.e
insert a thousand rows), the applicaiton tells the driver to insert a
'batch' of 1000 rows instead of performing 1000 regular inserts. This
allows the driver to optimize this operation as one network roundtrip
instead of 1000 roundtrips.

The current jdbc driver code doesn't do any optimization here and
stupidly still does 1000 roundtrips. However when I or someone has the
time this could be optimized in the current BE/FE protocol by
concatenating all 1000 individual inserts statements into a single query
to be issued to the backend. How could this be accomplished with the
new FE/BE protocol "extended query" facility?

If I understand the SYNC message, would it just be the case that the
client would send the PARSE/BIND/DESCRIBE/EXECUTE messages for all 1000
inserts before issuing a single SYNC?

thanks,
--Barry

Tom Lane wrote:
> I've been thinking some more about the FE/BE protocol redesign,
> specifically the desire to ensure that we can recover from error
> conditions without losing synchronization. The fact that the existing
> protocol doesn't do very well at this shows up in several places:
> * having to drop and restart the connection after a COPY error
> * fastpath function calls also lose sync if there's an error
> * libpq gets terribly confused if it runs out of memory for
> a query result
>
> I'm coming around to the idea that the cleanest solution is to require
> *all* protocol messages, in both directions, to have an initial length
> word. That is, the general message format would look like
> <message type> 1 byte
> <payload length> number of following bytes (4 bytes MSB-first)
> ... message data as needed ...
>
> The advantage of doing it this way is that the recipient can absorb the
> whole message before starting to parse the contents; then, errors detected
> while processing the message contents don't cause us to lose protocol
> synchronization. Also, even if the message is so large as to run the
> recipient out of memory, it can still use the <payload length> to count
> off the number of bytes it has to drop before looking for another message.
> This would make it quite a bit easier for libpq to cope with
> out-of-memory, as an example.
>
> These advantages seem enough to me to justify adding three or four bytes
> to the length of each message. Does anyone have a problem with that?
>
>
> A related point is that I had been thinking of the new "extended query"
> facility (separate PARSE/BIND/DESCRIBE/EXECUTE steps) in terms of
> sending just one big message to the backend per interaction cycle, with
> the processing-step commands appearing as fields within that message.
> But putting a length word in front would effectively require the
> frontend to marshal the whole sequence before sending any of it.
> It seems better to send each of the processing-step commands as an
> independent message. To do that, we need to introduce an additional
> processing step, call it SYNC, that substitutes for the functionality
> associated with the overall message boundary in the other way.
> Specifically:
> * ReadyForQuery (Z) is sent in response to SYNC.
> * If no BEGIN has been issued, SYNC is the point at which an implicit
> COMMIT is done.
> * After an error occurs in an extended-query command, the backend reads
> and discards messages until it finds a SYNC, then issues ReadyForQuery
> and resumes processing messages. This allows the frontend to be
> certain what has been processed and what hasn't.
>
> Comments?
>
> regards, tom lane
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>