Thread: BUG #8697: checkpoint cann't flush unlogged table's dirty page to disk.
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)
Re: BUG #8697: checkpoint cann't flush unlogged table's dirty page to disk.
From
Andres Freund
Date:
Hi, On 2013-12-23 09:41:07 +0000, digoal@126.com wrote: > checkpoint cann't flush unlogged table's dirty page to disk. > digoal=# create table u(id int); > digoal=# update pg_class set relpersistence ='u' where relname='u'; > digoal=# insert into u select generate_series(1,100000); > digoal=# checkpoint; > 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'; > digoal=# insert into u select generate_series(1,100000); > digoal=# checkpoint; > 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) I am confused. It certainly isn't allowed to simply update random pg_catalog.* tables - it will frequently break stuff. Why are you expecting that to work? Or are you simply trying to prove some other point? Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services