BUG #15667: "could not truncate file" error caused deleted rows to become visible

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: aki-kurosawa(at)vt(dot)jp(dot)nec(dot)com
Subject: BUG #15667: "could not truncate file" error caused deleted rows to become visible
Date: 2019-03-05 06:38:02
Message-ID: 15667-8d3fca4eba25174f@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 15667
Logged by: Akira Kurosawa
Email address: aki-kurosawa(at)vt(dot)jp(dot)nec(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2019-03-05 09:05:55 BUG #15668: Server crash in transformPartitionRangeBounds
Previous Message Michael Paquier 2019-03-04 22:44:50 Re: BUG #15665: ERROR: could not load library "/usr/pgsql-11/lib/oracle_fdw.so"