Re: skip WAL on COPY patch

On Tue, Aug 23, 2011 at 3:05 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Steve Singer <ssinger(at)ca(dot)afilias(dot)info> writes:
>> The attached patch adds an option to the COPY command to skip writing
>> WAL when the following conditions are all met:
>
>> 1) The table is empty (zero size on disk)
>> 2) The copy command can obtain an access exclusive lock on the table
>> with out blocking.
>> 3) The WAL isn't needed for replication
>
> Exposing this as a user-visible option seems a seriously bad idea.
> We'd have to support that forever.  ISTM it ought to be possible to
> avoid the exclusive lock ... maybe not with this particular
> implementation, but somehow.

Also, if it only works when the table is zero size on disk, you might
as well just let people truncate their already-empty tables when they
want this optimization.

What I think would be really interesting is a way to make this work
when the table *isn't* empty. In other words, have a COPY option that
(1) takes an exclusive lock on the table, (2) writes the data being
inserted into new pages beyond the old EOF, and (3) arranges for crash
recovery or transaction abort to truncate the table back to its
previous length. Then you could do fast bulk loads even into a table
that's already populated, so long as you don't mind that the table
will be excusive-locked and freespace within existing heap pages won't
be reused.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company