From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mike Broers <mbroers@gmail.com>
cc: pgsql-admin@postgresql.org, pgsql-hackers@postgresql.org
Subject: Re: after 9.2.4 patch vacuumdb -avz not analyzing all tables
In-reply-to: 
 <CAB9893het38CbHoHOt=GZ5tuUnb77EauGidjHsyy7Mqu_4_P9w@mail.gmail.com>
References: 
 <CAB9893het38CbHoHOt=GZ5tuUnb77EauGidjHsyy7Mqu_4_P9w@mail.gmail.com>
Comments: In-reply-to Mike Broers <mbroers@gmail.com>
	message dated "Thu, 11 Apr 2013 10:49:19 -0500"
Date: Thu, 11 Apr 2013 19:59:14 -0400
Message-ID: <28473.1365724754@sss.pgh.pa.us>
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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