Re: Remote connections?

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!)

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

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.)

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.

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.


Joe Conway wrote:
> 
> mlw wrote:
> 
> > I too find the internals of PostgreSQL virtually incomprehensible at the
> > internal level. If there were a document somewhere which published how a
> > function could return multiple tuples, remote views would be a trivial
> > undertaking. It could look like:
> >
> > select * from remote('select *from table', 'user=postgres host=outland
> > db=remote');
> >
> 
> See contrib/dblink in the 7.2 beta. It was my attempt inspired by
> Oracle's dblink and some code that you (mlw) posted a while back. Based
> on the limitations wrt returning muliple tuples, I wound up with
> something like:
> 
> lt_lcat=# select dblink_tok(t1.dblink_p,0) as f1 from (select
> dblink('hostaddr=127.0.0.1 dbname=template1 user=postgres
> password=postgres','select proname from pg_proc') as dblink_p) as t1;
> 
> Which, as has been pointed out more than once, is pretty ugly, but at
> least it's a start ;-)
> 
> Joe
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster