Re: unlogged tables

On Wed, Dec 8, 2010 at 12:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Yeah.  Given this info I'm strongly inclined to stick with UNLOGGED.

OK.  Here's an updated patch set with various fixes:

- Per musings from Tom, I've revisited and revised the logic that
cross-checks relpersistence when you try to create foreign keys.  The
old logic was buggy and wrong.
- I fixed the bug Andy Colson found, whereby any operation that
rewrote an unlogged table would cause its indexes to lose their _init
forks, leading to bizarre behavior.
- I fixed another infelicity Andy Colson noted, whereby vacuuming an
unlogged heap after restart would warn about a zeroed page, by
extending XLOG_SMGR_CREATE with a fork number.
- I added support for hash indexes on unlogged tables (gin and gist
are still not yet supported).

I think the first patch (relpersistence-v4.patch) is ready to commit,
and the third patch to allow synchronous commits to become
asynchronous when it doesn't matter (relax-sync-commit-v1.patch)
doesn't seem to be changing much either, although I would appreciate
it if someone with more expertise than I have with our write-ahead
logging system would give it a quick once-over.

The main patch (unlogged-tables-v4.patch) needs more thought.  Right
now, unlogged buffers are checkpointed, which I want to get rid of.
Andres Freund suggested we could get by with this and still survive a
clean shutdown if we fsync() every unlogged relation in the cluster
before shutting down, but I'm concerned about the case where one of
the fsync() calls fails.  That's presumably already a problem with
checkpoints generally, and I haven't traced through the logic to see
exactly what happens, but I guess this would need similar treatment.
In a non-shutdown checkpoint, the checkpoint can just fail.  In a
shutdown checkpoint, we presumably can't just refuse to exit, but it
shouldn't look like a clean shutdown...

As I was working on the hash index support, it occurred to me that at
some point in the future, we might want to allow an unlogged index on
a permanent table.  With the current patch, an index is unlogged if
and only if the corresponding table is unlogged, and both the table
and the index are reset to empty on restart.  But we could have a
slightly different flavor of index that, instead of being reset to
empty, just gets marked invalid, perhaps by truncating the file to
zero-length (and adding some code to treat that as something other
than a hard error).  Perhaps you could even arrange for autovacuum to
kick off an automatic rebuild, though that might need to be
configurable since some people might not want an index rebuild kicking
off immediately after a crash/failover.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company