Re: unlogged tables

On Wed, Dec 8, 2010 at 6:52 AM, Marti Raudsepp <marti(at)juffo(dot)org> wrote:
> A very useful feature for unlogged tables would be the ability to
> switch them back to normal tables -- this way you could do bulk
> loading into an unlogged table and then turn it into a regular table
> using just fsync(), bypassing all the WAL-logging overhead.

If archive_mode is off, then you can often find a way to bypass
WAL-logging during bulk loading anyway.

If archive_mode is on, then I don't see how this can work without
massive changes.

One possibility would be to create a mechanism to inject entire large
files into the archive log stream. (Such a facility might be useful
for other purposes too). So the transaction that changes the mode
from unlogged to logged would have to take an exclusive lock on the
unlogged table and make sure shared buffers for it are written out,
then it would just copy the backing file(s) for that table into the
archive steam with a special header that tells the recovery process
"Set these aside, I'll explain later". Once that is done, it would
just have to ensure the WAL segment it is currently on will come after
the injected files in the archive stream, and write a WAL record
explaining where those bulk files it sent early are supposed to go.

I don't know, sound like a lot of work and lot of pitfalls.

Cheers,

Jeff