[postgis-devel] [postgis-users] Changing transaction behaviour in shp2pgsql

[Kevin Neufeld]

Yes, I believe so, since the COPY command runs as one large transaction - WAL incremental data is still written in case 
of a failure or rollback.  Thus, when loading a large shape file, the rollback logs will be equally as large.

However, [1] indicates that that there are a few commands that don't do WAL archiving:
CREATE TABLE AS SELECT
CREATE INDEX
ALTER TABLE SET TABLESPACE
CLUSTER

Since these commands simply create a new file in the backend's tablespace, they can guarantee crash safety more cheaply 
by doing an fsync once at the end instead of writing to WAL.

Apparently, COPY FROM is also in this list *iff* it's wrapped in a transaction that includes a CREATE TABLE statement 
prior in the transaction block.  No WAL archiving is performed since if an error is encountered, the new backend file 
can simply be dropped.

Cheers,
Kevin

[1] http://www.postgresql.org/docs/8.4/static/populate.html


On 3/15/2010 1:03 AM, strk wrote:
> On Sun, Mar 14, 2010 at 03:41:25PM -0700, Chris Hodgson wrote:
>> It's chunked because loading records individually is significantly
>> slower, and doing one large transaction can easily run you out of
>> memory/transaction space.
>
> What about the COPY interface ? Does it suffer of the same
> memory/transaction space limits ?
>
> --strk;
>
>    ()   Free GIS&  Flash consultant/developer
>    /\   http://strk.keybit.net/services.html
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel