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

On 2014-08-13 21:57:08 +0900, MauMau wrote:
> From: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
> >>Anyone else has an opinion on this?
> >>
> >>
> >Since I did not hear anything on this, I created a patch that adds a new
> >flag to tell checkpointer to flush all pages to the disk. Tom (and even I)
> >have reservations about the approach, but I would nevertheless leave it to
> >the committer to decide. IMV we must fix this bug one way or the other.
> >Otherwise users face risk of failing to do clean shutdown.
>
> I'm reviewing this patch.
>
> According to the approach of the patch, CREATE DATABASE also needs the new
> flag to sync the unlogged tables before copying them from source database to
> the new one. You need to check other places which do checkpointing.

> I'm with Tom-san: I think it would be better for online checkpoints to sync
> unlogged tables without a new flag. The reasons are:

That'd mean that the next pointrelease will incur *significantly* higher
IO in many setups. If you currently have a workload where all dirty
buffers of unlogged tables fit in s_b you'll never have any OS
writes. That'd completely change.

A compromise would be to couple it to CHECKPOINT_IMMEDIATE and/or _FORCE
instead of creating a new flag - I think this is actually what Tom
proposed.

I'd planned to look (and possibly) commit this patch soonish. If I have
to decide it'd be a new flag.

> * There's a greater danger of losing data during operating system restart.
> For example, IIRC, Windows gives only 20 seconds to terminate all services
> during OS shutdown. If many dirty buffers for unlogged tables linger in the
> shared buffers, PostgreSQL service may fail to complete database shutdown.
> Even if the online checkpoint writes out all dirty buffers, the possibility
> of there being many dirty buffers at shutdown is not zero, but the
> probability would be lower.

Meh. That won't lead to data loss, just recovery on restart. And 20s
isn't sufficient for any halfway busy database anyway.

> * Unlogged tables are, as its name suggests, for better performance by not
> writing WAL.

Pft. They exist because they have higher performance due to lower
durability guarantees. Should we really create CREATE UNLOGGED AND NOT
WRITTEN UNTIL REALLY NECESSARY TABLE ...(...)?

Greetings,

Andres Freund

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