Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > Tom Lane wrote: >> On further thought though, that's not the whole story, and in fact >> VACUUM itself isn't doing very well at accounting for in-doubt tuples. > How about this: let's have VACUUM send a message at the start of > processing the table. pgstats saves the current counters for the table > somewhere and resets them to zero; and any transaction that sends > messages after that is counted to the new counter. > When vacuum finishes and commits, it sends another message and pgstats > forgets the counters it saved. At this point, the count of dead tuples > will be correct. (If during vacuum anyone retrieves the number of dead > tuples, the logical thing would be to report the saved counter). No, that doesn't work (not to mention that adding two more counters per-table will be a lot of bloat for the stats tables). The race conditions are a lot more subtle than that. The stats collector cannot know when it receives a tabstat message after VACUUM starts whether VACUUM has/will see the tuples involved, or whether it will see them as committed or not. That would depend on whether VACUUM has yet reached the page(s) the tuples are in. (Conversely tabstats arriving shortly after the VACUUM completion report might or might not correspond to tuples seen by VACUUM, though neither your proposal nor mine tries to address that.) AFAICS the only way to be 100% correct would be to track live/dead counts on a page-by-page basis, which is obviously impractical. (And I'm not sure even that works, given the possibility of stats messages arriving at the collector out-of-order compared to the actual page-changing operations.) So we have to settle for an approximation, and that being the case I'd rather not have an expensive approximation. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

Simon Riggs <simon@2ndquadrant.com> writes: > On Mon, 2007-11-19 at 10:38 -0500, Tom Lane wrote: >> The race conditions are a lot more subtle than that. The stats >> collector cannot know when it receives a tabstat message after VACUUM >> starts whether VACUUM has/will see the tuples involved, or whether it >> will see them as committed or not. That would depend on whether VACUUM >> has yet reached the page(s) the tuples are in. > I think the before-and-after approach can be made to work: > VACUUM just needs to save the counter in memory, it doesn't need to > write that anywhere else. > VACUUM can force the flush of the tabstat file so that there is no race > condition, or at least a minimised one. I don't think you understood what I said at all. The race condition is not "before vs after VACUUM starts", it is "before vs after when VACUUM scans the page that the in-doubt tuple is in". regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > Tom Lane wrote: >> Should we attempt to adjust VACUUM's accounting as well, or leave it >> for 8.4? For that matter, should adjusting ANALYZE be left for 8.4? >> Thoughts? > Has this issue been a real problem? If so, probably we should consider > adjusting ANALYZE for 8.3 per your proposal. I'm not sure. Upthread, two or three people said they thought they'd seen autovac launching vacuums against tables during bulk inserts. However, that could only happen if there were already a reason to launch an auto-analyze (which could misreport dead tuples and thus trigger a subsequent auto-vacuum), and in typical bulk load situations I don't see why that would be very likely to happen. I'm fine with leaving the whole issue for 8.4. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match