The following bug has been logged on the website:
Bug reference: 15787
Logged by: Michael Dobrovnik
Email address: michael.dobrovnik@groiss.com
PostgreSQL version: 11.2
Operating system: Windows 10 64 bit, Linux
Description:
Hello,
we are using a BYTEA column to store the contents of (large) files and
retrieve them later on.
On a particular customer system, we were getting memory allocation errors
which were not
a pain to reproduce in our systems. After much ado, we can finally pin it
down to a logging configuration.
The problematic system had log_min_duration=x configured.
So:
On PostgreSQL 11.2 (and 10.6. and 9.6.12 and 9.6.8) one gets memory alloc
errors
when the statement is logged (triggered e.g. via log_min_duration=x or via
log_statement=all in postgresql.conf).
c.f. postgresql.log:
2019-04-30 09:51:52 CEST testdb test100 ERROR: invalid memory alloc request
size 1846358165
2019-04-30 09:51:52 CEST testdb test100 STATEMENT: update doccontent set
content = $1 where oid=$2
the operation fails due to logging. Without logging the operation
succeeds.
Logging tries to write the parameters of the statement, the bytea column is
written out in hex format,
as can be seen in the following line (for a smaller file)
2019-04-30 10:48:41 CEST testdb test100 DETAIL: parameters: $1 =
'\x504b0304140000000....
so the allocation size 1846358165 comes from a file that is about 923179081
bytes in length (plus /minus preamble and trailer)
A warning in the documentation of the logging parameters will definitely be
helpful.
A more robust solution could e.g. be to truncate the bytea values written to
the log at some sensible (configurable) length (and also to read them just
up to that length for logging).
thank you all for your efforts concering PostgreSQL!
kind regards
Michael Dobrovnik