Re: Remote connections? - Mailing list pgsql-hackers

mlw wrote:

> Hey this looks really cool. It looks like something I was thinking about doing.
> I have a couple suggestions that could make it a little better, I hope you will
> not be offended. (If you want my help, I'll chip in!)
> 


Thanks! Suggestions welcomed.

> Why not use a binary cursor? That way native types can slip through without the
> overhead of conversion.
>


I wasn't sure that would work. Would you create dblink_tok as returning 
opaque then?

> Right now you get all rows up front, you may be able to increase overall
> performance by fetching only a few rows at a time, rather than get everything
> all at once. (Think on the order of 4 million rows from your remote query!)
> Execute the commit at the end of processing. There are even some asynchronous
> functions you may be able to utilize to reduce the I/O bottleneck. Use the
> synchronous function first, then before you return initiate an asynchronous
> read. Every successive pass through the function, read the newly arrived tuple,
> and initiate the next asynchronous read. (The two machine could be processing
> the query simultaneously, and this could even IMPROVE performance over a single
> system for heavy duty queries.)


Interesting . . . but aren't there some issues with the asynch functions?

> 
> Setup a hash table for field names, rather than requiring field numbers. (Keep
> field number for efficiency, of course.)
> 
> You could eliminate having to pass the result pointer around by keeping a
> static array in your extension. Use something like Oracle's "contains" notation
> of result number. Where each instantiation of "contains()" and "score()"
> require an id. i.e. 1,2,3,40 etc. Then hash those numbers into an array. I have
> some code that does this for a PostgreSQL extension (it implements contains) on
> my website (pgcontains, under download). It is ugly but works for the most
> part.
> 


I thought about the static array, but I'm not familiar with Oracle 
contains() and score() -- I'm only fluent enough with Oracle to be 
dangerous. Guess I'll have to dig out the books . . .


> Seriously, your stuff looks great. I think it could be the beginning of a
> fairly usable system for my company. Any help you need/want, just let me know.
> 

I am planning to improve dblink during the next release cycle, so I'll 
keep all this in mind (and might take you up on the help offer too!). I 
was hoping we'd have functions returning tuples by now, which would 
improve this extension dramatically. Unfortunately, it sounds like Alex 
won't have time to finish that even for 7.3 :(

Alex, can we get a look at your latest code? Is it any different the 
your last submission to PATCHES?

Joe