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

On Tue, Jun 17, 2014 at 8:54 AM, <maxim(dot)boguk(at)gmail(dot)com> wrote:

> The following bug has been logged on the website:
>
> Bug reference: 10675
> Logged by: Maxim Boguk
> Email address: maxim(dot)boguk(at)gmail(dot)com
> PostgreSQL version: 9.3.4
> Operating system: Linux (Ubuntu)
> Description:
>
> Hi,
>
> Now bug report with easy/short test case.
>
> PostgreSQL seems doesn't flush dirty buffers related to unlogged tables in
> the database during alter database set tablespace ...;
>
> Test case:
>
> mboguk=# create database test tablespace tmp;
> CREATE DATABASE
> mboguk=# \c test
> You are now connected to database "test" as user "mboguk".
> test=# create unlogged table test (id integer);
> CREATE TABLE
> test=# insert into test select * from generate_series(1,10000000);
> INSERT 0 10000000
> test=# \c postgres
> You are now connected to database "postgres" as user "mboguk".
> postgres=# alter database test set tablespace pg_default;
> ALTER DATABASE
> postgres=# checkpoint;
> ERROR: checkpoint request failed
> HINT: Consult recent messages in the server log for details.
>
> In PostgreSQL logs:
>
> 2014-06-16 23:16:41 EDT ERROR: could not open file
> "pg_tblspc/16558/PG_9.3_201306121/16559/16560": No such file or directory
> 2014-06-16 23:16:41 EDT CONTEXT: writing block 27059 of relation
> pg_tblspc/16558/PG_9.3_201306121/16559/16560
> 2014-06-16 23:16:41 EDT WARNING: could not write block 27059 of
> pg_tblspc/16558/PG_9.3_201306121/16559/16560
> 2014-06-16 23:16:41 EDT DETAIL: Multiple failures --- write error might be
> permanent.
>
>
Thanks for the report. I can reproduce this on the current HEAD as well
albeit not with the exact same steps. For me, it happens during the
shutdown checkpoint.

LOG: shutting down
FATAL: could not open file "pg_tblspc/24576/PG_9.5_201406121/40971/40972":
No such file or directory
CONTEXT: writing block 0 of relation pg_tblspc/24576/PG_9.5_201406121/
40971/40972
WARNING: buffer refcount leak: [193] (rel=pg_tblspc/24576/PG_9.5_201406121/
40971/40972, blockNum=0, flags=0x97, refcount=1 1)
TRAP: FailedAssertion("!(RefCountErrors == 0)", File:
"/home/pavan.deolasee/work/pgsql/postgresql/src/backend/storage/buffer/bufmgr.c",
Line: 1773)
LOG: checkpointer process (PID 2070) was terminated by signal 6: Aborted

It's clearly a bug. During a normal or a forced CHECKPOINT, we don't write
buffers of UNLOGGED tables, even if they are dirty. ALTER DATABASE SET
TABLESPACE relies on the checkpoint mechnism to ensure that all dirty
buffers are written to the disk before proceeding with moving the files to
the new tablespace. Leaving behind those dirty buffers with old tablespace
and old relfilenode causes problems later when we try to sync those dirty
buffers to the disk. AFAICS it can happen during the SHUTDOWN checkpoint or
the End-of-Recovery checkpoint, at least in the HEAD.

ISTM that the right fix is to write *all* dirty pages during a FORCE
checkpoint since system relies on FORCE checkpoints to handle such
alterations. Attached patch fixes this for the HEAD. But this needs to be
fixed all the way to 9.1 when unlogged tables were first introduced.

Thanks,
Pavan

--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee