[BUGS] Possible Bug?
We are running pg 7.1.3. x86 Redhat 6.1 with 2.4.17 and glibc 2.2.2. We have several s/w subsystems which keep some pg backends open for a VERY period of time. (Well the whole time the machine is powered up.), and others which have a short lifetime when connected to pg backends. The total amount of data stored within the database is relatively small < 30Mb. We have seen occurences when pg runs out of disk space (currently it has approx 90Mb allocated to it) and fails. In some circumstances we have noticed that some backends still have file descriptors open to /pg_xlog/* files even though they have obviously been deleted some while ago when that wal file was no longer referenced. Is this expected behaviour? Or just an artifact that the backend life is prolonged in our use of pg. We have started experimenting with wal_files and checkpoint_segment settings to see how that affects both the number of wal file created and whether or not they get deleted correctly. At the time of writing, even with wal_files =1 and checkpoint_segment =1 pg appeared to have one current wal file and one which had been deleted and still referenced by a backend. (You can observer these by "ls -l /proc/*/fd/* | grep xlog" ) eg: lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/1576/fd/36 -> /db/live/pg_xlog/0002 (deleted) lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/1613/fd/36 -> /db/live/pg_xlog/ (deleted) lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/1672/fd/36 -> /db/live/pg_xlog/ (deleted) lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/1763/fd/36 -> /db/live/pg_xlog/ (deleted) lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/1803/fd/26 -> /db/live/pg_xlog/ (deleted) lrwx-- 1 postgres postgres 64 Apr 22 21:20 /proc/3241/fd/36 -> /db/live/pg_xlog/0000 (deleted) TIA Francis Reader Imerge Limited Tel :- +44 (0)1954 783600 Unit 6 Bar Hill Business Park Fax :- +44 (0)1954 783601 Saxon Way Web :- http://www.imerge.co.uk Bar Hill Cambridge CB3 8SL United Kingdom ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [BUGS] Possible Bug? TEST CASE
The following is a simple test case to demonstrate a backend not closing a file descriptor to wal file, under Linux 2.4.17 and PG 7.1.3 In session1: Using psql cmd, create a table. Perform an insert on that table. In session2: Issue psql run "ls -l /proc/*/fd/* | grep pg_xlog" This will list all of the backends which have open any wal files. (The process for session1: can be observed at this point.) In session3: Generate lots of large transactions that force a new wal file to be created and used. Once the new wal file has started to be used by the actions performed in session3, stop all activity. After the CHECKPOINT_TIMEOUT time has been reached, the backend which had the original pqsl connection (session1:) now has had its wal file deleted from underneath it. running "ls -l /proc/*/fd/* | grep pg_xlog" will show that. If the database is in its own partition, running "df" and "du -c" will whow differing totals. To conclude, if you have many clients whose backends have infrequent write accesses and then long periods of inactivity, but whose connection to the database is left open, and other backends that are active which march on and create new wal files, many "(deleted)" wal can be created which are ONLY cleared up, with the closing of the backend or when another write access on the db from that backend. We believe that in 7.2, this issue can be avoided by precreating the a set number of wal files, and new wal file will rename the older one. (The file descriptors to these files will point to the new renamed wal file, rather than the old name.) As another point, is there a way to TURN OFF WAL completely Francis Reader Imerge Limited Tel :- +44 (0)1954 783600 Unit 6 Bar Hill Business Park Fax :- +44 (0)1954 783601 Saxon Way Web :- http://www.imerge.co.uk Bar Hill Cambridge CB3 8SL United Kingdom ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
