The following bug has been logged on the website:
Bug reference: 15667
Logged by: Akira Kurosawa
Email address: aki-kurosawa@vt.jp.nec.com
PostgreSQL version: 10.3
Operating system: Windows Server 2016 Standard
Description:
In vacuum after deletion, the following error occurred while trying to
truncate some blocks.
ERROR: could not truncate file "base/37389/42124" to 179 blocks:
Permission denied
CONTEXT: automatic vacuum of table "mydb.myschema.mytable"
I guess that "Permission denied" is caused by antivirus software.
However, this error caused data inconsistency in the table.
ERROR: missing chunk number 0 for toast value 69579 in pg_toast_42124
STATEMENT: SELECT * FROM myschema.mytable
When an error occurs in mdtruncate, buffers of related block has already
been invalidated in DropRelFileNodeBuffers.
If those buffers were dirty, the block image before deletion remains in the
table file.
I guess that because rows that should have been deleted in the table still
exist,
trying to access TOAST data that does not exist.
Following is pseudo reproduction procedure on linux.
----------
PostgreSQL 11.2
autovacuum = off
testdb=# create table sample (c1 int, c2 text, c3 text);
CREATE TABLE
testdb=# insert into sample select generate_series(1, 10, 1), repeat('a',
1000), repeat('a', 1000000);
INSERT 0 10
testdb=# checkpoint;
CHECKPOINT
testdb=# select ctid, c1 from sample;
ctid | c1
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(0,6) | 6
(0,7) | 7
(1,1) | 8
(1,2) | 9
(1,3) | 10
(10 rows)
testdb=# delete from sample where c1 > 5;
DELETE 5
testdb=# select ctid, c1 from sample;
ctid | c1
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(5 rows)
testdb=# vacuum sample;
ERROR: could not truncate file "base/16384/16524" to 1 blocks: Success //
gdb jump (skip FileTruncate)
testdb=# select ctid, c1 from sample;
ctid | c1
-------+----
(0,1) | 1
(0,2) | 2
(0,3) | 3
(0,4) | 4
(0,5) | 5
(1,1) | 8 //
(1,2) | 9 // deleted rows are visible
(1,3) | 10 //
(8 rows)
testdb=# select c3 from sample;
ERROR: missing chunk number 0 for toast value 16537 in pg_toast_16524
