Thread: Linking 2 or more databases.
Hi All,
I need to get information from 2 (or more) database preferably in one
select statement. Is there a way to join tables from different databases
in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view
'cross database' ? Can I make some server side programming to accomplish
this task ? Where do I find documentation if any ?
Thanks for any ideas,
Leif
See FAQ item 4.24. See the web site version because it is updated. --------------------------------------------------------------------------- Leif Jensen wrote: > > Hi All, > > I need to get information from 2 (or more) database preferably in one > select statement. Is there a way to join tables from different databases > in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view > 'cross database' ? Can I make some server side programming to accomplish > this task ? Where do I find documentation if any ? > > Thanks for any ideas, > > Leif > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Hi Bruce,
Thank you for you quick response. Just what I needed :-).
I just thought this was part of the SQL standard !?
Leif
On Tue, 22 Oct 2002, Bruce Momjian wrote:
>
> See FAQ item 4.24. See the web site version because it is updated.
>
> ---------------------------------------------------------------------------
>
> Leif Jensen wrote:
> >
> > Hi All,
> >
> > I need to get information from 2 (or more) database preferably in one
> > select statement. Is there a way to join tables from different databases
> > in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view
> > 'cross database' ? Can I make some server side programming to accomplish
> > this task ? Where do I find documentation if any ?
> >
> > Thanks for any ideas,
> >
> > Leif
> >
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman@candle.pha.pa.us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
Hi All,
I've got the contrib/dblink example working perfectly when connecting
to only one remote database, but when I wanna connect to two remote
databases, the last one screws up. I have been looking at the dblink code
and my SQL over and over, but to me it looks like it should work. Here are
the SQL:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
select project, employee,
dblink_tok(t2.dbl_p,1) as name,
startdato, slutdato,
dblink_tok(t1.dblink_p,0) as f1,
substr( dblink_tok(t1.dblink_p,1), 1, 20) as f2
from
(select dblink('hostaddr=192.168.10.1 dbname=db1',
'select gruppe, beskrv from dokumentno where klasse = 0 and dokno = 0')
as dblink_p)
as t1,
(select dblink('hostaddr=192.168.10.1 dbname=db2',
'select employee, firstname from employee')
as dbl_p)
as t2,
timesched
where
dblink_tok(t1.dblink_p,0) = project and
dblink_tok(t2.dbl_p,0) = employee
order by
employee, project, startdato;
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
and this is the result (partly):
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
project | employee | name | startdato | slutdato | f1 | f2
---------+----------+------+------------+------------+-----+-------
167 | 44 | | 2002-10-14 | 2002-10-25 | 167 | Text 1
173 | 44 | | 2002-10-28 | 2002-11-01 | 173 | Text 2
181 | 44 | | 2002-10-14 | 2002-11-29 | 181 | Text 3
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
and in the log file:
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
row number 102 is out of range 0..101
row number 102 is out of range 0..101
row number 102 is out of range 0..101
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
It clearly doesn't find the data of the second database/table (t2), but
I can't figure out why. If I switch the order of the tables in the from
clause, the problem is still in the second one (now t1). If only link to
one of them, it works nicely on either of them.
Please help,
Leif
On Wed, 23 Oct 2002, Leif Jensen wrote:
>
> Hi Bruce,
>
> Thank you for you quick response. Just what I needed :-).
>
> I just thought this was part of the SQL standard !?
>
> Leif
>
>
>
> On Tue, 22 Oct 2002, Bruce Momjian wrote:
>
> >
> > See FAQ item 4.24. See the web site version because it is updated.
> >
> > ---------------------------------------------------------------------------
> >
> > Leif Jensen wrote:
> > >
> > > Hi All,
> > >
> > > I need to get information from 2 (or more) database preferably in one
> > > select statement. Is there a way to join tables from different databases
> > > in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view
> > > 'cross database' ? Can I make some server side programming to accomplish
> > > this task ? Where do I find documentation if any ?
> > >
> > > Thanks for any ideas,
> > >
> > > Leif
> > >
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 4: Don't 'kill -9' the postmaster
> > >
> >
> > --
> > Bruce Momjian | http://candle.pha.pa.us
> > pgman@candle.pha.pa.us | (610) 359-1001
> > + If your life is a hard drive, | 13 Roberts Road
> > + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
Hi All (once more), Oops, sorry I forgot to mention: I'm running on PostgreSQL v.7.2.3, Linux 2.4.19 (Slackware 8.1). Leif On Wed, 23 Oct 2002, Leif Jensen wrote: > > Hi Bruce, > > Thank you for you quick response. Just what I needed :-). > > I just thought this was part of the SQL standard !? > > Leif > > > > On Tue, 22 Oct 2002, Bruce Momjian wrote: > > > > > See FAQ item 4.24. See the web site version because it is updated. > > > > --------------------------------------------------------------------------- > > > > Leif Jensen wrote: > > > > > > Hi All, > > > > > > I need to get information from 2 (or more) database preferably in one > > > select statement. Is there a way to join tables from different databases > > > in the same SQL ? Can I have a 'virtual' foreign key ? Can I make a view > > > 'cross database' ? Can I make some server side programming to accomplish > > > this task ? Where do I find documentation if any ? > > > > > > Thanks for any ideas, > > > > > > Leif > > > > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > > > TIP 4: Don't 'kill -9' the postmaster > > > > > > > -- > > Bruce Momjian | http://candle.pha.pa.us > > pgman@candle.pha.pa.us | (610) 359-1001 > > + If your life is a hard drive, | 13 Roberts Road > > + Christ can be your backup. | Newtown Square, Pennsylvania 19073 > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly >
Hi All,
I'm still trying to figure out how to connect to several tables in
remote database(s) using the contrib/dblink lib. I have succeeded in
crashing the postmaster several times. My last attempt was just to show
the pointers returned from the dblink call.
When connecting to only one 'foreign' table I got the bunch of values
back that I would expect, i.e it works fine, but when trying two tables
(from the same remote database) it runs for a long time using 100% cpu
with a lot of disk access and then finally crashes.
This is what I did (it also includes a 'tail -f' on the postgres log
file):
leif@casper$ psql -c "select u1.dblink_p, u2.dbl_p from
(select dblink('host=limbo dbname=mydb', 'select employee, firstname
from employee') as dblink_p) u1, (select dblink('host=limbo
dbname=mydb', 'select ordre, rekvisitionsnr from ut_order where
rekvisitionsnr is not null') as dbl_p) u2;" actoverview
DEBUG: server process (pid 20923) was terminated by signal 9
DEBUG: terminating any other active server processes
NOTICE: Message from PostgreSQL backend:
The Postmaster has informed me that some other backend
died abnormally and possibly corrupted shared memory.
I have rolled back the current transaction and am
going to terminate your database system connection and exit.
Please reconnect to the database system and repeat your query.
DEBUG: all server processes terminated; reinitializing shared memory and
semaphores
DEBUG: database system was interrupted at 2002-10-24 16:53:14 CEST
DEBUG: checkpoint record is at 0/1943BC0
DEBUG: redo record is at 0/1943BC0; undo record is at 0/0; shutdown FALSE
DEBUG: next transaction id: 2182; next oid: 130338
DEBUG: database system was not properly shut down; automatic recovery in
progress
DEBUG: ReadRecord: record with zero length at 0/1943C00
DEBUG: redo is not required
DEBUG: database system is ready
message type 0x44 arrived from server while idle
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
connection to server was lost
I'm running on
PostgreSQL v.7.2.3,
Linux 2.4.19 (Slackware 8.1).
and the remote server that I'm using is
PostgreSQL v.7.2.1,
Linux 2.4.18 (Slackware 8.0)
but from the log on the remote, it seems that it never did connect to
it.
Please help,
Leif