Postgres Pro
Downloads
Home   >   mailing lists

Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance - Mailing list pgsql-bugs

From Jeff Janes
Subject Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance
Date
Msg-id CAMkU=1yn=feWCzANUUG3okswzBM2EpFHcwv_R1Pii8HWKDaOyA@mail.gmail.com
Whole thread Raw
In response to Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: [BUGS] BUG #14845: postgres_fdw and mysql_fdw can cause crash of instance
List pgsql-bugs
Tree view
On Fri, Oct 6, 2017 at 10:01 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Josef Machytka <josef.machytka@gmail.com> writes:
> In this case which went wrong I used postgres_fdw to compare data on local
> and remote database using "select all from remote except select all from
> local".

I think my theory B probably applies then: without use_remote_estimate,
the planner would not guess that the remote table is huge, and that could
well allow it to pick a hashed EXCEPT implementation --- which will then
try to collect the entire remote table into an in-memory hash table.

Unfortunately, use_remote_estimate won't help for this particular case.  While it gets the estimate for the number of rows from the remote estimate, HashSetOp doesn't care about estimated number of rows, only estimated number of distinct rows.  And use_remote_estimate doesn't provide that, so it falls back on assuming (I think) 200 distinct rows.

What could help is running 'analyze' locally against the foreign table.  That can cause other problems, though.
 
Cheers,

Jeff

pgsql-bugs by date:

Previous
From: Andres Freund
Date:
Subject: Re: [BUGS] json(b)_array_elements use causes very large memory usagewhen also referencing entire json document
Next
From: Andres Freund
Date:
Subject: Re: [BUGS] json(b)_array_elements use causes very large memory usagewhen also referencing entire json document