Message-ID: <53032202.8020405@vmware.com>
Date: Tue, 18 Feb 2014 11:04:02 +0200
From: Heikki Linnakangas <hlinnakangas@vmware.com>
Organization: VMware
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20131103 Icedove/17.0.10
MIME-Version: 1.0
To: Jim Nasby <jim@nasby.net>
CC: Jaime Casanova <jaime@2ndquadrant.com>, Bruce Momjian <bruce@momjian.us>,
 Robert Haas <robertmhaas@gmail.com>, Kevin Grittner <kgrittn@mail.com>,
 Amit Kapila <amit.kapila@huawei.com>, Josh Berkus <josh@agliodbs.com>,
 "pgsql-hackers@postgresql.org" <pgsql-hackers@postgresql.org>
Subject: Re: 9.2.1 & index-only scans : abnormal heap fetches after VACUUM
 FULL
References: <20130112191404.255800@gmx.com> <20131127213320.GA3785@momjian.us>
 <CA+TgmoYBnQfS4ACVmAq9i4KQ+EOJo053oHhsfBkWEnLNCbsu5Q@mail.gmail.com>
 <20131128223805.GC20216@momjian.us> <20131203162556.GC27105@momjian.us>
 <CAJKUy5jUgCFxz=zdZ5esqcpKmunTpA+-Hj_s5hRD8ep8B4TOYg@mail.gmail.com>
 <53011D60.8020005@nasby.net>
In-Reply-To: <53011D60.8020005@nasby.net>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-hackers-owner@postgresql.org

On 02/16/2014 10:19 PM, Jim Nasby wrote:
> On 1/24/14, 3:52 PM, Jaime Casanova wrote:
>> On Tue, Dec 3, 2013 at 11:25 AM, Bruce Momjian<bruce@momjian.us>  wrote:
>>>>
>>>> Is everyone else OK with this approach?  Updated patch attached.
>>>>
>> Hi,
>>
>> I started to look at this patch and i found that it fails an assertion
>> as soon as you run a VACUUM FULL after a lazy VACUUM even if those are
>> on unrelated relations. For example in an assert-enabled build with
>> the regression database run:
>>
>> VACUUM customer;
>> [... insert here whatever commands you like or nothing at all ...]
>> VACUUM FULL tenk1;
>
> Is anyone else confused/concerned that regression testing didn't pick this up?

I wouldn't expect that to be explicitly tested - it's pretty unexpected 
that they work independently but not when run one after another. But 
it's a bit surprising that we don't happen to do that combination in any 
of the tests by pure chance.

> The vacuum.sql test does not test lazy vacuum at all, and I can't seem to find any other tests that test lazy vacuum either...

There are several lazy vacuums in the regression suite:

sql/alter_table.sql:vacuum analyze atacc1(a);
sql/alter_table.sql:vacuum analyze atacc1("........pg.dropped.1........");
sql/hs_standby_disallowed.sql:VACUUM hs2;
sql/indirect_toast.sql:VACUUM FREEZE toasttest;
sql/indirect_toast.sql:VACUUM FREEZE toasttest;
sql/matview.sql:VACUUM ANALYZE hogeview;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_add;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_sub;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_div;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_mul;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_sqrt;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_ln;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_log10;
sql/numeric_big.sql:VACUUM ANALYZE num_exp_power_10_ln;
sql/numeric.sql:VACUUM ANALYZE num_exp_add;
sql/numeric.sql:VACUUM ANALYZE num_exp_sub;
sql/numeric.sql:VACUUM ANALYZE num_exp_div;
sql/numeric.sql:VACUUM ANALYZE num_exp_mul;
sql/numeric.sql:VACUUM ANALYZE num_exp_sqrt;
sql/numeric.sql:VACUUM ANALYZE num_exp_ln;
sql/numeric.sql:VACUUM ANALYZE num_exp_log10;
sql/numeric.sql:VACUUM ANALYZE num_exp_power_10_ln;
sql/sanity_check.sql:VACUUM;
sql/without_oid.sql:VACUUM ANALYZE wi;
sql/without_oid.sql:VACUUM ANALYZE wo;

Most of those commands are there to analyze, rather than vacuum, but 
lazy vacuum is definitely exercised by the regression tests. I agree 
it's quite surprising that vacuum.sql doesn't actually perform any lazy 
vacuums.

- Heikki


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers