query

On Thu, 2005-09-01 at 13:34, Federico Balbi wrote:

Hi,
I was just wondering if postgresql supports queries between servers in
order to pull info from different machines.
Let's say server A can run a query against server B... something like:

server1 running a SELECT * FROM server2.table

or joins

SELECT A.name, B.name FROM server1.table A, server2.table B WHERE A.id=B.id

No, not directly. The two solutions are:

use two schemas, not two db servers, and use that. Then the query would
be the same, but instead of server1 and server2, it would be schema1 and
schema2.

OR

use the dblink module.

the dblink module does NOT allow you to rollback the transaction
entirely, because the transaction semantics across machines aren't there
or in it.

On Thu, Sep 01, 2005 at 01:34:41PM -0500, Federico Balbi wrote:

I was just wondering if postgresql supports queries between servers in
order to pull info from different machines.

Not natively, but contrib/dblink provides a way to query other
PostgreSQL databases, which can be running on other machines.
Functions in some procedural languages (e.g., PL/Perl) can query
arbitrary data sources (other kinds of databases, spreadsheets,
text files, etc.) and return data as sets of rows. With such
external data sources you don't get transactional semantics, however.

In PostgreSQL, if you need to partition data but also need to perform
cross-partition operations, then the usual recommendation is to use
multiple schemas inside a single database.

Let's say server A can run a query against server B... something like:

server1 running a SELECT * FROM server2.table

or joins

SELECT A.name, B.name FROM server1.table A, server2.table B WHERE A.id=B.id

You could abstract a dblink query with a view, but unfortunately
the WHERE clause wouldn't propogate to the underlying query on the
remote database -- a dblink query would have to fetch the entire
remote result set before the local database could do any restrictions.
In some cases, however, it can be worthwhile to call dblink directly
with an appropriate WHERE clause in the query string.

--
Michael Fuhr