Re: Reduce WAL logging of INSERT SELECT

On Thu, Aug 4, 2011 at 8:55 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Jeff Davis wrote:
>> On Thu, 2011-08-04 at 17:46 -0400, Bruce Momjian wrote:
>> > Right.  I brought up SELECT INTO because you could make the argument
>> > that INSERT ... SELECT is not a utility command like the other ones and
>> > therefore can't be done easily, but CREATE TABLE AS is internal SELECT
>> > INTO and implemented in execMain.c, which I think is where INSERT ...
>> > SELECT would also be implemented.
>>
>> The above statement is a little confusing, so let me start from the
>> beginning:
>>
>> How could we avoid WAL logging for INSERT ... SELECT?
>>
>> The way we do it for CREATE TABLE AS is because nobody would even *see*
>> the table if our transaction doesn't commit. Therefore we don't need to
>> bother logging it. Same can be said for SELECT INTO.
>>
>> INSERT ... SELECT is just an insert. It needs just as much logging as
>> inserting tuples any other way. For instance, it will potentially share
>> pages with other inserts, and better properly record all such page
>> modifications so that they return to a consistent state.
>
> It would act like COPY, meaning the table would have to be truncated or
> created in the same transaction.

It seems to me that, if we know the relation was created or truncated
in the current transaction, and if wal_level = minimal, then we don't
need to WAL-log *anything* until transaction commit (provided we fsync
at commit).

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