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
.
---------------------------------
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
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
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 ??
And what about making references between tables (creating foreign keys) is it possible ?????
Richard Huxton <dev@archonet.com> wrote:
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
---------------------------------
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
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 !
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 ???
Thx for your help
Darko Prenosil <darko.prenosil@finteh.hr> wrote:
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
---------------------------------
Do you Yahoo!?
Free online calendar with sync to Outlook(TM).
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