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

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

On Tue, Mar 05, 2019 at 06:38:02AM +0000, PG Bug reporting form wrote:
> I guess that "Permission denied" is caused by antivirus software.
> However, this error caused data inconsistency in the table.

Most likely so.  Please be very careful to disable that where Postgres
is running or apply filters.  Windows is not concurrent safe by
default when it comes to handle writes, and Postgres uses a wrapper
for open() which makes use of some concurrent-safe options when
handling files to avoid such problems.
--
Michael

Hi,

On 2019-03-06 11:56:26 +0900, Michael Paquier wrote:
> Windows is not concurrent safe by default when it comes to handle
> writes, and Postgres uses a wrapper for open() which makes use of some
> concurrent-safe options when handling files to avoid such problems.

I don't think this has anything to do with the problem. Isn't this the
known problem around truncation that Tom has recently talked about fixing?

[1] https://www.postgresql.org/message-id/2348.1544474335%40sss.pgh.pa.us

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> I don't think this has anything to do with the problem. Isn't this the
> known problem around truncation that Tom has recently talked about fixing?
> [1] https://www.postgresql.org/message-id/2348.1544474335%40sss.pgh.pa.us

Yeah, it sure looks like that same old issue to me.

            regards, tom lane

Thank you for reply.

> Andres Freund <andres@anarazel.de> writes:
> > I don't think this has anything to do with the problem. Isn't this the
> > known problem around truncation that Tom has recently talked about fixing?
> > [1] https://www.postgresql.org/message-id/2348.1544474335%40sss.pgh.pa.us
>
> Yeah, it sure looks like that same old issue to me.

I understand.
I hope that this problem will be fixed.


Regards,
Akira Kurosawa

On Thu, Mar 07, 2019 at 03:17:28PM +0000, Akira Kurosawa wrote:
>> Andres Freund <andres@anarazel.de> writes:
>> > I don't think this has anything to do with the problem. Isn't this the
>> > known problem around truncation that Tom has recently talked about fixing?
>> > [1] https://www.postgresql.org/message-id/2348.1544474335%40sss.pgh.pa.us
>>
>> Yeah, it sure looks like that same old issue to me.
>
> I understand.
> I hope that this problem will be fixed.

Of course.  Thanks for the reminder, Tom and Andres.
--
Michael