On Sat, 2007-01-06 at 21:18 -0500, Tom Lane wrote: > "Simon Riggs" <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
