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.
Some additional info:
select oid,* from pg_tablespace where oid=16558;
oid | spcname | spcowner | spcacl | spcoptions
-------+---------+----------+--------+------------
16558 | tmp | 16397 | |
test=# select relname from pg_class where relfilenode=16560;
relname
---------
test
So the database flush dirty shared buffers related to the unlogged table
into the old file location (pre-alter tablespace).
Kind Regards,
Maksym