[BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance

The following bug has been logged on the website:

Bug reference:      14845
Logged by:          Josef Machytka
Email address:      josef.machytka@gmail.com
PostgreSQL version: 9.6.5
Operating system:   Debian 8
Description:

Hi,
I work from time to time with postgres_fdw and mysql_fdw and they both seem
to have the same problem - they use too much memory if I query huge remote
tables.

Since I have seen this behavior before on our monitoring I decided to test
it on testing instance with really huge amount of remote data which would
not fit into memory. 

Behavior was as expected - postgesql started to use more and more memory
until the whole available memory was used.

After that in first case postgresql crashed (which was more or less OK
because it restarted) but in second case the whole Debian instance crashed
and did not want to start again even after repeated attempts. I did not
fiddle with it and just recreated it but I even do not want to imagine if it
would happen on some other hugely used instance.

This behavior limits usage of those fdw very significantly. I cannot allow
other people to use foreign tables on really huge tables we have and I have
to check sizes before I use some query which is expected to be "heavy"... Of
course I would love to hear from someone that I should not use "heavy
queries"... :-)

So is there a way to for example force fdw to swap data into some temp file
and not use so much memory? This behavior seems to not depend on work_mem
setting...

Thanks
Josef

(Config: postgresql 9.6.5, latest versions of both fdw, Debian 8)




--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs