Horton
Menu

Thread

  1. When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-01-29T23:22:57Z

    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

  2. Re: When extended query protocol ends?

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-01-29T23:40:15Z

    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

  3. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-01-30T01:15:50Z

    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

  4. Re: When extended query protocol ends?

    Dave Cramer <davecramer@postgres.rocks> — 2024-02-01T10:00:17Z

    On Mon, 29 Jan 2024 at 20:15, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> 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>

Dave

  5. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-01T11:21:50Z

    >> 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

  6. Re: When extended query protocol ends?

    Dave Cramer <davecramer@postgres.rocks> — 2024-02-13T14:34:46Z

    HI Tatsuo,



On Mon, 29 Jan 2024 at 20:15, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> 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


Can you ask the OP what version of the driver they are using. From what I
can tell we send BEGIN using SimpleQuery.

Dave

  7. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-13T23:17:03Z

    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

  8. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-14T22:55:27Z

    >>> 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

  9. Re: When extended query protocol ends?

    Dave Cramer <davecramer@postgres.rocks> — 2024-02-15T13:34:12Z

    On Wed, 14 Feb 2024 at 17:55, Tatsuo Ishii <ishii@sraoss.co.jp> wrote:

> >>> 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.
>

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()'

Thanks,
Dave

>
>

  10. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-16T00:43:31Z

    > 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

  11. Re: When extended query protocol ends?

    Dave Cramer <davecramer@postgres.rocks> — 2024-02-16T00:50:27Z

    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
>

  12. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-16T00:58:41Z

    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
>>

  13. Re: When extended query protocol ends?

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2024-02-21T15:34:49Z

    >Is it possible for the JDBC
>driver to issue a Sync message before sending SAVEPOINT in simple
>query protocol?

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.

https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-PIPELINING
reads that clients can omit sending Sync to make the error handling the way
they like.
I am not that sure we must omit sync there, however, it might be the case.

https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
reads "simple Query message also destroys the unnamed statement" and
"simple Query message also destroys the unnamed portal"

>Or you can send SAVEPOINT using the extended query protocol.

I am afraid we can't.
The purpose of savepoints at the driver's level is to enable migrating
applications from other databases to PostgreSQL.
In PostgreSQL any SQL exception fails the transaction, including errors
like "prepared statement \"...\" does not exist", and so on.
It might be unexpected for the users to unexpectedly get "prepared
statement is no longer valid" errors in case somebody adds a column to a
table.

We can't send complete parse-bind-execute commands for every "savepoint"
call as it would hurt performance.
We can't cache the parsed statement as it could be discarded by a random
"deallocate all".
So the only way out I see is to use simple query mode for savepoint queries.

Vladimir

  14. Re: When extended query protocol ends?

    Jelte Fennema <postgres@jeltef.nl> — 2024-02-21T15:50:03Z

    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.

  15. Re: When extended query protocol ends?

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2024-02-21T16:07:20Z

    >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.

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.

Vladimir

  16. Re: When extended query protocol ends?

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-02-21T16:18:42Z

    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

  17. Re: When extended query protocol ends?

    Jelte Fennema <postgres@jeltef.nl> — 2024-02-22T09:01:36Z

    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.

  18. Re: When extended query protocol ends?

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2024-02-22T09:27:52Z

    >When splitting a multi insert statement you're going to duplicate some work

I do not understand why I am going to duplicate some work.
I assume the database does its best to perform all the needed preparation
when processing "parse" message,
and it should perform only the minimum required work when processing
bind+exec messages.

Unfortunately, it is not completely the case, so using bind+exec+bind+exec
is suboptimal even for trivial insert statements.

Please, take into account the following sequence:
One-time-only:
parse S1 as insert into table(id, name) values(?,?)

Some time later:
bind S1 ...
exec S1
bind S1 ...
exec S1
bind S1 ...
exec S1
bind S1 ...
exec S1
sync

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.
As I said above, the same flow for multi-value insert beats the
bind+exec+bind+exec sequence at a cost of poor reporting.
For instance, for multivalue insert we can't tell how many rows are
generated for each statement.

---

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

$ cat svpnt

BEGIN;
SAVEPOINT PGJDBC_AUTOSAVE;
RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
COMMIT;

$ pgbench -f svpnt --protocol=extended --time=10 --progress=1 -r

progress: 1.0 s, 4213.8 tps, lat 0.237 ms stddev 0.034, 0 failed
progress: 2.0 s, 4367.9 tps, lat 0.229 ms stddev 0.024, 0 failed
progress: 3.0 s, 4296.2 tps, lat 0.233 ms stddev 0.038, 0 failed
progress: 4.0 s, 4382.0 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 5.0 s, 4374.1 tps, lat 0.228 ms stddev 0.026, 0 failed
progress: 6.0 s, 4305.7 tps, lat 0.232 ms stddev 0.035, 0 failed
progress: 7.0 s, 4111.1 tps, lat 0.243 ms stddev 0.182, 0 failed
progress: 8.0 s, 4245.0 tps, lat 0.235 ms stddev 0.042, 0 failed
progress: 9.0 s, 4219.9 tps, lat 0.237 ms stddev 0.036, 0 failed
progress: 10.0 s, 4231.1 tps, lat 0.236 ms stddev 0.031, 0 failed
transaction type: svpnt
scaling factor: 1
query mode: extended
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 42748
number of failed transactions: 0 (0.000%)
latency average = 0.234 ms
latency stddev = 0.065 ms
initial connection time = 2.178 ms
tps = 4275.562760 (without initial connection time)
statement latencies in milliseconds and failures:
         0.058           0  BEGIN;
         0.058           0  SAVEPOINT PGJDBC_AUTOSAVE;
         0.058           0  RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
         0.060           0  COMMIT;

$ pgbench -f svpnt --protocol=simple --time=10 --progress=1 -r

progress: 1.0 s, 4417.7 tps, lat 0.225 ms stddev 0.033, 0 failed
progress: 2.0 s, 4446.0 tps, lat 0.225 ms stddev 0.079, 0 failed
progress: 3.0 s, 4377.1 tps, lat 0.228 ms stddev 0.048, 0 failed
progress: 4.0 s, 4485.0 tps, lat 0.223 ms stddev 0.024, 0 failed
progress: 5.0 s, 4355.9 tps, lat 0.229 ms stddev 0.353, 0 failed
progress: 6.0 s, 4444.3 tps, lat 0.225 ms stddev 0.035, 0 failed
progress: 7.0 s, 4530.7 tps, lat 0.220 ms stddev 0.020, 0 failed
progress: 8.0 s, 4431.1 tps, lat 0.225 ms stddev 0.022, 0 failed
progress: 9.0 s, 4497.1 tps, lat 0.222 ms stddev 0.027, 0 failed
progress: 10.0 s, 4507.0 tps, lat 0.222 ms stddev 0.024, 0 failed
transaction type: svpnt
scaling factor: 1
query mode: simple
number of clients: 1
number of threads: 1
maximum number of tries: 1
duration: 10 s
number of transactions actually processed: 44493
number of failed transactions: 0 (0.000%)
latency average = 0.224 ms
latency stddev = 0.116 ms
initial connection time = 2.690 ms
tps = 4450.372095 (without initial connection time)
statement latencies in milliseconds and failures:
         0.056           0  BEGIN;
         0.056           0  SAVEPOINT PGJDBC_AUTOSAVE;
         0.056           0  RELEASE SAVEPOINT PGJDBC_AUTOSAVE;
         0.057           0  COMMIT;

Vladimir

  19. Re: When extended query protocol ends?

    Jelte Fennema <postgres@jeltef.nl> — 2024-02-22T11:09:02Z

    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.

  20. Re: When extended query protocol ends?

    Michael Zhilin <m.zhilin@postgrespro.ru> — 2024-02-22T12:01:36Z

    Hi,

On 2/22/24 14:09, Jelte Fennema-Nio wrote:
>> 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
I would like to say this document states that "at completion... frontend 
should issue a Sync message... causes the backend to 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 single protocol".
Otherwise, we can't use both protocols under same transaction that looks 
too strict limitation.
> 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

  21. Re: When extended query protocol ends?

    Jelte Fennema <postgres@jeltef.nl> — 2024-02-22T13:16:09Z

    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 backend to 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 single protocol".
> 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"

  22. Re: When extended query protocol ends?

    Tatsuo Ishii <ishii@sraoss.co.jp> — 2024-02-28T12:05:32Z

    > 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

  23. Re: When extended query protocol ends?

    Vladimir Sitnikov <sitnikov.vladimir@gmail.com> — 2024-02-28T16:51:00Z

    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"

Can we have a similar diagnostic to catch the unspoken rule of
"sync should be there in-between extended messages and simple/functioncall
messages and copy subprotocol"?

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.

-----

Jelte>And only extended protocol
Jelte>messages are allowed to be part of a pipeline: "Use of the extended
Jelte>query protocol allows pipelining"

Frankly speaking, I think there's a misunderstanding of "pipelining"

The full quote is

pgdoc>"Use of the extended query protocol allows pipelining,
pgdoc>which means sending a series of queries without waiting for earlier
ones to complete"

The key is "without waiting for earlier ones to complete".
However, in Simple Query section, the documentation reads that
the frontend does not need to wait for the response:

pgdoc>It is not actually necessary for the frontend to wait for
ReadyForQuery before issuing another command"

In other words, SimpleQuery supports pipelining just as fine, and there's
nothing special in "extended query" in that regard.

Vladimir

  24. Re: When extended query protocol ends?

    Jelte Fennema <postgres@jeltef.nl> — 2024-02-28T19:18:02Z

    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.