Re: pgin.tcl pg_exec_prepared slow (was: Released...)

On Mon, Jul 05, 2004 at 10:43:47PM -0400, Tom Lane wrote:
> L J Bayuk <ljb220(at)mindspring(dot)com> writes:
> > The delay we are seeing (about 40 ms) is from the time the client sends the
> > Bind message to PostgreSQL until it gets a TCP ACK back. (It should get
> > a BindComplete message back, but the backend doesn't flush after sending
> > this - no pq_flush() at the end of exec_bind_message(). Hello, Tom, is this
> > a bug?
>
> No. If you wanted a flush just there, send a Flush message. But AFAICS
> the only reason why you'd do that is if you needed to look at the
> BindComplete before sending your next message --- which you do not.

I agree, it works OK as is. I only raised the question because of two
things. First, the protocol documentation says:
"The response (to the Bind message) is either BindComplete
or ErrorResponse."
>From which one might wrongly conclude that you get BindComplete (or
ErrorResponse) back when you send Bind. (Pgin.tcl doesn't assume this.)

Second, this comment in backend/utils/error/elog.c:
"This flush is normally not necessary, since postgres.c will flush
out waiting data when control returns to the main loop. But it..."
I could not find the described flush "when control returns to the main
loop". Either I'm missing it, or it isn't there, and the comment implies
that it may have been there at one time.

> > pgin.tcl sets its Tcl connection socket channel to "unbuffered",
> > which means each PostgreSQL message it sends will be go into the TCP
> > buffers immediately, since each message is written in a single "puts".
>
> This is certainly bogus. A good implementation would arrange to flush
> the TCP outbound queue immediately after sending either a Flush or a
> Sync message --- and *no place else*. Those are exactly the points
> where you are starting to wait for a backend reply. More frequent
> flushing simply results in more separate network packets, which is not
> a win. Also, if you haven't sent one of these message types, you are
> not entitled to expect any immediate reply (which is essentially what's
> happening with pgin.tcl, evidently).
> ...

No, I didn't explain it well. Pgin.tcl itself isn't waiting for a reply to
Bind. It sends Bind, DescribePortal, Execute, and Sync without waiting in
between. Currently it flushes after each, but this is Tcl flushing to TCP,
not to the wire. I'm going to change that, although in Tcl I can't force
TCP to flush its outbound queue. But for now, I seem to end up with Bind
in one packet, and the other three messages combined into another. That
delay is pgin.tcl blocked while the TCP stack waits for an ACK to the Bind
message's packet. I don't know why this happens but it seems to be a case
where TCP performs "less than optimally" in deciding when to send what it's
got, when to ACK, and when to wait. I will work around it by exercising
more control on when Tcl flushes the data to TCP. I just haven't decided
whether to flush before reading, or flush after all messages that need a
response (I think: Startup, PasswordMessage, Query, Sync, CopyDone, and
FunctionCall are the ones I use).