Breakage with VACUUM ANALYSE + partitions

From: Thom Brown <thom(at)linux(dot)com>
To: pgsql-bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Breakage with VACUUM ANALYSE + partitions
Date: 2016-03-20 19:55:26
Message-ID: CAA-aLv6Dp_ZsV-44QA-2zgkqWKQq=GedBX2dRSrWpxqovXK=Pg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Hi,

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

Looking at the file, I get:

File: ‘35173’
Size: 0 Blocks: 0 IO Block: 4096 regular empty file
Device: 801h/2049d Inode: 4504115 Links: 1
Access: (0600/-rw-------) Uid: (1000001/ thom) Gid: (1000001/ thom)
Access: 2016-03-20 19:37:50.433485085 +0000
Modify: 2016-03-20 19:39:49.422959222 +0000
Change: 2016-03-20 19:39:49.422959222 +0000
Birth: -

This is the same file info I get if I just to a regular VACUUM
instead, but that doesn't result in the error. And if I follow that
up with a VACUUM ANALYSE, the error still doesn't occur.

The base directory also shows this listing for files name after the
affected filenode:

-rw------- 1 thom thom 0 Mar 20 19:39 35173
-rw------- 1 thom thom 0 Mar 20 19:39 35173.1
-rw------- 1 thom thom 16384 Mar 20 19:39 35173_fsm
-rw------- 1 thom thom 0 Mar 20 19:39 35173_vm

After I see the error, I get the same error again if I then try to run
plain VACUUM.

The oid 35173 shown in this error message references the parent
pgbench_accounts table.

If I reduce the scale factor to 50, or increase shared_buffers to
512MB, there's no error. If, instead of VACUUM ANALYSE, I just use
VACUUM, or just ANALYSE, the error doesn't happen.

System details:
OS: Linux Mint Debian Edition (Wheezy 7.0)
Linux Kernel: 3.11.8
RAM: 16GB
Filesystem: ext4 (rw,noatime,discard,errors=remount-ro,data=ordered)

Regard

Thom

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Artur Zakirov 2016-03-21 08:28:52 Re: BUG #14032: trigram index is not used for '=' operator
Previous Message vendforce 2016-03-20 15:55:41 BUG #14037: Move history file

Browse pgsql-hackers by date

  From Date Subject
Next Message Tomas Vondra 2016-03-20 20:47:14 Re: Parallel Aggregate
Previous Message Andrew Dunstan 2016-03-20 18:43:17 Re: VS 2015 support in src/tools/msvc