Date: Wed, 16 Jun 2021 11:48:28 +0200
From: Boris Kolpackov <boris@codesynthesis.com>
To: pgsql-hackers@lists.postgresql.org
Cc: alvaro.herrera@2ndquadrant.com
Subject: Pipeline mode and PQpipelineSync()
Message-ID: <boris.20210616110321@codesynthesis.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Organization: Code Synthesis
User-Agent: Mutt/1.5.24 (2015-08-30)
Archived-At: 
 <https://www.postgresql.org/message-id/boris.20210616110321%40codesynthesis.com>
Precedence: bulk

I am trying to add bulk operation support to ODB (a C++ ORM) using
the new pipeline mode added to libpq in PostgreSQL 14. However, things
don't seem to be working according to the documentation (or perhaps I
am misunderstanding something). Specifically, the documentation[1]
makes it sound like the use of PQpipelineSync() is optional (34.5.1.1
"Issuing Queries"):

"After entering pipeline mode, the application dispatches requests using
PQsendQuery, PQsendQueryParams, or its prepared-query sibling
PQsendQueryPrepared. These requests are queued on the client-side until
flushed to the server; this occurs when PQpipelineSync is used to establish a
synchronization point in the pipeline, or when PQflush is called. [...]

The server executes statements, and returns results, in the order the client
sends them. The server will begin executing the commands in the pipeline
immediately, not waiting for the end of the pipeline. [...]"

Based on this I expect to be able to queue a single prepared INSERT
statement with PQsendQueryPrepared() and then call PQflush() and
PQconsumeInput() to send/receive the data. This, however, does not
work: the client gets blocked because there is no data to read. Here
is the call sequence:

select()               # socket is writable
PQsendQueryPrepared()  # success
PQflush()              # returns 0 (queue is now empty)
select()               # blocked here indefinitely

In contrast, if I add the PQpipelineSync() call after PQsendQueryPrepared(),
then everything starts functioning as expected:

select()               # socket is writable
PQsendQueryPrepared()  # success
PQpipelineSync()       # success
PQflush()              # returns 0 (queue is now empty)
select()               # socket is readable
PQconsumeInput()       # success
PQgetResult()          # INSERT result
PQgetResult()          # NULL
PQgetResult()          # PGRES_PIPELINE_SYNC

So to me it looks like, contrary to the documentation, the server does
not start executing the statements immediately, instead waiting for the
synchronization point. Or am I missing something here?

The above tests were performed using libpq from 14beta1 running against
PostgreSQL server version 9.5. If you would like to take a look at the
actual code, you can find it here[2] (the PIPELINE_SYNC macro controls
whether PQpipelineSync() is used).

On a related note, I've been using libpq_pipeline.c[3] as a reference
and I believe it has a busy loop calling PQflush() repeatedly on line
721 since once everything has been sent and we are waiting for the
result, select() will keep returning with an indication that the socket
is writable (you can find one way to fix this in [2]).

[1] https://www.postgresql.org/docs/14/libpq-pipeline-mode.html
[2] https://git.codesynthesis.com/cgit/odb/libodb-pgsql/tree/odb/pgsql/statement.cxx?h=bulk#n771
[3] https://doxygen.postgresql.org/libpq__pipeline_8c_source.html