Content-Type: multipart/mixed; boundary="------------6Pv1hP3ToLdc0VlXT2rEXvNP"
Message-ID: <d47d8122-415e-425c-d0a2-e0160829702d@iki.fi>
Date: Thu, 27 Jan 2022 19:55:45 +0200
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.4.1
Content-Language: en-US
From: Heikki Linnakangas <hlinnaka@iki.fi>
Subject: Creation of an empty table is not fsync'd at checkpoint
To: pgsql-hackers <pgsql-hackers@postgresql.org>
Archived-At: 
 <https://www.postgresql.org/message-id/d47d8122-415e-425c-d0a2-e0160829702d%40iki.fi>
Precedence: bulk

This is a multi-part message in MIME format.
--------------6Pv1hP3ToLdc0VlXT2rEXvNP
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

If you create an empty table, it is not fsync'd. As soon as you insert a 
row to it, register_dirty_segment() gets called, and after that, the 
next checkpoint will fsync it. But before that, the creation itself is 
never fsync'd. That's obviously not great.

The lack of an fsync is a bit hard to prove because it requires a 
hardware failure, or a simulation of it, and can be affected by 
filesystem options too. But I was able to demonstrate a problem with 
these steps:

1. Create a VM with two virtual disks. Use ext4, with 'data=writeback' 
option (I'm not sure if that's required). Install PostgreSQL on one of 
the virtual disks.

2. Start the server, and create a tablespace on the other disk:

CREATE TABLESPACE foospc LOCATION '/data/heikki';

3. Do this:

CREATE TABLE foo (i int) TABLESPACE foospc;
CHECKPOINT;

4. Immediately after that, kill the VM. I used:

killall -9 qemu-system-x86_64

5. Restart the VM, restart PostgreSQL. Now when you try to use the 
table, you get an error:

postgres=# select * from crashtest ;
ERROR:  could not open file "pg_tblspc/81921/PG_15_202201271/5/98304": 
No such file or directory

I was not able to reproduce this without the tablespace on a different 
virtual disk, I presume because ext4 orders the writes so that the 
checkpoint implicitly always flushes the creation of the file to disk. I 
tried data=writeback but it didn't make a difference. But with a 
separate disk, it happens every time.

I think the simplest fix is to call register_dirty_segment() from 
mdcreate(). As in the attached. Thoughts?

- Heikki
--------------6Pv1hP3ToLdc0VlXT2rEXvNP
Content-Type: text/x-patch; charset=UTF-8;
 name="0001-Ensure-that-creation-of-an-empty-relfile-is-fsync-d-.patch"
Content-Disposition: attachment;
 filename*0="0001-Ensure-that-creation-of-an-empty-relfile-is-fsync-d-.pa";
 filename*1="tch"
Content-Transfer-Encoding: base64

RnJvbSAwNzViNTlmNGMzMGMwYTlkZTY2ZmU3MmNlNDljYTQwYjVhOTM4OGY5IE1vbiBTZXAg
MTcgMDA6MDA6MDAgMjAwMQpGcm9tOiBIZWlra2kgTGlubmFrYW5nYXMgPGhlaWtraS5saW5u
YWthbmdhc0Bpa2kuZmk+CkRhdGU6IFRodSwgMjcgSmFuIDIwMjIgMTk6NTI6MzcgKzAyMDAK
U3ViamVjdDogW1BBVENIIDEvMV0gRW5zdXJlIHRoYXQgY3JlYXRpb24gb2YgYW4gZW1wdHkg
cmVsZmlsZSBpcyBmc3luYydkIGF0CiBjaGVja3BvaW50LgoKSWYgeW91IGNyZWF0ZSBhIHRh
YmxlIGFuZCBkb24ndCBpbnNlcnQgYW55IGRhdGEgaW50byBpdCwgdGhlIHJlbGF0aW9uIGZp
bGUKaXMgbmV2ZXIgZnN5bmMnZC4gWW91IGRvbid0IGxvc2UgZGF0YSwgYmVjYXVzZSBhbiBl
bXB0eSB0YWJsZSBkb2Vzbid0IGhhdmUKYW55IGRhdGEgdG8gYmVnaW4gd2l0aCwgYnV0IGlm
IHlvdSBjcmFzaCBhbmQgbG9zZSB0aGUgZmlsZSwgc3Vic2VxdWVudApvcGVyYXRpb25zIG9u
IHRoZSB0YWJsZSB3aWxsIGZhaWwgd2l0aCAiY291bGQgbm90IG9wZW4gZmlsZSIgZXJvcnIu
CgpUbyBmaXgsIHJlZ2lzdGVyIGFuIGZzeW5jIHJlcXVlc3QgaW4gbWRjcmVhdGUoKSwgbGlr
ZSB3ZSBkbyBmb3IgbWR3cml0ZSgpLgotLS0KIHNyYy9iYWNrZW5kL3N0b3JhZ2Uvc21nci9t
ZC5jIHwgMyArKysKIDEgZmlsZSBjaGFuZ2VkLCAzIGluc2VydGlvbnMoKykKCmRpZmYgLS1n
aXQgYS9zcmMvYmFja2VuZC9zdG9yYWdlL3NtZ3IvbWQuYyBiL3NyYy9iYWNrZW5kL3N0b3Jh
Z2Uvc21nci9tZC5jCmluZGV4IGQyNmM5MTVmOTBlLi4yZGZkODBjYTY2YiAxMDA2NDQKLS0t
IGEvc3JjL2JhY2tlbmQvc3RvcmFnZS9zbWdyL21kLmMKKysrIGIvc3JjL2JhY2tlbmQvc3Rv
cmFnZS9zbWdyL21kLmMKQEAgLTIyNSw2ICsyMjUsOSBAQCBtZGNyZWF0ZShTTWdyUmVsYXRp
b24gcmVsbiwgRm9ya051bWJlciBmb3JrTnVtLCBib29sIGlzUmVkbykKIAltZGZkID0gJnJl
bG4tPm1kX3NlZ19mZHNbZm9ya051bV1bMF07CiAJbWRmZC0+bWRmZF92ZmQgPSBmZDsKIAlt
ZGZkLT5tZGZkX3NlZ25vID0gMDsKKworCWlmICghU21ncklzVGVtcChyZWxuKSkKKwkJcmVn
aXN0ZXJfZGlydHlfc2VnbWVudChyZWxuLCBmb3JrTnVtLCBtZGZkKTsKIH0KIAogLyoKLS0g
CjIuMzAuMgoK

--------------6Pv1hP3ToLdc0VlXT2rEXvNP--