From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Broers <mbroers@gmail.com>
Cc: pgsql-admin@postgresql.org
Cc: pgsql-hackers@postgresql.org
Subject: Re: after 9.2.4 patch vacuumdb -avz not analyzing all tables
Date: Thu, 11 Apr 2013 19:59:14 -0400
Message-ID: <28473.1365724754@sss.pgh.pa.us> (raw)
In-Reply-To: <CAB9893het38CbHoHOt=GZ5tuUnb77EauGidjHsyy7Mqu_4_P9w@mail.gmail.com>
References: <CAB9893het38CbHoHOt=GZ5tuUnb77EauGidjHsyy7Mqu_4_P9w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
Mike Broers <mbroers@gmail.com> writes:
> After patching to 9.2.4 I am noticing some mysterious behavior in my
> nightly vacuumdb cron job.
> I have been running vacuumdb -avz nightly for a while now, and have a
> script that tells me the next day if all the tables in pg_stat_user_tables
> have been vacuumed and analyzed in the last 24 hours.
> Since the patch some tables do not seem to be getting analyzed. This did
> not happen after I patched from earlier versions of 9.2 like 9.2.1 to
> 9.2.2, 9.2.3.
> I pipe the output of the vacuumdb command to a log file, and there dont
> appear to be any errors, its just not analyzing all the tables as it did
> before. Some tables are still being analyzed ok. Its vacuuming all but a
> few tables that I know are being blocked by idle in transaction sessions.
[ pokes around ... ] You certain 9.2.3 didn't do this too? This
appears to be an intentional behavior of the 9.2.3 patch that made it
cancel truncation when there were conflicting lock requests:
/*
* Report results to the stats collector, too. An early terminated
* lazy_truncate_heap attempt suppresses the message and also cancels the
* execution of ANALYZE, if that was ordered.
*/
if (!vacrelstats->lock_waiter_detected)
pgstat_report_vacuum(RelationGetRelid(onerel),
onerel->rd_rel->relisshared,
new_rel_tuples);
else
vacstmt->options &= ~VACOPT_ANALYZE;
However I've got to say that both of those side-effects of
exclusive-lock abandonment seem absolutely brain dead now that I see
them. Why would we not bother to tell the stats collector what we've
done? Why would we think we should not do ANALYZE when we were told to?
Would someone care to step forward and defend this behavior? Because
it's not going to be there very long otherwise.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, mbroers@gmail.com, pgsql-hackers@postgresql.org
Subject: Re: after 9.2.4 patch vacuumdb -avz not analyzing all tables
In-Reply-To: <28473.1365724754@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox