[postgis-devel] [postgis-users] Changing transaction behaviour in shp2pgsql
Kevin Neufeld
kneufeld at refractions.net
Mon Mar 15 09:21:45 PDT 2010
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
More information about the postgis-devel
mailing list