Thread: Accessing 2 different databases in the same query ?

Hi everyone,

Is there a way to access 2 different Tables each one in a different database with one sql query ?(or to make references between them)...

Personally i don't believe there's a way because we are talking about two different databases, but i'm facing this problem and i would really appreciate if anyone could help.

Best Regards

Nagib Abi Fadel

.

On Monday 02 June 2003 08:22, Nagib Abi Fadel wrote:
> Hi everyone,
>
> Is there a way to access 2 different Tables each one in a different
> database with one sql query ?(or to make references between them)...
>
> Personally i don't believe there's a way because we are talking about two
> different databases, but i'm facing this problem and i would really
> appreciate if anyone could help.
>


See dblink in postgresql contrib.
Regards !

On Monday 02 Jun 2003 7:22 am, Nagib Abi Fadel wrote:
> Hi everyone,
>
> Is there a way to access 2 different Tables each one in a different
> database with one sql query ?(or to make references between them)...
>
> Personally i don't believe there's a way because we are talking about two
> different databases, but i'm facing this problem and i would really
> appreciate if anyone could help.

There's a package called "dblink" in the contrib directory of the source
distribution. Might also be available as RPM etc. This sounds like what you
want.

--
  Richard Huxton

On Monday 02 Jun 2003 7:22 am, Nagib Abi Fadel wrote:
> Hi everyone,
>
> Is there a way to access 2 different Tables each one in a different
> database with one sql query ?(or to make references between them)...
>
> Personally i don't believe there's a way because we are talking about two
> different databases, but i'm facing this problem and i would really
> appreciate if anyone could help.

There's a package called "dblink" in the contrib directory of the source
distribution. Might also be available as RPM etc. This sounds like what you
want.

--
Richard Huxton

On Monday 02 Jun 2003 9:39 am, Nagib Abi Fadel wrote:
> From what i read dblink let's us access another database in the same query
> ... Which is great. But does it slow down the query ??

Don't use it myself, but from discussion on the lists, I'd say "probably" -
don't see how it could be otherwise. You'll want to test.

> And what about making references between tables (creating foreign keys) is
> it possible ?????

Nope - just not practical. You could bring both servers to a grinding halt
very quickly trying to keep track of FKs over a network link.

Incidentally, if you don't need the two databases on different machines,
perhaps look at using schemas to separate users - that would let you have FKs
etc.

--
  Richard Huxton

On Monday 02 June 2003 10:39, Nagib Abi Fadel wrote:
> From what i read dblink let's us access another database in the same query
> ... Which is great. But does it slow down the query ??

What do You mean slow down ? In compare to what ?
If You question was is such query slower than the query on tables in local
database, the answer is YES.  Let me describe the mechanism of dblink:
    Your backend process becomes client of another backend (on same or different
server). dblink establishes connection to remote database, executes the
query, and send data to Your backend.

I use dblink over slow ISDN connection, so You can figure out how slow it can
be, but that is the beauty of dblink: You can query servers that are anywhere
on the network !

> And what about making references between tables (creating foreign keys) is
> it possible ?????
>

No, but you can create "remote view" using dblink and then do the same thing
using triggers(foreign keys are just specific triggers).
There are few samples in dblink documentation - check them out !

Regards !

On Monday 02 June 2003 10:39, Nagib Abi Fadel wrote:
> From what i read dblink let's us access another database in the same query
> ... Which is great. But does it slow down the query ??

What do You mean slow down ? In compare to what ?
If You question was is such query slower than the query on tables in local
database, the answer is YES. Let me describe the mechanism of dblink:
Your backend process becomes client of another backend (on same or different
server). dblink establishes connection to remote database, executes the
query, and send data to Your backend.

I use dblink over slow ISDN connection, so You can figure out how slow it can
be, but that is the beauty of dblink: You can query servers that are anywhere
on the network !

> And what about making references between tables (creating foreign keys) is
> it possible ?????
>

No, but you can create "remote view" using dblink and then do the same thing
using triggers(foreign keys are just specific triggers).
There are few samples in dblink documentation - check them out !

Regards !

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nagib Abi Fadel wrote:
> Well actually the 2 databases are on the same machine, so i wanted to
> know if  using the dblink for accessing 2 tables each one in a
> different database will dramatically slow down the query (specially
> if it's a join query), Comparing to a query on 2 tables on the same
> database. If it's the case i would consider replacing the 2 databases
> with one database, but does this have any disadvantage does it make
> queries slower if the database grow in size ???
>

dblink is using a client library and is therefore certainly slower than
backend heap access.

Whether the slowdown is "dramatic" or not, is a dependent on *your*
definition of dramatic, and the specifics of what you are trying to do.
I'd suggest you try it and find out.

If it's too slow to meet your needs, and you are able to combine the two
databases into two schemas of one database, then do that instead.

Joe