PostgreSQL Bugs

Collected from the PG bugs email list.

Bug ID15787
PG Version11.2
OSWindows 10 64 bit, Linux
Opened2019-04-30 10:27:18+00
Reported byMichael Dobrovnik
StatusNew

Body of first available message related to this bug follows.

The following bug has been logged on the website:

Bug reference:      15787
Logged by:          Michael Dobrovnik
Email address:      (redacted)
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

Messages

DateAuthorSubject
2019-04-30 10:27:18+00PG Bug reporting formBUG #15787: Statement logging may consume huge amounts of memory when BYTEA parameters are involved.