Thread: When extended query protocol ends?

While taking care of a Pgpool-II trouble report from user [1], I found
an interesting usage pattern of the extended query protocol. In my
understanding a series of queries in the extended query protocol is
ended by a sync message. Then one ReadyForQuery response comes for one
sync message. However this does not apply if simple query message is
sent instead of sync.

Below is outputs from "pgproto" command coming with Pgpool-II.
(Lines starting "FE" represents a message from frontend to backend.
Lines starting "BE" represents a message from backend to frontend.)

FE=> Parse(stmt="", query="SET extra_float_digits = 3")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Parse(stmt="", query="SET extra_float_digits = 3")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Query (query="SET extra_float_digits = 3")
<= BE ParseComplete
<= BE BindComplete
<= BE CommandComplete(SET)
<= BE ParseComplete
<= BE BindComplete
<= BE CommandComplete(SET)
<= BE CommandComplete(SET)
<= BE ReadyForQuery(I)
FE=> Terminate

As you can see, two "SET extra_float_digits = 3" is sent in the
extended query protocol, then one "SET extra_float_digits = 3" follows
in the simple query protocol. No sync message is sent. However, I get
ReadyForQuery at the end. It seems the extended query protocol is
ended by a simple query protocol message instead of a sync message.

My question is, is this legal in terms of fronted/backend protocol?
If it's legal, I think we'd better to explicitly mention in our
document. Otherwise, users may be confused. For example, in
"55.2.4. Pipelining":

"When using this method, completion of the pipeline must be determined
by counting ReadyForQuery messages and waiting for that to reach the
number of Syncs sent."

Apparently this does not apply to the above example because there's 0
sync message.

Best reagards,

[1] https://www.pgpool.net/pipermail/pgpool-general/2023-December/009051.html
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Tatsuo Ishii <ishii@sraoss.co.jp> writes:
> Below is outputs from "pgproto" command coming with Pgpool-II.
> (Lines starting "FE" represents a message from frontend to backend.
> Lines starting "BE" represents a message from backend to frontend.)

> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
> FE=> Bind(stmt="", portal="")
> FE=> Execute(portal="")
> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
> FE=> Bind(stmt="", portal="")
> FE=> Execute(portal="")
> FE=> Query (query="SET extra_float_digits = 3")
> <= BE ParseComplete
> <= BE BindComplete
> <= BE CommandComplete(SET)
> <= BE ParseComplete
> <= BE BindComplete
> <= BE CommandComplete(SET)
> <= BE CommandComplete(SET)
> <= BE ReadyForQuery(I)
> FE=> Terminate

> As you can see, two "SET extra_float_digits = 3" is sent in the
> extended query protocol, then one "SET extra_float_digits = 3" follows
> in the simple query protocol. No sync message is sent. However, I get
> ReadyForQuery at the end. It seems the extended query protocol is
> ended by a simple query protocol message instead of a sync message.

> My question is, is this legal in terms of fronted/backend protocol?

I think it's poor practice, at best.  You should end the
extended-protocol query cycle before invoking simple query.

I'm disinclined to document, or make any promises about,
what happens if you mix the protocols.

            regards, tom lane

Hello Dave,

> Tatsuo Ishii <ishii@sraoss.co.jp> writes:
>> Below is outputs from "pgproto" command coming with Pgpool-II.
>> (Lines starting "FE" represents a message from frontend to backend.
>> Lines starting "BE" represents a message from backend to frontend.)
> 
>> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
>> FE=> Bind(stmt="", portal="")
>> FE=> Execute(portal="")
>> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
>> FE=> Bind(stmt="", portal="")
>> FE=> Execute(portal="")
>> FE=> Query (query="SET extra_float_digits = 3")
>> <= BE ParseComplete
>> <= BE BindComplete
>> <= BE CommandComplete(SET)
>> <= BE ParseComplete
>> <= BE BindComplete
>> <= BE CommandComplete(SET)
>> <= BE CommandComplete(SET)
>> <= BE ReadyForQuery(I)
>> FE=> Terminate
> 
>> As you can see, two "SET extra_float_digits = 3" is sent in the
>> extended query protocol, then one "SET extra_float_digits = 3" follows
>> in the simple query protocol. No sync message is sent. However, I get
>> ReadyForQuery at the end. It seems the extended query protocol is
>> ended by a simple query protocol message instead of a sync message.
> 
>> My question is, is this legal in terms of fronted/backend protocol?
> 
> I think it's poor practice, at best.  You should end the
> extended-protocol query cycle before invoking simple query.

From [1] I think the JDBC driver sends something like below if
autosave=always option is specified.

"BEGIN READ ONLY" Parse/Bind/Eexecute (in the extended query protocol)
"SAVEPOINT PGJDBC_AUTOSAVE" (in the simple query protocol)   

It seems the SAVEPOINT is sent without finishing the extended query
protocol (i.e. without Sync message). Is it possible for the JDBC
driver to issue a Sync message before sending SAVEPOINT in simple
query protocol? Or you can send SAVEPOINT using the extended query
protocol.

[1] https://www.pgpool.net/pipermail/pgpool-general/2023-December/009051.html

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

>> Hello Dave,
>>
>> > Tatsuo Ishii <ishii@sraoss.co.jp> writes:
>> >> Below is outputs from "pgproto" command coming with Pgpool-II.
>> >> (Lines starting "FE" represents a message from frontend to backend.
>> >> Lines starting "BE" represents a message from backend to frontend.)
>> >
>> >> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
>> >> FE=> Bind(stmt="", portal="")
>> >> FE=> Execute(portal="")
>> >> FE=> Parse(stmt="", query="SET extra_float_digits = 3")
>> >> FE=> Bind(stmt="", portal="")
>> >> FE=> Execute(portal="")
>> >> FE=> Query (query="SET extra_float_digits = 3")
>> >> <= BE ParseComplete
>> >> <= BE BindComplete
>> >> <= BE CommandComplete(SET)
>> >> <= BE ParseComplete
>> >> <= BE BindComplete
>> >> <= BE CommandComplete(SET)
>> >> <= BE CommandComplete(SET)
>> >> <= BE ReadyForQuery(I)
>> >> FE=> Terminate
>> >
>> >> As you can see, two "SET extra_float_digits = 3" is sent in the
>> >> extended query protocol, then one "SET extra_float_digits = 3" follows
>> >> in the simple query protocol. No sync message is sent. However, I get
>> >> ReadyForQuery at the end. It seems the extended query protocol is
>> >> ended by a simple query protocol message instead of a sync message.
>> >
>> >> My question is, is this legal in terms of fronted/backend protocol?
>> >
>> > I think it's poor practice, at best.  You should end the
>> > extended-protocol query cycle before invoking simple query.
>>
>> From [1] I think the JDBC driver sends something like below if
>> autosave=always option is specified.
>>
>> "BEGIN READ ONLY" Parse/Bind/Eexecute (in the extended query protocol)
>> "SAVEPOINT PGJDBC_AUTOSAVE" (in the simple query protocol)
>>
>> It seems the SAVEPOINT is sent without finishing the extended query
>> protocol (i.e. without Sync message). Is it possible for the JDBC
>> driver to issue a Sync message before sending SAVEPOINT in simple
>> query protocol? Or you can send SAVEPOINT using the extended query
>> protocol.
>>
>> [1]
>> https://www.pgpool.net/pipermail/pgpool-general/2023-December/009051.html
>>
>>
> Hi Tatsuo,
>
> Yes, it would be possible.
>
> Can you create an issue on github? Issues · pgjdbc/pgjdbc (github.com)
> <https://github.com/pgjdbc/pgjdbc/issues>

Sure.

https://github.com/pgjdbc/pgjdbc/issues/3107

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Hi Dave,

>> From [1] I think the JDBC driver sends something like below if
>> autosave=always option is specified.
>>
>> "BEGIN READ ONLY" Parse/Bind/Eexecute (in the extended query protocol)
>> "SAVEPOINT PGJDBC_AUTOSAVE" (in the simple query protocol)
>>
>> It seems the SAVEPOINT is sent without finishing the extended query
>> protocol (i.e. without Sync message). Is it possible for the JDBC
>> driver to issue a Sync message before sending SAVEPOINT in simple
>> query protocol? Or you can send SAVEPOINT using the extended query
>> protocol.
>>
>> [1]
>> https://www.pgpool.net/pipermail/pgpool-general/2023-December/009051.html
> 
> 
> Can you ask the OP what version of the driver they are using. From what I
> can tell we send BEGIN using SimpleQuery.

Sure. I will get back once I get the JDBC version.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

>>> From [1] I think the JDBC driver sends something like below if
>>> autosave=always option is specified.
>>>
>>> "BEGIN READ ONLY" Parse/Bind/Eexecute (in the extended query protocol)
>>> "SAVEPOINT PGJDBC_AUTOSAVE" (in the simple query protocol)
>>>
>>> It seems the SAVEPOINT is sent without finishing the extended query
>>> protocol (i.e. without Sync message). Is it possible for the JDBC
>>> driver to issue a Sync message before sending SAVEPOINT in simple
>>> query protocol? Or you can send SAVEPOINT using the extended query
>>> protocol.
>>>
>>> [1]
>>> https://www.pgpool.net/pipermail/pgpool-general/2023-December/009051.html
>> 
>> 
>> Can you ask the OP what version of the driver they are using. From what I
>> can tell we send BEGIN using SimpleQuery.
> 
> Sure. I will get back once I get the JDBC version.

Here it is:
> JDBC driver version used:42.5.1 Regards, Karel.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

> Can you ask the OP what they are doing in the startup. I'm trying to
> replicate their situation.
> Looks like possibly 'setReadOnly' and 'select version()'

Sure I will. By the way 'select version()' may be issued by Pgpool-II
itself. In this case it should be 'SELECT version()', not 'select
version()'.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

Hi Dave,

Oh, I see.

> Hi Tatsuo,
> 
> Actually no need, I figured it out.
> 
> I don't have a solution yet though.
> 
> Dave Cramer
> www.postgres.rocks
> 
> 
> On Thu, 15 Feb 2024 at 19:43, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:
> 
>> > Can you ask the OP what they are doing in the startup. I'm trying to
>> > replicate their situation.
>> > Looks like possibly 'setReadOnly' and 'select version()'
>>
>> Sure I will. By the way 'select version()' may be issued by Pgpool-II
>> itself. In this case it should be 'SELECT version()', not 'select
>> version()'.
>>
>> Best reagards,
>> --
>> Tatsuo Ishii
>> SRA OSS LLC
>> English: http://www.sraoss.co.jp/index_en/
>> Japanese:http://www.sraoss.co.jp
>>

On Wed, 21 Feb 2024 at 16:35, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
> We can't send complete parse-bind-execute commands for every "savepoint" call as it would hurt performance.

Would performance suffer that much? I'd expect the simple and extended
protocol to have roughly the same overhead for simple queries without
arguments such SAVEPOINT.

Vladimir Sitnikov <sitnikov.vladimir@gmail.com> writes:
>> Would performance suffer that much?

> I have not benchmarked it much, however, the driver sends "autosave"
> queries once (savepoint) or twice(savepoint+release) for every
> user-provided query.
> If we use extended queries (parse+bind+exec) for every savepoint, that
> would result in 3 or 6 messages overhead for every user query.

Those should get bundled into single TCP messages, though.  Assuming
that that's done properly, I share the doubt that you're saving
anything very meaningful.

            regards, tom lane

On Wed, 21 Feb 2024 at 17:07, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
> From many measurements we know that insert into table(id, name) values(?,?),(?,?),(?,?) is much more efficient than
> sending individual bind-exec-bind-exec-bind-exec-sync messages like "insert into table(id, name) values(?,?)"
> For instance, here are some measurements: https://www.baeldung.com/spring-jdbc-batch-inserts#performance-comparisons
> Based on that measurements I assume there's a non-trivial per-message overhead.

That's quite a different case. When splitting a multi insert statement
you're going to duplicate some work, e.g. executor initialization and
possibly even planning. But when replacing one Query packet with
Parse-Bind-Exec-Sync, these 4 packets are not duplicating such
expensive work. The only thing they should be doing extra is a bit of
packet parsing, which is very cheap.

On Thu, 22 Feb 2024 at 10:28, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
>
> >When splitting a multi insert statement you're going to duplicate some work
>
> I do not know how this could be made more efficient as I execute parse only once, and then I send
bind+exec+bind+exec
> without intermediate sync messages, so the data should flow nicely in TCP packets.

I agree you cannot change that flow to be more efficient, but I meant
that your comparison was not fair:
1. Multi-insert vs multiple single inserts is actually executing
different queries
2. Going from Query -> Parse+Bind+Exec for the same query, only
changes protocol related things

> Here are some measurements regarding savepoints for simple vs extended
> Sure they are not very scientific, however, they show improvement for simple protocol

Alright, those improvements are not huge, but I agree it's clear that
the extended protocol has some overhead. So probably you'd want to
keep using the simple protocol to send the SAVEPOINT query.

> Apparently, sending an extra message would increase the overhead of the protocol, thus reducing the efficiency of the
application.
> What is the benefit of sending extra Sync?
>
> https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-MESSAGE-CONCEPTS
> suggests that is is fine to mix both simple and extended messages
> depending on the needs of the application.

Yes, it's fine to mix and match extended and simple protocol. But the
protocol docs quite clearly state that a sync is required before going
back to the Simple protocol: "At completion of each series of
extended-query messages, the frontend should issue a Sync message."
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Terminating a sequence of extended messages with a Query message
instead of a Sync message is definitely undefined behaviour.

Apparently, sending an extra message would increase the overhead of the protocol, thus reducing the efficiency of the application.
What is the benefit of sending extra Sync?

https://www.postgresql.org/docs/current/protocol-overview.html#PROTOCOL-MESSAGE-CONCEPTS
suggests that is is fine to mix both simple and extended messages
depending on the needs of the application.

Yes, it's fine to mix and match extended and simple protocol. But the
protocol docs quite clearly state that a sync is required before going
back to the Simple protocol: "At completion of each series of
extended-query messages, the frontend should issue a Sync message."
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY

Terminating a sequence of extended messages with a Query message
instead of a Sync message is definitely undefined behaviour.

-- 
Michael Zhilin
Postgres Professional
+7(925)3366270
https://www.postgrespro.ru

On Thu, 22 Feb 2024 at 13:01, Michael Zhilin <m.zhilin@postgrespro.ru> wrote:
> I would like to say this document states that "at completion... frontend should issue a Sync message... causes the
backendto close the current transaction"
 
> It looks like the sense of wording is "to complete transaction" at the eventual end of traffic, but not "to switch to
singleprotocol".
 
> Otherwise, we can't use both protocols under same transaction that looks too strict limitation.

Sync only closes the current transaction when you didn't explicitly
open one, i.e. you're using an implicit transaction (part of t. If you
open an explicit transaction (, then you can use both extended and
simple protocol messages in the same transaction. The way I understand
it is: Multiple extended messages together form a single pipeline
(easier understood as SQL statement), until a Sync is reached. So
Parse-Bind-Execute-Parse-Bind-Execute-Sync counts as a single unit
from postgres its visibility/rollback behaviour standpoint.

And that's also where sending a Query instead of a Sync introduces a
problem: What is supposed to happen if something fails. Let's take the
simple example Bind-Execute-Query: If the Execute causes an error, is
the Query still executed or not? And what about if the Query fails, is
the Execute before committed or rolled back?

That's why we have the Sync messages, clarify what happens when a
failure occurs somewhere in the pipeline. And only extended protocol
messages are allowed to be part of a pipeline: "Use of the extended
query protocol allows pipelining"

> And that's also where sending a Query instead of a Sync introduces a
> problem: What is supposed to happen if something fails. Let's take the
> simple example Bind-Execute-Query: If the Execute causes an error, is
> the Query still executed or not? And what about if the Query fails, is
> the Execute before committed or rolled back?
> 
> That's why we have the Sync messages, clarify what happens when a
> failure occurs somewhere in the pipeline. And only extended protocol
> messages are allowed to be part of a pipeline: "Use of the extended
> query protocol allows pipelining"

Good point. If we have started extended protocol messages, I think
there's no way to avoid the Sync messages before issuing simple
protocol messages.

Best reagards,
--
Tatsuo Ishii
SRA OSS LLC
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp

On Wed, 28 Feb 2024 at 17:51, Vladimir Sitnikov
<sitnikov.vladimir@gmail.com> wrote:
>
> Jelte> If the Execute causes an error, is
> Jelte> the Query still executed or not? And what about if the Query fails, is
> Jelte> the Execute before committed or rolled back?
>
> Frankly, if there's a requirement from the backend, I would like it to produce a corresponding error message.
>
> What do you think of making the backend respond with an error message if the requests come out of sequence?
> For instance, if execute comes without parse, backend responds with "ERROR:  prepared statement "..." does not
exist"

I totally agree that it makes sense to throw an error in this case.
Libpq actually throws an error client side when a caller attempts to
do this, but this is something that should be checked server side,
given that the protocol docs specify this:
docs> At completion of each series of extended-query messages, the
frontend should issue a Sync message.

> If "extended query" is supposed to be like a subprotocol which allows no extra messages,
> then it would help if the documentation enumerated the subprotocols and their messages.
>
> For instance, for copy, the documentation is clear; it goes with a subprotocol, so only specific messages are
allowed.

Yeah, I think the existence of this thread is proof that the docs are
currently not very clear on this. Feel free to suggest some changes.

> In other words, SimpleQuery supports pipelining just as fine

That's fair.

> and there's nothing special in "extended query" in that regard.

I think the main difference is that pipelining of extended query
messages requires more care at the client than pipelining of simple
Query messages, because not every extended query message always gets a
response in case of an error.