Re: NOLOGGING option, or ?

Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> - it is an option to COPY and CREATE TABLE AS, not GUC, not ALTER TABLE

AFAICS it could just happen automatically for CREATE TABLE AS; there's
no need for an option there, any more than there is for CREATE INDEX.

The only reason it needs to be an explicitly specified option for COPY
is that it would require taking a sole-writer lock on the table, which
COPY does not now do (and I believe I've heard of people using parallel
COPYs to load a table faster, so changing the lock type wouldn't be
transparent for everyone).

> Another point that needs thought is what to do if the table has any
> indexes. Are operations on said indexes logged or not? Maybe we should
> just say that indexes are verbotten and the user needs to create them
> afterwards.

That seems pretty reasonable to me. Again, that's what you'd do anyway
if you are after the fastest possible load time, so why should we work
much harder to support an inefficient approach?

Thinking about it, maybe the user-visible option should be defined thus:

LOCK
Causes COPY FROM to acquire Exclusive lock on the target
table, rather than RowExclusive lock as it normally does.
This ensures that no other process is modifying the table
while the COPY proceeds. In some cases this can allow
significantly faster operation.

and then the checks on PITR mode, no indexes, and empty starting table
could be internal implementation details rather than part of the
user-visible spec (ie, we just fall through and do it normally if any of
those conditions don't hold). I like this a little better because there
might be application-level reasons to want exclusive lock, independently
of implementation details.

Also: AFAICS the starting table need not be empty, if we arrange for all
inserts done by the COPY to be done into freshly-appended blocks. The
initial WAL entry could note the current table length, and instead of
"truncate to 0 length" the recovery action is "truncate to noted
length". So really the constraints are just "no PITR" and "no indexes".

regards, tom lane