"Ross J. Reedstrom" wrote:
>
> 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@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 ;-)
I think we can we can dispense worrying about two stage commits, if we
assume that remote connections are treated as views with no rules. As
long as remote tables are "read only" then the implementation is much
easier.
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');