Re: Implement UNLOGGED clause for COPY FROM

At Fri, 11 Sep 2020 17:36:19 +0900 (JST), Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)com> wrote in
> At Fri, 11 Sep 2020 05:15:32 +0000, "tsunakawa(dot)takay(at)fujitsu(dot)com" <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote in
> > From: Peter Smith <smithpb2250(at)gmail(dot)com>
> > On Thu, Sep 10, 2020 at 7:16 PM tsunakawa(dot)takay(at)fujitsu(dot)com
> > > <tsunakawa(dot)takay(at)fujitsu(dot)com> wrote:
> > > > ALTER TABLE takes long time proportional to the amount of existing data,
> > > while wal_level = none doesn't.
> > >
> > > Right, but if wal_level=none is considered OK for that table with
> > > existing data, then why not just create the table UNLOGGED in the
> > > first place? (or ALTER it to set UNLOGGED just one time and then leave
> > > it as UNLOGGED).
> >
> > The target tables sometimes receive updates (for data maintenance and/or correction). They don't want those updates to be lost due to the database server crash. Unlogged tables lose their entire contents during crash recovery.
> >
> > Please think like this: logging is is the norm, and unlogged operations are exceptions/hacks for some requirement of which the user wants to minimize the use.
>
> I suspect that wal_level=none is a bit too toxic.
>
> "ALTER TABLE SET UNLOGGED" doesn't dump large amount of WAL so I don't
> think it can be a problem. "ALTER TABLE SET LOGGED" also doesn't issue

(Oops! this runs a table copy)

> WAL while wal_level=minimal but runs a table copy. I think the only
> problem of the UNLOGGED table method is that table copy.
>
> If we can skip the table-copy when ALTER TABLE SET LOGGED on
> wal_level=minimal, is your objective achived?

--
Kyotaro Horiguchi
NTT Open Source Software Center