Обсуждение: Stats not updated after rollback -- autovacuum confused.
Hello, I have a system where there are mostly COPYs, which insert data into a table. Ocasionally a COPY will fail (and thus, dead rows appear), but as far as I can tell ROLLBACK is not reflected anywhere in the pg_stats_user_tables. And since there are no rows n_tup_upd or n_tup_del, therefore autovacuum will not fire for that table. I see two possible solutions:1) let rollback increment both n_tup_ins and n_tup_del (or maybe n_tup_upd, at least)? Thiswould be a good safeguard, I guess. 2) ANALYZE is able to see wether table is accumulating dead rows. It might be a good idea to make ANALYZE able hint autovacuum that some tables need VACUUM (that they exceed limits set for autovacuum). The 2nd point could be a TODO item, perhaps? Something like: When ANALYZE runs, make it note removable dead rows and non-removable dead rows. If removable dead rows exceed some threshold, hint autovacuum at that table. Regards, Dawid
This has been saved for the 8.4 release: http://momjian.postgresql.org/cgi-bin/pgpatches_hold --------------------------------------------------------------------------- Dawid Kuroczko wrote: > Hello, I have a system where there are mostly COPYs, > which insert data into a table. Ocasionally a COPY will fail (and thus, > dead rows appear), but as far as I can tell ROLLBACK is not reflected > anywhere in the pg_stats_user_tables. And since there are no rows > n_tup_upd or n_tup_del, therefore autovacuum will not fire for that table. > > I see two possible solutions: > 1) let rollback increment both n_tup_ins and n_tup_del (or maybe > n_tup_upd, at least)? This would be a good safeguard, I guess. > > 2) ANALYZE is able to see wether table is accumulating dead rows. > It might be a good idea to make ANALYZE able hint autovacuum that > some tables need VACUUM (that they exceed limits set for autovacuum). > > The 2nd point could be a TODO item, perhaps? Something like: > When ANALYZE runs, make it note removable dead rows and non-removable > dead rows. If removable dead rows exceed some threshold, hint autovacuum > at that table. > > Regards, > Dawid > > ---------------------------(end of broadcast)--------------------------- > TIP 7: You can help support the PostgreSQL project by donating at > > http://www.postgresql.org/about/donate -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian escribió: > > This has been saved for the 8.4 release: > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold FWIW this has been fixed in 8.3, you can drop the item from the 8.4 queue. Thanks. > --------------------------------------------------------------------------- > > Dawid Kuroczko wrote: > > Hello, I have a system where there are mostly COPYs, > > which insert data into a table. Ocasionally a COPY will fail (and thus, > > dead rows appear), but as far as I can tell ROLLBACK is not reflected > > anywhere in the pg_stats_user_tables. And since there are no rows > > n_tup_upd or n_tup_del, therefore autovacuum will not fire for that table. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Removed. --------------------------------------------------------------------------- Alvaro Herrera wrote: > Bruce Momjian escribi?: > > > > This has been saved for the 8.4 release: > > > > http://momjian.postgresql.org/cgi-bin/pgpatches_hold > > FWIW this has been fixed in 8.3, you can drop the item from the 8.4 > queue. Thanks. > > > > --------------------------------------------------------------------------- > > > > Dawid Kuroczko wrote: > > > Hello, I have a system where there are mostly COPYs, > > > which insert data into a table. Ocasionally a COPY will fail (and thus, > > > dead rows appear), but as far as I can tell ROLLBACK is not reflected > > > anywhere in the pg_stats_user_tables. And since there are no rows > > > n_tup_upd or n_tup_del, therefore autovacuum will not fire for that table. > > > -- > Alvaro Herrera http://www.CommandPrompt.com/ > PostgreSQL Replication, Consulting, Custom Development, 24x7 support > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Alvaro Herrera <alvherre@commandprompt.com> writes: > FWIW this has been fixed in 8.3, you can drop the item from the 8.4 > queue. Thanks. There are a couple of other things on that page that seem already applied, for instance hashing for numeric and an early form of the seq scan ringbuffer patch. While we're griping, http://momjian.us/pgrelease/ is a bit behind the times... regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@commandprompt.com> writes: > > FWIW this has been fixed in 8.3, you can drop the item from the 8.4 > > queue. Thanks. > > There are a couple of other things on that page that seem already > applied, for instance hashing for numeric and an early form of the > seq scan ringbuffer patch. Removed. > While we're griping, > http://momjian.us/pgrelease/ > is a bit behind the times... Updated for 8.3 though some of the items aren't active until we enter beta. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +