The time it takes to complete is comparable to the original command executed directly on PostgreSQL.
By the way, since this is less efficient then piping directly from OGR, I would introduce a new setting OGR2OGR_COPY_WITH_DUMP that can be optionally set to True to switch the default (current) command.
I would also remove the PG_USE_COPY setting, and the option to switch to INSERT statements because it proved to be extremely slow and inefficient.
Introduction
In the end, it looks like the problem with the COPY command and PgPool is due to OGR.
Even Rouault confirms he was able to reproduce the issue and he made a commit to add a warning inside the page of the Postgresql OGR driver:
Solution
The solution is to dump the command generated by
ogr2ogrtopsqlthrough/vsistdout/.Example:
/usr/bin/ogr2ogr --config PG_USE_COPY YES -f PGDump /vsistdout/ "/usr/src/geonode/pgpool_tests/it_1km/it_1km.shp" -nln it_1km "it_1km" -lco precision=no -lco DIM=2 -lco GEOMETRY_NAME=geometry | psql -d geonode_data -h pgpool -U postgres -f -The time it takes to complete is comparable to the original command executed directly on PostgreSQL.
By the way, since this is less efficient then piping directly from OGR, I would introduce a new setting
OGR2OGR_COPY_WITH_DUMPthat can be optionally set to True to switch the default (current) command.I would also remove the
PG_USE_COPYsetting, and the option to switch toINSERTstatements because it proved to be extremely slow and inefficient.