Re: NOLOGGING option, or ?

Jochem van Dieten wrote:
> On 6/1/05, Bruce Momjian wrote:
> > Jochem van Dieten wrote:
> >>
> >> Why only on an empty table? What is the problem with bypassing WAL on
> >> any table as long as all files of that table are fsync'ed before
> >> commit?
> >
> > Because adding rows to a table might modify existing pages, and if the
> > COPY fails, you have to restore those pages to a consistent state, and
> > make sure they are recovered for partial page writes, which we can't do
> > without WAL. With an initially empty table, you can just throw away the
> > file system file.
>
> Thank you for the explanation, but I am afraid I still don't get it.
>
> COPY can either fail and do a normal rollback, in which case there is
> no problem because the xid never made it to the xlog. So I take it you
> are talking about a hard crash (pull the plug) somewhere during the
> actual writing to disk. In that case you have updated several pages
> and overwritten the free space with new tuples. But you have not
> overwritten live tuples, so why would you need to restore them? I
> mean, didn't PostgreSQL < 7.1 work without a WAL at all?

What if you are adding rows to an existing page --- in that case you are
writing a page that also contained valid tuples before the COPY.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073