Re: Remote connections?

On Thu, Dec 06, 2001 at 01:28:04PM -0500, mlw wrote:
> I just found out something about Oracle which that looks like something
> that could be doable in PostgreSQL.
>
> What do you all think:
>
> Oracle's version is something like this:
>
> create [public] database link using [...]
>
> select * from sometable(at)remotelink
>
>
> I was thinking how this could be done with postgreSQL. How hard would it
> be to make something that is similar to a view, but executes a query
> remotely? I envision something like this:
>
> create [public] link name query using [...]
>
> The table link will be similar to a view. It could be used like this:
>
> CREATE LINK test as select * from test WITH 'user=postgres host=remote
> db=data';
>
> SELECT * from test;
>
> or
>
> SELECT * from fubar join test on (fubar.id = test.id) ;
>
> So, what do you think? Impossible, possible, too hard? too easy?

Here we come, full circle. This is just about where I came on board.
Many moons ago, I started looking at Mariposa, in the hopes of forward
patching it into PostgreSQL, and generalizing the 'remote' part to allow
exactly the sort of access you described above.

The biggest problem with this is transactional semantics: you need
two-stage commits to get this right, and we don't hav'em. (Has there
been an indepth discussion concerning what how hard it would be to do
that with postgresql?)

The _actual_ biggest problem was my lack of knowledge of the PostgreSQL
codebase ;-)

Ross
--
Ross Reedstrom, Ph.D. reedstrm(at)rice(dot)edu
Executive Director phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics fax: 713-348-6182
Rice University MS-39
Houston, TX 77005