Re: SQL/MED estimated time of arrival?

On Sat, 6 Nov 2010 16:04:37 +0900
Hitoshi Harada <umi(dot)tanuki(at)gmail(dot)com> wrote:
> 2010/11/5 Shigeru HANADA <hanada(at)metrosystems(dot)co(dot)jp>:
> > On Fri, 5 Nov 2010 16:27:49 +0900
> > Itagaki Takahiro <itagaki(dot)takahiro(at)gmail(dot)com> wrote:
> >> PL/Proxy has a similar functionality with RUN ON ALL to start queries
> >> in parallel. So, I think it's a infrastructure commonly required.
> > I noticed the lack of consideration about cache invalidation from
> > reading PL/Proxy source, thanks for your mention about PL/Proxy. :-)
>
> And if we really make this async query come true, I suggest designing
> resource (i.e. remote connection) management very carefully. When the
> executor fails in the middle of its execution, it possibly fails to
> release its own resource; close() in ExecutorEnd() will never be
> called. As far as I know files and memory are released automatically
> in the current mechanism, but MED APIs will use their own resources
> other than them.

Yes, managegement of FDW's resources is very important issue. Curren
FdwRoutine includes ConnectServer and FreeFSConnection, but they might
not be enough to manage FDW's resources by backend in common way.
Because connection is not only resource FDW use. Possible resources
are:

- Files (Virtual File descriptor would help to manage)
- Database connections (might be cached)
- Server-side cursors (would be released with DB connection?)
- Heap memory (for instance, libpq uses malloc)

For example, if postgresql_fdw uses server-side cursor to retreive
result tuples, it would be required to CLOSE cursors at the end of
transaction. Closing cursor at the end of session wouldn't be good
idea because clients might pool and reuse connections.

How about removing them, ConnectServer and FreeFSConnection, from
FdwRoutine and leaving the responsibility of resource management to
each FDW? Each FDW would have to use mechanism such as Virtual File
and ResourceOwner to manage resources properly, though.

Regards,
--
Shigeru Hanada