Re: Breakage with VACUUM ANALYSE + partitions - Mailing list pgsql-bugs

On Thu, Mar 24, 2016 at 4:20 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Sun, Mar 20, 2016 at 3:55 PM, Thom Brown <thom@linux.com> wrote:
>> I've not determined what's causing the following issue, but this is
>> the simplest reproducible test case I've managed to create so far in
>> 9.6 latest git master:
>>
>> postgresql.conf:
>>   shared_buffers = 256MB
>>
>> createdb pgbench
>> pgbench -i -s 80 pgbench
>>
>> psql pgbench
>>   \pset pager off
>>   CREATE TABLE pgbench_accounts_1 (CHECK (bid % 2 = 0)) INHERITS
>> (pgbench_accounts);
>>   CREATE TABLE pgbench_accounts_2 (CHECK (bid % 2 = 1)) INHERITS
>> (pgbench_accounts);
>>   WITH del AS (DELETE FROM pgbench_accounts WHERE bid % 2 = 0
>> RETURNING *) INSERT INTO pgbench_accounts_1 SELECT * FROM del;
>>   WITH del AS (DELETE FROM pgbench_accounts WHERE bid % 2 = 1
>> RETURNING *) INSERT INTO pgbench_accounts_2 SELECT * FROM del;
>>   VACUUM ANALYSE;
>>   EXPLAIN ANALYSE SELECT count(*) FROM pgbench_accounts;
>>
>> This last statement produces:
>>   ERROR:  could not read block 0 in file "base/35160/35173": read only
>> 0 of 8192 bytes
>
> Hmm.  The natural thing to suspect here would be a problem with the
> freeze map changes.  But I tried this and I couldn't reproduce it.
> Any chance you can try this on each of the following commits?
>
> fd31cd265138019dcccc9b5fe53043670898bc9f
> 77a1d1e79892a20ed15a67be42b96949b8546bf6
> a892234f830e832110f63fc0a2afce2fb21d1584
> 68c521eb92c3515e3306f51a7fd3f32d16c97524
>
> Make sure to re-initdb each time.

I am able to reproduce the problem with latest HEAD 473b932 code.

During vacuum analyse operation, vacuum truncates the entire table
to 0 bytes because of no records present in the table and invalidates
the smgr relation.

During analyse operation, the smgr relation is constructed with segno = 1
also in the following location. In this case the smgr relation is not
invalidated.

#0  _mdfd_openseg (reln=0x1b8d790, forknum=MAIN_FORKNUM, segno=1,
oflags=0) at md.c:1736
#1  0x00000000007fa032 in _mdfd_getseg (reln=0x1b8d790,
forknum=MAIN_FORKNUM, blkno=131147, skipFsync=0 '\000',
behavior=EXTENSION_RETURN_NULL) at md.c:1815
#2  0x00000000007f8449 in mdwriteback (reln=0x1b8d790,
forknum=MAIN_FORKNUM, blocknum=131147, nblocks=1) at md.c:686
#3  0x00000000007faca3 in smgrwriteback (reln=0x1b8d790,
forknum=MAIN_FORKNUM, blocknum=131147, nblocks=1) at smgr.c:663
#4  0x00000000007c5211 in IssuePendingWritebacks (context=0xe70fc0
<BackendWritebackContext>) at bufmgr.c:4112
#5  0x00000000007c5071 in ScheduleBufferTagForWriteback
(context=0xe70fc0 <BackendWritebackContext>, tag=0x7ff18deaa180) at
bufmgr.c:4041
#6  0x00000000007c0a9d in BufferAlloc (smgr=0x1b8d490,
relpersistence=112 'p', forkNum=MAIN_FORKNUM, blockNum=247,
strategy=0x1bded48, foundPtr=0x7ffea3f0f34f "") at bufmgr.c:1134
#7  0x00000000007bff58 in ReadBuffer_common (smgr=0x1b8d490,
relpersistence=112 'p', forkNum=MAIN_FORKNUM, blockNum=247,
mode=RBM_NORMAL, strategy=0x1bded48, hit=0x7ffea3f0f43b "")
    at bufmgr.c:744
#8  0x00000000007bfd6e in ReadBufferExtended (reln=0x7ff19dc69280,
forkNum=MAIN_FORKNUM, blockNum=247, mode=RBM_NORMAL,
strategy=0x1bded48) at bufmgr.c:663
#9  0x00000000005e09f7 in acquire_sample_rows (onerel=0x7ff19dc69280,
elevel=13, rows=0x1be7f28, targrows=15000, totalrows=0x7ffea3f0f598,
totaldeadrows=0x7ffea3f0f590)
    at analyze.c:1025
#10 0x00000000005e1877 in acquire_inherited_sample_rows
(onerel=0x7ff19dc76a78, elevel=13, rows=0x1be7f28, targrows=30000,
totalrows=0x7ffea3f0f780, totaldeadrows=0x7ffea3f0f778)
    at analyze.c:1410
#11 0x00000000005dfa68 in do_analyze_rel (onerel=0x7ff19dc76a78,
options=3, params=0x7ffea3f0fab0, va_cols=0x0, acquirefunc=0x5e08ca
<acquire_sample_rows>, relpages=0, inh=1 '\001',
    in_outer_xact=0 '\000', elevel=13) at analyze.c:486
#12 0x00000000005df276 in analyze_rel (relid=16463, relation=0x0,
options=3, params=0x7ffea3f0fab0, va_cols=0x0, in_outer_xact=0 '\000',
bstrategy=0x1bded48) at analyze.c:264


So further operations on the table uses the already constructed smgr relation
and treats that there are RELSEG_SIZE number of blocks in the page and try
to do the scan. But there are 0 pages in the table thus it produces the error.

The issue doesn't occur from another session. Because of this reason only
if we do only vacuum operation, the error not occurred.

Regards,
Hari Babu
Fujitsu Australia