[postgis-devel] shp2pgsql multi-transaction INSERT mode

[Sandro Santilli]

On Fri, Jul 18, 2014 at 10:36:47AM +0100, Mark Cave-Ayland wrote:
> On 18/07/14 10:17, Sandro Santilli wrote:
> 
> >It looks like shp2pgsql doesn't do INSERT in multiple
> >transactions anymore, but I couldn't figure out since when.
> >
> >It used to do 250 inserts per transaction around 2004, do
> >you know when was that changed and why ?
> 
> Not sure - check the history? However I would definitely have argued
> to remove this, given as I have personal experience that the time
> cost of manually sorting through a partial committed shapefile is
> exponentially larger than failing individual queued shapefiles so
> you easily know which files you need to retry.

I agree a single transaction is easier to deal with, but anywa
given this is a change in behavior I would have expected an entry
in the NEWS file.

Maybe it changed during the refactoring toward -core and -cli.
I guess a comparable speed to the "chunked insert" could be obtained
by using a single INSERT for multiple rows, like:

 INSERT INTO target VALUES
  (row1),
  (rowX),
  (row255)
  ;

Remi: shp2pgsql can do boty COPY or INSERT. You can't use COPY when
reprojecting, which is a reason to use INSERT.

--strk;