Re: COPY with no WAL, in certain circumstances

On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote:
> "Simon Riggs"  writes:
> > 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.
> 
> Uh ... what in the world has an active portal got to do with it?
> I think you've confused snapshot considerations with crash recovery.

The patch sets HEAP_XMIN_COMMITTED on all of the rows loaded by COPY as
well. So the active portal consideration does apply in this case. (We
discussed about a year ago the idea of setting FrozenTransactionId,
which I now agree wouldn't work, but setting the hint bits does work.).
That is important, because otherwise the first person to read the newly
loaded table has to re-write the whole table again; right now we ignore
that cost as being associated with the original COPY, but from most
users perspective it is. Its common practice to issue a select count(*)
from table after its been loaded, so that later readers of the table
don't suffer.

Which makes me think we can still use the no-WAL optimisation, but just
without setting HEAP_XMIN_COMMITTED when there is an active portal.

(I should also mention that the creation of the relfilenode can happen
in earlier committed subtransactions also. There is also a great big
list of commands that throw implicit transactions, all of which cannot
therefore be used with this optimisation either.)

-- 
  Simon Riggs             
  EnterpriseDB   http://www.enterprisedb.com