Обсуждение: BUG #13561: "could not write to log file, Bad file descriptor" error when working with huge table

The following bug has been logged on the website:

Bug reference:      13561
Logged by:          Manuel Pallier
Email address:      manuel.pallier@beko.at
PostgreSQL version: 9.4.4
Operating system:   Windows Server 2008 SP2 x64
Description:

I'm having a big problem with a PostreSQL database on a specific machine.
Whenever I do some specific things with a huge table (over 9 million rows)
the whole postgres process crashes with the following entries in the log
file:

PANIC:  could not write to log file 000000010000000000000022 at offset
11673600, length 909312: Bad file descriptor
LOG:  WAL writer process (PID 17056) exited with exit code 3
LOG:  terminating any other active server processes
WARNING:  terminating connection because of crash of another server process
CONTEXT:  COPY fund_records, line 2088885: "..."
LOG:  PID 31820 in cancel request did not match any process
LOG:  all server processes terminated; reinitializing
FATAL:  pre-existing shared memory block is still in use
HINT:  Check if there are any old server processes still running, and
terminate them.

The `CONTEXT` is of course differnt depending on what I did. The above
`COPY` command was logged when restoring a dump with the big table with
pg_restore. Other occurrences were `VACUUM` (when I run vacuum on either the
single big table or the whole database) or even `SELECT` (when I run a
simple 1 row result select joining the big table).

I can reproduce this crash 100% by either running vacuum on the table or
trying to restore the table data from a dump. Other things, like select
statements, do not reproduce always, but at least they crash often enough
that it is very annoying. It's always the `could not write to log file x at
offset y, length z: Bad file descriptor` error. I've already tried disabling
autovacuum during pg_resotre but it still fails.

This problem seems to be specific to one server, because the same table/dump
works fine when I import it on my local machine. The affected server is a
virtual (on Parallels Virtuozzo base) Windows Server 2008 SP2 x64 with 2 GB
RAM. There is only a single HDD partition and nearly 40 GB of free space on
it. Nothing other than PostgreSQL is running on the server, also no anti
virus software. But PostgreSQL is the only application on this server that
shows problems, all other operations I've tried work fine, so it seems like
it's not a general problem with this machine.

I've tried PostgreSQL server versions 8.4.4 (this was running for years
until it first showed the error), 8.4.22 and 9.4.4, all show the same error.
I've tried both upgrading from 8.4.4 to 8.4.22 and fresh installs of 8.4.22
and 9.4.4 (there I've also completely deleted the data folder) multiple
times.

Since I've changed some values in postgresql.conf away from their default
values, the the crash now happens much rarer. Simple SQL and DML statements
only crash very rarely (once every few days), but VACUUM still crashes 100%.
Here are the values I changed:

shared_buffers = 128MB
work_mem = 16MB
maintenance_work_mem = 64MB
wal_buffers = 4MB

Can someone please explain what's wrong here?

SGksDQoNCj4gQ291bGQgeW91IGhlbHAgdXMga25vdyB0aGUgc2l6ZSBvZiB0aGUgZGF0YWJhc2Ug
eW91IGFyZSBkdW1waW5nID8NClRoZSBzaXplIG9mIHRoZSBkYXRhIGRpcmVjdG9yeSBpcyAzLjM3
IEdCLiBUaGUgc2l6ZSBvZiB0aGUgZHVtcCBmaWxlIHdpdGggLS1jb21wcmVzcz05IGlzIDMwMCBN
Qi4NCg0KPiBUaGlzIHNob3VsZCBwcm9iYWJseSBiZSBhIFdpbmRvd3MgbWVtb3J5IGFsbG9jYXRp
b24gcHJvYmxlbS4gRG8geW91IHNlZSBhbnkgc3VjaCBpc3N1ZXMgbGlrZSBjb3JydXB0aW9uIHdp
dGggbWVtb3J5IG9yIGhhcmQtZGlzayA/DQpObywgYWxsIG90aGVyIG9wZXJhdGlvbnMgb24gdGhl
IG1hY2hpbmUgd29ya3MgZmluZSwgaW5jbHVkaW5nIGNvcHlpbmcgbGFyZ2UgZmlsZXMgYW5kIGlu
c3RhbGxhdGlvbiBvZiBzb2Z0d2FyZS4gQnV0IEkgY2Fu4oCZdCBkbyByZWFsIG1lbW9yeSBvciBo
YXJkLWRpc2sgdGVzdHMgYmVjYXVzZSB0aGlzIGlzIGp1c3QgYSB2aXJ0dWFsIG1hY2hpbmUgYW5k
IEkgZG9u4oCZdCBoYXZlIGFueSBhY2Nlc3MgdG8gdGhlIHBoeXNpY2FsIG1hY2hpbmUuDQoNCj4g
QXNzdW1pbmcgYWxsIGlzIHdlbGwgd2l0aCB0aGUgaGFyZHdhcmUsIElmIHlvdSBjYW4gdHJ5IGFu
ZCBpbmNyZWFzZSB0aGUgc2hhcmVkX2J1ZmZlcnMgYW5kIHNlZSBpZiB0aGF0IGltcHJvdmVzIHRo
ZSBzaXR1YXRpb24uDQpBcyB3cml0dGVuLCBJ4oCZdmUgYWxyZWFkeSBpbmNyZWFzZWQgc2hhcmVk
X2J1ZmZlcnMgdG8gMTI4TUIgYW5kIGl0IGluZGVlZCBpbXByb3ZlZCB0aGUgc2l0dWF0aW9uLiBC
dXQgaXQgaXNu4oCZdCByZXNvbHZlZC4gV2hhdCB2YWx1ZSB3b3VsZCB5b3Ugc3VnZ2VzdD8NCg0K
QmVzdCByZWdhcmRzLA0KTWFudWVsDQoNCg0KVm9uOiBWZW5rYXRhIEJhbGFqaSBOIFttYWlsdG86
bmFnMTAxMEBnbWFpbC5jb21dDQpHZXNlbmRldDogTWl0dHdvY2gsIDEyLiBBdWd1c3QgMjAxNSAw
Mjo0OA0KQW46IFBhbGxpZXIsIE1hbnVlbCAvIEJFS08gR3Jheg0KQmV0cmVmZjogUmU6IFtCVUdT
XSBCVUcgIzEzNTYxOiAiY291bGQgbm90IHdyaXRlIHRvIGxvZyBmaWxlLCBCYWQgZmlsZSBkZXNj
cmlwdG9yIiBlcnJvciB3aGVuIHdvcmtpbmcgd2l0aCBodWdlIHRhYmxlDQoNCg0KT24gVHVlLCBB
dWcgMTEsIDIwMTUgYXQgMzo0MSBQTSwgPG1hbnVlbC5wYWxsaWVyQGJla28uYXQ8bWFpbHRvOm1h
bnVlbC5wYWxsaWVyQGJla28uYXQ+PiB3cm90ZToNClRoZSBmb2xsb3dpbmcgYnVnIGhhcyBiZWVu
IGxvZ2dlZCBvbiB0aGUgd2Vic2l0ZToNCg0KQnVnIHJlZmVyZW5jZTogICAgICAxMzU2MQ0KTG9n
Z2VkIGJ5OiAgICAgICAgICBNYW51ZWwgUGFsbGllcg0KRW1haWwgYWRkcmVzczogICAgICBtYW51
ZWwucGFsbGllckBiZWtvLmF0PG1haWx0bzptYW51ZWwucGFsbGllckBiZWtvLmF0Pg0KUG9zdGdy
ZVNRTCB2ZXJzaW9uOiA5LjQuNA0KT3BlcmF0aW5nIHN5c3RlbTogICBXaW5kb3dzIFNlcnZlciAy
MDA4IFNQMiB4NjQNCkRlc2NyaXB0aW9uOg0KDQpJJ20gaGF2aW5nIGEgYmlnIHByb2JsZW0gd2l0
aCBhIFBvc3RyZVNRTCBkYXRhYmFzZSBvbiBhIHNwZWNpZmljIG1hY2hpbmUuDQpXaGVuZXZlciBJ
IGRvIHNvbWUgc3BlY2lmaWMgdGhpbmdzIHdpdGggYSBodWdlIHRhYmxlIChvdmVyIDkgbWlsbGlv
biByb3dzKQ0KdGhlIHdob2xlIHBvc3RncmVzIHByb2Nlc3MgY3Jhc2hlcyB3aXRoIHRoZSBmb2xs
b3dpbmcgZW50cmllcyBpbiB0aGUgbG9nDQpmaWxlOg0KDQpQQU5JQzogIGNvdWxkIG5vdCB3cml0
ZSB0byBsb2cgZmlsZSAwMDAwMDAwMTAwMDAwMDAwMDAwMDAwMjIgYXQgb2Zmc2V0DQoxMTY3MzYw
MCwgbGVuZ3RoIDkwOTMxMjogQmFkIGZpbGUgZGVzY3JpcHRvcg0KTE9HOiAgV0FMIHdyaXRlciBw
cm9jZXNzIChQSUQgMTcwNTYpIGV4aXRlZCB3aXRoIGV4aXQgY29kZSAzDQpMT0c6ICB0ZXJtaW5h
dGluZyBhbnkgb3RoZXIgYWN0aXZlIHNlcnZlciBwcm9jZXNzZXMNCldBUk5JTkc6ICB0ZXJtaW5h
dGluZyBjb25uZWN0aW9uIGJlY2F1c2Ugb2YgY3Jhc2ggb2YgYW5vdGhlciBzZXJ2ZXIgcHJvY2Vz
cw0KQ09OVEVYVDogIENPUFkgZnVuZF9yZWNvcmRzLCBsaW5lIDIwODg4ODU6ICIuLi4iDQpMT0c6
ICBQSUQgMzE4MjAgaW4gY2FuY2VsIHJlcXVlc3QgZGlkIG5vdCBtYXRjaCBhbnkgcHJvY2Vzcw0K
TE9HOiAgYWxsIHNlcnZlciBwcm9jZXNzZXMgdGVybWluYXRlZDsgcmVpbml0aWFsaXppbmcNCkZB
VEFMOiAgcHJlLWV4aXN0aW5nIHNoYXJlZCBtZW1vcnkgYmxvY2sgaXMgc3RpbGwgaW4gdXNlDQpI
SU5UOiAgQ2hlY2sgaWYgdGhlcmUgYXJlIGFueSBvbGQgc2VydmVyIHByb2Nlc3NlcyBzdGlsbCBy
dW5uaW5nLCBhbmQNCnRlcm1pbmF0ZSB0aGVtLg0KDQpUaGUgYENPTlRFWFRgIGlzIG9mIGNvdXJz
ZSBkaWZmZXJudCBkZXBlbmRpbmcgb24gd2hhdCBJIGRpZC4gVGhlIGFib3ZlDQpgQ09QWWAgY29t
bWFuZCB3YXMgbG9nZ2VkIHdoZW4gcmVzdG9yaW5nIGEgZHVtcCB3aXRoIHRoZSBiaWcgdGFibGUg
d2l0aA0KcGdfcmVzdG9yZS4gT3RoZXIgb2NjdXJyZW5jZXMgd2VyZSBgVkFDVVVNYCAod2hlbiBJ
IHJ1biB2YWN1dW0gb24gZWl0aGVyIHRoZQ0Kc2luZ2xlIGJpZyB0YWJsZSBvciB0aGUgd2hvbGUg
ZGF0YWJhc2UpIG9yIGV2ZW4gYFNFTEVDVGAgKHdoZW4gSSBydW4gYQ0Kc2ltcGxlIDEgcm93IHJl
c3VsdCBzZWxlY3Qgam9pbmluZyB0aGUgYmlnIHRhYmxlKS4NCg0KSSBjYW4gcmVwcm9kdWNlIHRo
aXMgY3Jhc2ggMTAwJSBieSBlaXRoZXIgcnVubmluZyB2YWN1dW0gb24gdGhlIHRhYmxlIG9yDQp0
cnlpbmcgdG8gcmVzdG9yZSB0aGUgdGFibGUgZGF0YSBmcm9tIGEgZHVtcC4gT3RoZXIgdGhpbmdz
LCBsaWtlIHNlbGVjdA0Kc3RhdGVtZW50cywgZG8gbm90IHJlcHJvZHVjZSBhbHdheXMsIGJ1dCBh
dCBsZWFzdCB0aGV5IGNyYXNoIG9mdGVuIGVub3VnaA0KdGhhdCBpdCBpcyB2ZXJ5IGFubm95aW5n
LiBJdCdzIGFsd2F5cyB0aGUgYGNvdWxkIG5vdCB3cml0ZSB0byBsb2cgZmlsZSB4IGF0DQpvZmZz
ZXQgeSwgbGVuZ3RoIHo6IEJhZCBmaWxlIGRlc2NyaXB0b3JgIGVycm9yLiBJJ3ZlIGFscmVhZHkg
dHJpZWQgZGlzYWJsaW5nDQphdXRvdmFjdXVtIGR1cmluZyBwZ19yZXNvdHJlIGJ1dCBpdCBzdGls
bCBmYWlscy4NCg0KVGhpcyBwcm9ibGVtIHNlZW1zIHRvIGJlIHNwZWNpZmljIHRvIG9uZSBzZXJ2
ZXIsIGJlY2F1c2UgdGhlIHNhbWUgdGFibGUvZHVtcA0Kd29ya3MgZmluZSB3aGVuIEkgaW1wb3J0
IGl0IG9uIG15IGxvY2FsIG1hY2hpbmUuIFRoZSBhZmZlY3RlZCBzZXJ2ZXIgaXMgYQ0KdmlydHVh
bCAob24gUGFyYWxsZWxzIFZpcnR1b3p6byBiYXNlKSBXaW5kb3dzIFNlcnZlciAyMDA4IFNQMiB4
NjQgd2l0aCAyIEdCDQpSQU0uIFRoZXJlIGlzIG9ubHkgYSBzaW5nbGUgSEREIHBhcnRpdGlvbiBh
bmQgbmVhcmx5IDQwIEdCIG9mIGZyZWUgc3BhY2Ugb24NCml0LiBOb3RoaW5nIG90aGVyIHRoYW4g
UG9zdGdyZVNRTCBpcyBydW5uaW5nIG9uIHRoZSBzZXJ2ZXIsIGFsc28gbm8gYW50aQ0KdmlydXMg
c29mdHdhcmUuIEJ1dCBQb3N0Z3JlU1FMIGlzIHRoZSBvbmx5IGFwcGxpY2F0aW9uIG9uIHRoaXMg
c2VydmVyIHRoYXQNCnNob3dzIHByb2JsZW1zLCBhbGwgb3RoZXIgb3BlcmF0aW9ucyBJJ3ZlIHRy
aWVkIHdvcmsgZmluZSwgc28gaXQgc2VlbXMgbGlrZQ0KaXQncyBub3QgYSBnZW5lcmFsIHByb2Js
ZW0gd2l0aCB0aGlzIG1hY2hpbmUuDQoNCkkndmUgdHJpZWQgUG9zdGdyZVNRTCBzZXJ2ZXIgdmVy
c2lvbnMgOC40LjQgKHRoaXMgd2FzIHJ1bm5pbmcgZm9yIHllYXJzDQp1bnRpbCBpdCBmaXJzdCBz
aG93ZWQgdGhlIGVycm9yKSwgOC40LjIyIGFuZCA5LjQuNCwgYWxsIHNob3cgdGhlIHNhbWUgZXJy
b3IuDQpJJ3ZlIHRyaWVkIGJvdGggdXBncmFkaW5nIGZyb20gOC40LjQgdG8gOC40LjIyIGFuZCBm
cmVzaCBpbnN0YWxscyBvZiA4LjQuMjINCmFuZCA5LjQuNCAodGhlcmUgSSd2ZSBhbHNvIGNvbXBs
ZXRlbHkgZGVsZXRlZCB0aGUgZGF0YSBmb2xkZXIpIG11bHRpcGxlDQp0aW1lcy4NCg0KU2luY2Ug
SSd2ZSBjaGFuZ2VkIHNvbWUgdmFsdWVzIGluIHBvc3RncmVzcWwuY29uZiBhd2F5IGZyb20gdGhl
aXIgZGVmYXVsdA0KdmFsdWVzLCB0aGUgdGhlIGNyYXNoIG5vdyBoYXBwZW5zIG11Y2ggcmFyZXIu
IFNpbXBsZSBTUUwgYW5kIERNTCBzdGF0ZW1lbnRzDQpvbmx5IGNyYXNoIHZlcnkgcmFyZWx5IChv
bmNlIGV2ZXJ5IGZldyBkYXlzKSwgYnV0IFZBQ1VVTSBzdGlsbCBjcmFzaGVzIDEwMCUuDQpIZXJl
IGFyZSB0aGUgdmFsdWVzIEkgY2hhbmdlZDoNCg0Kc2hhcmVkX2J1ZmZlcnMgPSAxMjhNQg0Kd29y
a19tZW0gPSAxNk1CDQptYWludGVuYW5jZV93b3JrX21lbSA9IDY0TUINCndhbF9idWZmZXJzID0g
NE1CDQoNCkNhbiBzb21lb25lIHBsZWFzZSBleHBsYWluIHdoYXQncyB3cm9uZyBoZXJlPw0KDQpD
b3VsZCB5b3UgaGVscCB1cyBrbm93IHRoZSBzaXplIG9mIHRoZSBkYXRhYmFzZSB5b3UgYXJlIGR1
bXBpbmcgPyBUaGlzIHNob3VsZCBwcm9iYWJseSBiZSBhIFdpbmRvd3MgbWVtb3J5IGFsbG9jYXRp
b24gcHJvYmxlbS4gRG8geW91IHNlZSBhbnkgc3VjaCBpc3N1ZXMgbGlrZSBjb3JydXB0aW9uIHdp
dGggbWVtb3J5IG9yIGhhcmQtZGlzayA/DQpBc3N1bWluZyBhbGwgaXMgd2VsbCB3aXRoIHRoZSBo
YXJkd2FyZSwgSWYgeW91IGNhbiB0cnkgYW5kIGluY3JlYXNlIHRoZSBzaGFyZWRfYnVmZmVycyBh
bmQgc2VlIGlmIHRoYXQgaW1wcm92ZXMgdGhlIHNpdHVhdGlvbi4NClJlZ2FyZHMsDQpWZW5rYXRh
IEJhbGFqaSBODQpGdWppdHN1IEF1c3RyYWxpYQ0KDQoNCg==