BUG #8697: checkpoint cann't flush unlogged table's dirty page to disk. - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8697
Logged by:          digoal.zhou
Email address:      digoal@126.com
PostgreSQL version: 9.3.2
Operating system:   CentOS 5.x x64
Description:

checkpoint cann't flush unlogged table's dirty page to disk.
digoal=# create table u(id int);
CREATE TABLE
-- update table u to unlogged table
-- and insert some data
digoal=# update pg_class set relpersistence ='u' where relname='u';
UPDATE 1
digoal=# insert into u select generate_series(1,100000);
INSERT 0 100000
-- then query the file stats.
digoal=# select * from pg_stat_file(pg_relation_filepath('u')),
pg_relation_filepath('u');
  size   |         access         |      modification      |         change
       | creation | isdir | pg_relation_filepath

---------+------------------------+------------------------+------------------------+----------+-------+----------------------
 3629056 | 2013-12-23 17:36:54+08 | 2013-12-23 17:37:02+08 | 2013-12-23
17:37:02+08 |          | f     | base/16399/122891
(1 row)
-- then checkpoint,
digoal=# checkpoint;
CHECKPOINT
-- and then query the file stats ,
-- we can see the stats no change.
-- so checkpoint cann't flush unlogged table's dirty page.
digoal=# select * from pg_stat_file(pg_relation_filepath('u')),
pg_relation_filepath('u');
  size   |         access         |      modification      |         change
       | creation | isdir | pg_relation_filepath

---------+------------------------+------------------------+------------------------+----------+-------+----------------------
 3629056 | 2013-12-23 17:36:54+08 | 2013-12-23 17:37:02+08 | 2013-12-23
17:37:02+08 |          | f     | base/16399/122891
(1 row)
-- then change table u to normal table
digoal=# update pg_class set relpersistence ='p' where relname='u';
UPDATE 1
-- then insert some data to u
digoal=# insert into u select generate_series(1,100000);
INSERT 0 100000
-- and then checkpoint
digoal=# checkpoint;
CHECKPOINT
-- we can see checkpoint flush the normal table's dirty page to disk
digoal=# select * from pg_stat_file(pg_relation_filepath('u')),
pg_relation_filepath('u');
  size   |         access         |      modification      |         change
       | creation | isdir | pg_relation_filepath

---------+------------------------+------------------------+------------------------+----------+-------+----------------------
 7249920 | 2013-12-23 17:36:54+08 | 2013-12-23 17:37:29+08 | 2013-12-23
17:37:29+08 |          | f     | base/16399/122891
(1 row)
-- or use this method , change the table to logged table after insert data
digoal=# update pg_class set relpersistence ='u' where relname='u';
UPDATE 1
digoal=# insert into u select generate_series(1,100000);
INSERT 0 100000
digoal=# update pg_class set relpersistence ='p' where relname='u';
UPDATE 1
digoal=# checkpoint;
CHECKPOINT
digoal=# select * from pg_stat_file(pg_relation_filepath('u')),
pg_relation_filepath('u');
   size   |         access         |      modification      |         change
        | creation | isdir | pg_relation_filepath

----------+------------------------+------------------------+------------------------+----------+-------+----------------------
 10878976 | 2013-12-23 17:36:54+08 | 2013-12-23 17:40:16+08 | 2013-12-23
17:40:16+08 |          | f     | base/16399/122891
(1 row)