Re: COPY with no WAL, in certain circumstances

On Saturday 06 January 2007 16:36, Simon Riggs wrote:
> The rule is: if the relfilenode for a table is new in this transaction
> (and therefore the whole things will be dropped at end-of-transaction)
> then *all* COPY commands are able to avoid writing WAL safely, if:
> - PITR is not enabled
> - there is no active portal (which could have been opened on an earlier
> commandid and could therefore see data prior to the switch to the new
> relfilenode). In those cases, *not* using WAL causes no problems at all,
> so sleep well without it.
>
<snip>
> BEGIN;
> CREATE TABLE foo...
> INSERT INTO foo --uses WAL
> COPY foo.. --no WAL
> INSERT INTO foo --uses WAL
> COPY foo.. --no WAL
> INSERT INTO foo --uses WAL
> COPY foo... --no WAL
> COMMIT;
>

Is there some technical reason that the INSERT statements need to use WAL in
these scenarios? ISTM that in the above scenario there are no cases where
the INSERT statements are any more recoverable than the COPY statements.
While there might not be much gain from bypassing WAL on a single insert, in
bunches, or more importantly when doing INSERT INTO foo SELECT *, it could be
a nice improvement as well. Am I overlooking something?

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL