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

The following bug has been logged on the website:

Bug reference:      10675
Logged by:          Maxim Boguk
Email address:      maxim.boguk@gmail.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.