Re: Reduce WAL logging of INSERT SELECT

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Jeff Davis <pgsql(at)j-davis(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Reduce WAL logging of INSERT SELECT
Date: 2011-08-05 01:14:40
Message-ID: CA+TgmoY=X0ShhoN3AbYfG1L65vd-aPdo5sULgpL0Fh9L5vwvLg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Jeff Davis 2011-08-05 01:23:27 Re: Reduce WAL logging of INSERT SELECT
Previous Message Jeff Davis 2011-08-05 01:12:06 Re: psql: bogus descriptions displayed by \d+