Thread: BUG #14936: Huge backend memory usage during schema dump of databasewith many views

The following bug has been logged on the website:

Bug reference:      14936
Logged by:          Maksym Boguk
Email address:      maxim.boguk@gmail.com
PostgreSQL version: 9.5.10
Operating system:   Linux
Description:

Hi,

I got off-list report of the following issue:
server used all memory and crash with OOM during pg_dump of the database
with many schemes and views.

After removing all extra stuff problem can be show with simple run the
following query on the database:
select (CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN
pg_get_viewdef(c.oid) ELSE NULL::text END) as view_src from pg_class c where
(c.relkind = 'v'::"char"); 

This query used up to 15GB backend local memory before finishing in one
hour.

The database contains 22 schema with ~1000views in each schema, average view
length ~2kb (3+ table in each view), so total ~20-25k views.

If required, I can ask for dump of the few schemes to provide self-contained
test case.

Memory leak somewhere inside pg_get_viewdef?

Kind Regards,
Maksym

maxim.boguk@gmail.com writes:
> I got off-list report of the following issue:
> server used all memory and crash with OOM during pg_dump of the database
> with many schemes and views.
> After removing all extra stuff problem can be show with simple run the
> following query on the database:
> select (CASE WHEN pg_has_role(c.relowner, 'USAGE'::text) THEN
> pg_get_viewdef(c.oid) ELSE NULL::text END) as view_src from pg_class c where
> (c.relkind = 'v'::"char"); 

Hm.  It's certainly no surprise if pg_get_viewdef eats memory, but why
wouldn't it get reclaimed at the end of each tuple cycle?

Also: I don't think the above query is very representative of what
pg_dump actually does; AFAIR it pulls view definitions one at a time,
not all at once.

That leads me to guess that what's going on is relcache bloat, since
relcache entries would persist across queries.  There's no mechanism
for limiting the number of entries in that cache.
        regards, tom lane