Re: pgsql_fdw, FDW for PostgreSQL server

(2011/10/30 11:34), Shigeru Hanada wrote:
> 2011/10/30 Tom Lane<tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>> I think we have to. Even if we estimate that a given scan will return
>> only a few rows, what happens if we're wrong? We don't want to blow out
>> memory on the local server by retrieving gigabytes in one go.
>
> Oh, I overlooked the possibility of wrong estimation. Old PostgreSQL uses
> 1000 as default estimation, so big table which has not been analyzed may
> crashes the backend.
>
> To ensure the data retrieving safe, we need to get actual amount of result,
> maybe by executing SELECT COUNT(*) in planning phase. It sounds too heavy
> to do for every scan, and it still lacks actual width.
>
> One possible idea is to change default value of min_cursur_rows option to 0
> so that pgsql_fdw uses CURSOR by default, but it seems not enough. I'll
> drop simple SELECT mode from first version of pgsql_fdw for safety.

I removed simple SELECT mode from pgsql_fdw, and consequently also
removed min_cursor_rows FDW option. This fix avoids possible memory
exhaustion due to wrong estimation gotten from remote side.

Once libpq has had capability to retrieve arbitrary number of rows from
remote portal at a time without server-side cursor in future, then we
will be able to revive simple SELECT. Then it's enough safe even if we
don't have actual data size, but (maybe) faster than cursor mode because
we can reduce # of SQL commands. Though of course proof of performance
advantage should be shown before such development.

--
Shigeru Hanada