Horton

Thread

Commits

  1. Pipeline mode disallows multicommand strings

  1. BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    The Post Office <noreply@postgresql.org> — 2021-10-18T11:09:09Z

    The following bug has been logged on the website:

Bug reference:      17235
Logged by:          RekGRpth
Email address:      rekgrpth@gmail.com
PostgreSQL version: 14.0
Operating system:   docker alpine
Description:        

I use libpq in C.
I call PQsendQuery(conn, "select * from cats; select * from cats") after
PQenterPipelineMode.
This cause ERROR
cannot insert multiple commands into a prepared statement.
postgresql client 14
postgresql server 13.4

  2. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    Daniel Verite <daniel@manitou-mail.org> — 2021-10-20T09:21:10Z

    	PG Bug reporting form wrote:

> I use libpq in C.
> I call PQsendQuery(conn, "select * from cats; select * from cats") after
> PQenterPipelineMode.
> This cause ERROR
> cannot insert multiple commands into a prepared statement.
> postgresql client 14
> postgresql server 13.4


In pipeline mode, queries are handled with the extended query protocol
which does not support multiple commands per query.

This is mentioned in this note in the documentation
(see
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY):

  "The query string contained in a Parse message cannot include more than
  one SQL statement; else a syntax error is reported. This restriction
  does not exist in the simple-query protocol, but it does exist in the
  extended protocol, because allowing prepared statements or portals to
  contain multiple commands would complicate the protocol unduly."


Best regards,
-- 
Daniel Vérité
PostgreSQL-powered mailer: https://www.manitou-mail.org
Twitter: @DanielVerite

  3. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    David G. Johnston <david.g.johnston@gmail.com> — 2021-10-20T15:01:33Z

    On Wed, Oct 20, 2021 at 2:21 AM Daniel Verite <daniel@manitou-mail.org>
wrote:

>         PG Bug reporting form wrote:
>
> > I use libpq in C.
> > I call PQsendQuery(conn, "select * from cats; select * from cats") after
> > PQenterPipelineMode.
> > This cause ERROR
> > cannot insert multiple commands into a prepared statement.
> > postgresql client 14
> > postgresql server 13.4
>
>
> In pipeline mode, queries are handled with the extended query protocol
>

This fact is only indirectly mentioned in the documentation:

"While the pipeline API was introduced in PostgreSQL 14, it is a
client-side feature which doesn't require special server support and works
on any server that supports the v3 extended query protocol."

https://www.postgresql.org/docs/current/libpq-pipeline-mode.html

which does not support multiple commands per query.


> This is mentioned in this note in the documentation
> (see
>
> https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-FLOW-EXT-QUERY
> ):
>

It seems desirable to talk about the multi-command limitation directly in
the libpq chapter of the documentation and only refer to the protocol
documentation for "additional reading".  We should not assume the reader of
this material knows that extended protocol implies single command.

More specifically, PQsendQuery is not documented to have the single-command
limitation, nor from what I understand, can a user explicitly make things
such that when used outside of Pipeline mode that the extended query
protocol is used.

It is too late to change now but why did we feel the need to make
PQsendQuery work in Pipeline mode at all?  Lacking a decent answer it is
probably worth saying that while PQsendQuery works it behaves like
PQsendQueryParams without any parameters specified.  In particular, it does
not allow for multiple commands to be specified in the query.

Combine that with a mention that all interactions with the server are done
using v3 of the extended protocol.  Maybe by rewriting the above as:

"While in pipeline mode, communication with the server is done using v3 of
the extended query protocol.  This means that the pipeline API can be used
to communicate with all servers that support the protocol.  It also means
that it is not possible to include multiple commands in a single query (see
the note in Protocol XRef for details)."

(I don't think the mentioning of v14 adds enough info to keep when we
generally avoid trying to mention versions in our documentation)
David J.

  4. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-10-20T19:34:31Z

    On 2021-Oct-20, David G. Johnston wrote:

> It seems desirable to talk about the multi-command limitation directly in
> the libpq chapter of the documentation and only refer to the protocol
> documentation for "additional reading".  We should not assume the reader of
> this material knows that extended protocol implies single command.

Sure.

> It is too late to change now but why did we feel the need to make
> PQsendQuery work in Pipeline mode at all?  

Mostly, I saw no reason not to make it work for the common case.

> Lacking a decent answer it is probably worth saying that while
> PQsendQuery works it behaves like PQsendQueryParams without any
> parameters specified.  In particular, it does not allow for multiple
> commands to be specified in the query.

Sure.

> Combine that with a mention that all interactions with the server are done
> using v3 of the extended protocol.  Maybe by rewriting the above as:
> 
> "While in pipeline mode, communication with the server is done using v3 of
> the extended query protocol.  This means that the pipeline API can be used
> to communicate with all servers that support the protocol.  It also means
> that it is not possible to include multiple commands in a single query (see
> the note in Protocol XRef for details)."

Hmm, this seems to get into too much detail for that particular spot.
Also, this paragraph is there precisely to let the reader know they can
use the pipeline mode with older server versions.  I don't disagree with
rewording the paragraph in a similar way to what you outline in the
first two phrases (not exactly that though), but the third one you
propose seems a bit out of place.  Why not add a few words to
PQsendQuery() doc blurb instead?

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/

  5. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    David G. Johnston <david.g.johnston@gmail.com> — 2021-10-20T19:48:10Z

    On Wednesday, October 20, 2021, Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
>
> Hmm, this seems to get into too much detail for that particular spot.
> Also, this paragraph is there precisely to let the reader know they can
> use the pipeline mode with older server versions.  I don't disagree with
> rewording the paragraph in a similar way to what you outline in the
> first two phrases (not exactly that though), but the third one you
> propose seems a bit out of place.  Why not add a few words to
> PQsendQuery() doc blurb instead?
>
>
Kinda feel it should be mentioned generally as a limitation of the mode
just like not allowing sync calls.  Specifically, in 34.5.1.  I’m good with
34.5 not covering that specific point.

Whether or how to address the behavior of PQsendQuery in 34.5.1.1 to also
address its behavior due to this would be a further, independent, change.

David J.

  6. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-10-20T20:37:26Z

    On 2021-Oct-20, David G. Johnston wrote:

> On Wednesday, October 20, 2021, Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:
> >
> > Hmm, this seems to get into too much detail for that particular spot.
> > Also, this paragraph is there precisely to let the reader know they can
> > use the pipeline mode with older server versions.  I don't disagree with
> > rewording the paragraph in a similar way to what you outline in the
> > first two phrases (not exactly that though), but the third one you
> > propose seems a bit out of place.  Why not add a few words to
> > PQsendQuery() doc blurb instead?
>
> Kinda feel it should be mentioned generally as a limitation of the mode
> just like not allowing sync calls.  Specifically, in 34.5.1.  I’m good with
> 34.5 not covering that specific point.

Yeah, seems good to me.

> Whether or how to address the behavior of PQsendQuery in 34.5.1.1 to also
> address its behavior due to this would be a further, independent, change.

I think also mentioning in 34.5.1.1 after explaining in 34.5.1 is not
very interesting; to me it sounds like 34.4 is a better place:

"After successfully calling PQsendQuery, ...  that the command is done.

 In pipeline mode, commands strings containing more than one SQL command
 are disallowed."

or something like that.

-- 
Álvaro Herrera         PostgreSQL Developer  —  https://www.EnterpriseDB.com/

  7. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    David G. Johnston <david.g.johnston@gmail.com> — 2021-10-20T20:42:23Z

    On Wednesday, October 20, 2021, Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:

> On 2021-Oct-20, David G. Johnston wrote:
>
> > Whether or how to address the behavior of PQsendQuery in 34.5.1.1 to also
> > address its behavior due to this would be a further, independent, change.
>
> I think also mentioning in 34.5.1.1 after explaining in 34.5.1 is not
> very interesting; to me it sounds like 34.4 is a better place:


Agreed

David J.

  8. Re: BUG #17235: PQsendQuery (with two sql) after PQenterPipelineMode cause ERROR

    Alvaro Herrera <alvherre@alvh.no-ip.org> — 2021-11-05T14:58:21Z

    On 2021-Oct-20, David G. Johnston wrote:

> On Wednesday, October 20, 2021, Alvaro Herrera <alvherre@alvh.no-ip.org>
> wrote:

> > I think also mentioning in 34.5.1.1 after explaining in 34.5.1 is not
> > very interesting; to me it sounds like 34.4 is a better place:
> 
> Agreed

Okay, pushed that way.  Thanks!

-- 
Álvaro Herrera           39°49'30"S 73°17'W  —  https://www.EnterpriseDB.com/