use QGis import data cause pgpool hang.

[liujinyang-highgo]

hi,
when I use Qgis import data from local disk to postgresql(with postgis) through pgpool, that cause pgpool hang.
pgpool version: 4.6.2

reproduce:
1.install Qis
version: QGIS-OSGeo4W-3.40.14-1.msi
you can download it on https://qgis.org/downloads-list/#windows and search by file name "QGIS-OSGeo4W-3.40.14-1", then can download it.
install this version, after installed, directory "QGIS 3.40.14" will be appeared on your desktop, and later use "OSGeo4W Shell" in this directory to test.
2. install pg 16 with postgis.
3. test file see attachment : sampleGDB.zip
unzip this file to local disk d:\testdata
4. test command
ogr2ogr -f "PostgreSQL" "PG:dbname=postgres host=192.168.71.100 port=9999 user=postgres password=123456" D:\testdata\sampleGDB\sampleGDB.gdb sample -nln "sde.bbbbb10" -lco GEOMETRY_NAME=shape -lco FID=objectid -overwrite -progress

run above command in "OSGeo4W Shell", then pgool is hung.
if change port to 5432 connect to PG that can success.

I checked code, it is hung in function CopyDataRows(),but don't know the root cause.

CopyDataRows()
{

if (copyin)
{
	for (i = 0; i < NUM_BACKENDS; i++)
	{
		if (VALID_BACKEND(i))
		{
			pool_flush(CONNECTION(backend, i));

			/*
			 * Check response from the backend.  First check SSL and read
			 * buffer of the backend. It is possible that there's an error
			 * message in the buffer if the COPY command went wrong.
			 * Otherwise wait for data arrival to the backend socket.
			 */
			if (!pool_ssl_pending(CONNECTION(backend, i)) &&
				pool_read_buffer_is_empty(CONNECTION(backend, i)) &&
				synchronize(CONNECTION(backend, i)))                                                 -------was hung in this function
				ereport(FATAL,
						(return_code(2),
						 errmsg("unable to copy data rows"),
						 errdetail("failed to synchronize")));
		}
	}
}

}

could you help check this?
thanks a lot!

sampleGDB.zip

[tatsuo-ishii]

Yes, it looks like synchronize() is waiting for data coming from backend.
Can you share pgpool log with log_client_messages = on ?
Unfortunately I don't have an Windows environment, and cannot reproduce the issue.
Also, if possible, can you share the COPY data so that I could feed the data to pgpool using plain psql -c "COPY TO" command?

[liujinyang-highgo]

hi，
I upload the log.
the last log info is "2026-05-22 13:21:27.882: GDAL 3.12.1 pid 1868236: DETAIL: invalid copyin kind. expected 'd' got 'c'"

your system is MacOS? QGIS also supply that.

pool.log

[tatsuo-ishii]

I upload the log.

Thanks. I will take a look at it.

your system is MacOS? QGIS also supply that.

Ubuntu 24.

[tatsuo-ishii]

Can you share PostgreSQL log?

[liujinyang-highgo]

hi，
I capture the packets between ogr2ogr and pgpool.

we can see, pgpool, received S after c from fronted ,I checked code, pgpool would not forward S to backend , I don't if that cause this issue.

since I did not use offical PG（a database from another company based on PG）,I did not find useful log, I will try later.

hope the Wireshark packets is useful to you.

thanks!

pg1.zip

[liujinyang-highgo]

attachment is database log(not offical pg), I did not find useful info.
with ：
logging_collector = on
log_min_messages = debug5
log_min_error_statement = debug5

pg.log

[tatsuo-ishii]

Thank you for the WireShark packet capture log. With pool.log you sent, I noticed that QGis uses extended query protocol + COPY-IN which pgpool currently cannot handle.

we can see, pgpool, received S after c from fronted ,I checked code, pgpool would not forward S to backend , I don't if that cause this issue.

Probably it's not related to the issue. The code path is used while 'd' packet (copy data) is receiving. But in this case the sync message comes after 'c' (CopyDone).

I think the real issue is, PostgreSQL backend does not respond with 'Z' (ReadyForQuery) when a Sync message is received after Execute message. It seems the backend responds with 'G' (CopyInResponse) instead. As far as I know the behavior is not described in the PostgreSQL document.
BTW, I have modified "pgproto" which comes with pgpool so that I simulate your case. Below is an output from pgproto. As you can see, the first Sync is followed by Parse Complete (it's a response of "Parse" message), Bind Complete (response of "Bind") and CopyInResponse. No ReadyForQuery. On the other hand, the second Sync is responded by CommandComplete and ReadyForQuery. Pgpool is not prepared for the behavior. I am going to adapt the behavior but it will take sometime.

FE=> Query (query="CREATE TEMP TABLE t1(i int)")
<= BE CommandComplete(CREATE TABLE)
<= BE ReadyForQuery(I)
FE=> Parse(stmt="", query="COPY t1 FROM STDIN")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> Sync
<= BE ParseComplete
<= BE BindComplete
<= BE CopyInResponse
FE=> CopyData (copy data="1
")
FE=> CopyData (copy data="2
")
FE=> CopyDone
FE=> Sync
<= BE CommandComplete(COPY 2)
<= BE ReadyForQuery(I)
FE=> Terminate

[liujinyang-highgo]

thanks！
So, why backend does not respond with 'C'(commandComplete) and 'Z' (ReadyForQuery) when a Sync message is received after Execute message?

if need any test support ,please let know.

BTW, Do you have a rough time frame for that？

[tatsuo-ishii]

So, why backend does not respond with 'C'(commandComplete) and 'Z' (ReadyForQuery) when a Sync message is received after Execute message?

Looking into the backend code path, main functions of COPY-IN is DoCopy.

DoCopy
->BeginCopyFrom
-->ReceiveCopyBegin
-->CopyFrom

Here, ReceiveCopyBegin sends CopyInResponse. CopyFrom does NOT accept (actually ignores) Sync (and Flush) message.

BTW, Do you have a rough time frame for that？

No idea for now. I am afraid it will take for a while because 1) it's hard to deal with this and 2) I am busy with other tasks in Pgpool-II project.

[liujinyang-highgo]

Thanks。
Could you provide a more detailed description of the problem? For example, what incorrect aspects of pgool processing led to the backend not returning C and Z?

If it's convenient for you, could you provide an analysis of the root cause and a temporary solution? Alternatively, I can do contribution

[tatsuo-ishii]

what incorrect aspects of pgool processing led to the backend not returning C and Z?

I'm starting to think he backend not returning C and Z is not incorrect.
When the backend receives a valid COPY FROM STDIN, it immediately goes into "COPY-IN" state. In the state
only limited kind of messages are accepted. CopyData, CopyDone and so on. Sync and Flush are just ignored. Probably this is what stated in the document.
https://www.postgresql.org/docs/current/protocol-flow.html#PROTOCOL-COPY
"The backend will ignore Flush and Sync messages received during copy-in mode."

After the backend goes into the state, pgpool calls CopyDataRows to handle CopyData etc.
CopyDataRows ignores Sync and Flush, this is an expected behavior.

Now problem part. pgpool;'s calling timing of CopyDataRows is too late: It calls CopyDataRows after receiving CopyInResponse. However, clients feel free to send entire sequence of data then send Sync at once. For example:

FE=> Parse(stmt="", query="COPY t1 FROM STDIN")
FE=> Bind(stmt="", portal="")
FE=> Execute(portal="")
FE=> CopyData (copy data="1
")
FE=> CopyData (copy data="2
")
FE=> CopyDone
FE=> Sync
<= BE ParseComplete
<= BE BindComplete
<= BE CopyInResponse
(hung)

In this case CopyDataRows is not called until CopyInResponse is received. And previously sent CopyData (that are just forwarded to the bacend), then ignored by the backend.

Probably there's more problem but for now I think this needs to be addressed at least.

[liujinyang-highgo]

hi,
about scenario you mentioned above.

packets between ogr2ogr and pgpool：

packets between pgpool and DB(db listening port:5866)：

from packets org2ogr <---->pgpool <-------->DB
we can see that，clients send entire sequence of data after received ‘G’.

attachment is DB pcap.

db.zip

[tatsuo-ishii]

I created a patch for this. Please test attached patch if you like.

v1-0001-Fix-COPY-IN-hang-in-extended-query-mode.txt

[liujinyang-highgo]

thanks a lot!
verification has been passed on my environment.