Hi,

I have difficulties with extended queries and pgpool combo.

the background:

After sending a query to the backend, pgpool waits for a response from
the backend so that pgpool can make sure that the query
completes. This is necessary since pgpool needs to keep the order of
SQL commands (especially which cause some locking), first master then
secondary to avoid a deadlock.

the problem:

a "simple query" returns CommandComplete(or ErrorResponse etc.) after
completing the query. So far no problem.

However a extended query "Execute" does not return CommandComplete
(note: ErrorResponse will be returned if an error occurs). Although
the doc says " Therefore, an Execute phase is always terminated by the
appearance of exactly one of these messages: CommandComplete,
EmptyQueryResponse (if the portal was created from an empty query
string), ErrorResponse, or PortalSuspended", I don't think the
description is quite correct from observing the actual implementation
(or the implementation is wrong).

A workaround I found so far was issuing "Flush" immediately after
"Execute". I'm not sure if this is the correct solution, but I
couldn't find any better way.

Suggestions?
--
Tatsuo Ishii
SRA OSS, Inc. Japan

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> However a extended query "Execute" does not return CommandComplete
> (note: ErrorResponse will be returned if an error occurs). Although
> the doc says " Therefore, an Execute phase is always terminated by the
> appearance of exactly one of these messages: CommandComplete,
> EmptyQueryResponse (if the portal was created from an empty query
> string), ErrorResponse, or PortalSuspended", I don't think the
> description is quite correct from observing the actual implementation
> (or the implementation is wrong).

If you can demonstrate that this is not so, I'd like to see it.

> A workaround I found so far was issuing "Flush" immediately after
> "Execute".

This seems to indicate that you may be confused about the difference
between "backend responds with message xyz" and "message xyz will be
immediately delivered to the client". In *all* cases, you should send
either Flush or Sync before waiting for a backend response, because
only those message types force the backend output buffer to be flushed.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> > However a extended query "Execute" does not return CommandComplete
> > (note: ErrorResponse will be returned if an error occurs). Although
> > the doc says " Therefore, an Execute phase is always terminated by the
> > appearance of exactly one of these messages: CommandComplete,
> > EmptyQueryResponse (if the portal was created from an empty query
> > string), ErrorResponse, or PortalSuspended", I don't think the
> > description is quite correct from observing the actual implementation
> > (or the implementation is wrong).
>
> If you can demonstrate that this is not so, I'd like to see it.
>
> > A workaround I found so far was issuing "Flush" immediately after
> > "Execute".
>
> This seems to indicate that you may be confused about the difference
> between "backend responds with message xyz" and "message xyz will be
> immediately delivered to the client". In *all* cases, you should send
> either Flush or Sync before waiting for a backend response, because
> only those message types force the backend output buffer to be flushed.

My point is, message xyz is immediately delivered or not is not very
cleary described in the docs.

For example in "simple" quries at the completion of the commands
CommandComplete is immediately delivered despite it's not cleary
stated in the docs.
--
Tatsuo Ishii
SRA OSS, Inc. Japan

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> My point is, message xyz is immediately delivered or not is not very
> cleary described in the docs.
> For example in "simple" quries at the completion of the commands
> CommandComplete is immediately delivered despite it's not cleary
> stated in the docs.

Well, the protocol doc says

The simple Query message is approximately equivalent to the series Parse,
Bind, portal Describe, Execute, Close, Sync, using the unnamed ...
^^^^

and it's the Sync part that ensures prompt delivery. But feel free to
propose improved wording.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend