Re: BUG #10675: alter database set tablespace and unlogged table

On 2014-06-18 09:45:46 -0400, Tom Lane wrote:
> Andres Freund <andres(at)2ndquadrant(dot)com> writes:
> > On 2014-06-18 16:15:47 +0530, Pavan Deolasee wrote:
> >> But I wonder if not flushing dirty buffers
> >> of unlogged tables at a checkpoint is a bad idea anyways. User might expect
> >> that the unlogged tables to sustain server crash or unclean shutdown if
> >> there had been no writes after successful manual checkpoint(s).
>
> > They'll get reset at unlcean startup anyway. Independent of having been
> > touched or not.
>
> I'm with Pavan on this one: it's *not* a good thing that manually issued
> checkpoints skip unlogged tables. That's surprising, possibly dangerous,
> and no case whatsoever has been made that anyone sees it as an important
> performance benefit.

I don't understand what dangers it has? Any unclean shutdown resets all
unlogged tables.

/* REDO */
if (InRecovery)
{
...
/*
* We're in recovery, so unlogged relations may be trashed and must be
* reset. This should be done BEFORE allowing Hot Standby
* connections, so that read-only backends don't try to read whatever
* garbage is left over from before.
*/
ResetUnloggedRelations(UNLOGGED_RELATION_CLEANUP);
...

And I don't really see why it's so surprising given that normal
checkpoints skip those buffers?

> I trust that a shutdown checkpoint, at least, would write such pages?

Yes.

> If so, I'd expect that a manual checkpoint would do it too. Maybe
> I'm checkpointing because I want to be sure that the shutdown will be
> quick so I can do a minor release update with minimal downtime.

That's a fair argument.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services