In-placre persistance change of a relation

Hello. This is a thread for an alternative solution to wal_level=none
[*1] for bulk data loading.

*1: https://www.postgresql.org/message-id/TYAPR01MB29901EBE5A3ACCE55BA99186FE320%40TYAPR01MB2990.jpnprd01.prod.outlook.com

At Tue, 10 Nov 2020 09:33:12 -0500, Stephen Frost <sfrost(at)snowman(dot)net> wrote in
> Greetings,
>
> * Kyotaro Horiguchi (horikyota(dot)ntt(at)gmail(dot)com) wrote:
> > For fuel(?) of the discussion, I tried a very-quick PoC for in-place
> > ALTER TABLE SET LOGGED/UNLOGGED and resulted as attached. After some
> > trials of several ways, I drifted to the following way after poking
> > several ways.
> >
> > 1. Flip BM_PERMANENT of active buffers
> > 2. adding/removing init fork
> > 3. sync files,
> > 4. Flip pg_class.relpersistence.
> >
> > It always skips table copy in the SET UNLOGGED case, and only when
> > wal_level=minimal in the SET LOGGED case. Crash recovery seems
> > working by some brief testing by hand.
>
> Somehow missed that this patch more-or-less does what I was referring to
> down-thread, but I did want to mention that it looks like it's missing a
> necessary FlushRelationBuffers() call before the sync, otherwise there
> could be dirty buffers for the relation that's being set to LOGGED (with
> wal_level=minimal), which wouldn't be good. See the comments above
> smgrimmedsync().

Right. Thanks. However, since SetRelFileNodeBuffersPersistence()
called just above scans shared buffers so I don't want to just call
FlushRelationBuffers() separately. Instead, I added buffer-flush to
SetRelFileNodeBuffersPersistence().

FWIW this is a revised version of the PoC, which has some known
problems.

- Flipping of Buffer persistence is not WAL-logged nor even be able to
be safely roll-backed. (It might be better to drop buffers).

- This version handles indexes but not yet handle toast relatins.

- tableAMs are supposed to support this feature. (but I'm not sure
it's worth allowing them not to do so).

> > Of course, I haven't performed intensive test on it.
>
> Reading through the thread, it didn't seem very clear, but we should
> definitely make sure that it does the right thing on replicas when going
> between unlogged and logged (and between logged and unlogged too), of
> course.

regards.

--
Kyotaro Horiguchi
NTT Open Source Software Center