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

On 2014-06-18 09:45:46 -0400, Tom Lane wrote:
> Andres Freund <andres@2ndquadrant.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