PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15667
PG Version10.3
OSWindows Server 2016 Standard
Opened2019-03-05 06:38:02+00
Reported byAkira Kurosawa
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15667
Logged by:          Akira Kurosawa
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-03-05 06:38:02+00PG Bug reporting formBUG #15667: "could not truncate file" error caused deleted rows to become visible
2019-03-06 02:56:26+00Michael PaquierRe: BUG #15667: "could not truncate file" error caused deleted rows to become visible
2019-03-06 03:04:59+00Andres FreundRe: BUG #15667: "could not truncate file" error caused deleted rows to become visible
2019-03-06 04:42:38+00Tom LaneRe: BUG #15667: "could not truncate file" error caused deleted rows to become visible
2019-03-07 15:17:28+00Akira KurosawaRe: BUG #15667: "could not truncate file" error caused deleted rows to become visible
2019-03-08 01:39:40+00Michael PaquierRe: BUG #15667: "could not truncate file" error caused deleted rows to become visible