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)? 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
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
--
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:
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:
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. +