BUG #15787: Statement logging may consume huge amounts of memory when BYTEA parameters are involved.

classic Classic list List threaded Threaded
1 message Options
Reply | Threaded
Open this post in threaded view
|

BUG #15787: Statement logging may consume huge amounts of memory when BYTEA parameters are involved.

PG Bug reporting form
The following bug has been logged on the website:

Bug reference:      15787
Logged by:          Michael Dobrovnik
Email address:      [hidden email]
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