Re: Unlogged tables, persistent kind

On Sun, Apr 24, 2011 at 6:15 PM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> For large sets of low value data, it makes sense. Deleting all data,
> just simply because some of it might be damaged, is not the only
> option. IMHO deleting all the data is a surprising option that will
> cause many people to curse us. I don't see preserving some of the data
> as being worse.

For the "cache table" case, it is *certainly* reasonable to delete
everything upon discovering the risk that some might be damaged.

I have seen cases "in production" where the fix to 'oh, looks like
something's corrupted' is indeed "truncate the cache," where that has
become a Standard Operating Procedure.

Sure, to have Postgres do this is a bit heavy-handed, but it's not as
if this isn't going to be heavily documented with warnings like:
"Contains live plague bacteria.
Beware the Rabid Hippopotami. May cause bleeding at the eyes. If
your database crashes, this table WILL get truncated at startup time."
If the docs are short on warnings, that should probably get
rectified. (I'm allowed to be volunteered to do so :-).)

I'd actually find it unsurprising for such tables to get truncated
even on a clean restart; I'd favor that being an option, as along as
make it thus were generally unproblematic. If the application using
such a table can't cope with that logic, better to induce an
understanding of that sooner rather than later ;-).

It seems like a losing battle to try terribly hard to keep the data
around when, by marking it unlogged, the data definition specifically
warned that this was risky.

I'd not go so far as to suggest having autovac TRUNCATE such tables at
random intervals, but that's a pathology that's not completely
incompatible with the DDL declaration :-)
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"