pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDiferent databases on same query...
9+ messages / 7 participants
[nested] [flat]
* Diferent databases on same query...
@ 2001-10-19 01:45 Douglas Rafael da Silva <douglas@inducola.com.br>
0 siblings, 1 reply; 9+ messages in thread
From: Douglas Rafael da Silva @ 2001-10-19 01:45 UTC (permalink / raw)
To: pgsql-sql
Hi,
I'd like to do a query where can be possible I access tables from
diferent databases on the same query.
On MySQL, I do:
SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
Database1.People1, Database2.Result1 WHERE ...
I think on ORACLE works like as:
SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
People1@Database1, Result1@Database2 WHERE ...
But Who I can to do this on Postgresql ?
Thank you.
Douglas Rafael.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-19 19:05 Andre Schnabel <a_schnabel@t-online.de>
parent: Douglas Rafael da Silva <douglas@inducola.com.br>
0 siblings, 1 reply; 9+ messages in thread
From: Andre Schnabel @ 2001-10-19 19:05 UTC (permalink / raw)
To: pgsql-sql
"Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag
news:3BCF859C.D42C8895@inducola.com.br...
> Hi,
>
> I'd like to do a query where can be possible I access tables from
> diferent databases on the same query.
> On MySQL, I do:
>
> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
> Database1.People1, Database2.Result1 WHERE ...
>
> I think on ORACLE works like as:
>
> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
> People1@Database1, Result1@Database2 WHERE ...
>
> But Who I can to do this on Postgresql ?
>
You CANNOT do that with PostgreSQL.
But why do you want to do that? IMHO it's a rather bas design to hold data
in different places, if you need to select them in one query.
Is there a real reason to hold the tables in different databases?
Andre
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-19 22:53 chrup@mac.com
parent: Andre Schnabel <a_schnabel@t-online.de>
0 siblings, 1 reply; 9+ messages in thread
From: chrup@mac.com @ 2001-10-19 22:53 UTC (permalink / raw)
To: Andre Schnabel <a_schnabel@t-online.de>; +Cc: pgsql-sql
Andre,
The only reason to have data for one query in different databases is if
the databases are on different machines. If you're running on PC-type
hardware, you might have to do that some times for performance reasons.
But if at that point, you should ask yourself if you shouldn't invest in
some faster hardware ;-).
Best regards (Tschüs),
Chris
On Friday, October 19, 2001, at 02:05 , Andre Schnabel wrote:
>
> "Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag
> news:3BCF859C.D42C8895@inducola.com.br...
>> Hi,
>>
>> I'd like to do a query where can be possible I access tables from
>> diferent databases on the same query.
>> On MySQL, I do:
>>
>> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
>> Database1.People1, Database2.Result1 WHERE ...
>>
>> I think on ORACLE works like as:
>>
>> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
>> People1@Database1, Result1@Database2 WHERE ...
>>
>> But Who I can to do this on Postgresql ?
>>
>
> You CANNOT do that with PostgreSQL.
> But why do you want to do that? IMHO it's a rather bas design to hold data
> in different places, if you need to select them in one query.
> Is there a real reason to hold the tables in different databases?
>
> Andre
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
--
Chris Ruprecht
Network grunt and bit pusher extraordinaíre
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-21 00:11 Douglas Rafael da Silva <douglas@inducola.com.br>
0 siblings, 1 reply; 9+ messages in thread
From: Douglas Rafael da Silva @ 2001-10-21 00:11 UTC (permalink / raw)
To: pgsql-sql; +Cc: Andre Schnabel <a_schnabel@t-online.de>
>> Hi,
>>
>> I'd like to do a query where can be possible I access tables from
>> diferent databases on the same query.
>> On MySQL, I do:
>>
>> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
>> Database1.People1, Database2.Result1 WHERE ...
>>
>> I think on ORACLE works like as:
>>
>> SELECT People1.PersID, People1.Name, Result1.Value, Result1.Date FROM
>> People1@Database1, Result1@Database2 WHERE ...
>>
>> But Who I can to do this on Postgresql ?
>>
>
>You CANNOT do that with PostgreSQL.
>But why do you want to do that? IMHO it's a rather bas design to hold data
>in different places, if you need to select them in one query.
>Is there a real reason to hold the tables in different databases?
>
>Andre
Hi, Andre !
Maybe yes, maybe not. I have 6 companies of the same group, little but
they are like as, and they share the main database. But there are data
that are specific of each one. The design of tables is the same for all
them. So, on mysql I had on diferent databases. Thus, on the same query,
I select the name of employee, the truck and invoice of the specific
company. Two companies have server separated. How can I do to do
distribute database server with postgres ? You have a suggestion ?
Thank you,
Douglas.
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-23 15:03 Troy <tjk@tksoft.com>
0 siblings, 0 replies; 9+ messages in thread
From: Troy @ 2001-10-23 15:03 UTC (permalink / raw)
To: a_schnabel@t-online.de; +Cc: pgsql-sql
Just my two cents on the issue:
A good reason would be having several databases which
each use the same set of tables which contain some information
which takes a lot of storage, such as dictionaries or map
information. You wouldn't want to maintain several copies
of 500 Mb tables, especially if they get updated frequently.
The workaround, for now, is to have a table just for the
foreign keys for the different systems, and to query the
shared database separately from within your programming
environment.
Troy
>
>
> "Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag
> news:3BCF859C.D42C8895@inducola.com.br...
> > Hi,
> >
> > I'd like to do a query where can be possible I access tables from
> > diferent databases on the same query.
> > On MySQL, I do:
> >
> > But Who I can to do this on Postgresql ?
>
> You CANNOT do that with PostgreSQL.
> But why do you want to do that? IMHO it's a rather bas design to hold data
> in different places, if you need to select them in one query.
> Is there a real reason to hold the tables in different databases?
>
> Andre
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-23 20:17 Andre Schnabel <a_schnabel@t-online.de>
parent: Douglas Rafael da Silva <douglas@inducola.com.br>
0 siblings, 0 replies; 9+ messages in thread
From: Andre Schnabel @ 2001-10-23 20:17 UTC (permalink / raw)
To: pgsql-sql
"Douglas Rafael da Silva" <douglas@inducola.com.br> schrieb im Newsbeitrag
news:3BD2129F.7D200D01@inducola.com.br...
>
> Hi, Andre !
>
> Maybe yes, maybe not. I have 6 companies of the same group, little but
> they are like as, and they share the main database. But there are data
> that are specific of each one. The design of tables is the same for all
> them. So, on mysql I had on diferent databases. Thus, on the same query,
> I select the name of employee, the truck and invoice of the specific
> company. Two companies have server separated. How can I do to do
> distribute database server with postgres ? You have a suggestion ?
Hi Douglas,
ok, that's a good point to have different databases in one query.
Unfortunately there is no way to do this with postgresql. At least no way
with the standard distribution.
Maybe it's worth to try replication. You should be able to replicate the
tables of your main db between the others. But I never tried that. AFAIK, he
most advanced replication tooll is PostgreSQL Replicator
(http://pgreplicator.sourceforge.net/).
Maybe it's the better way to go back(?) to MySQL or have a look at
Interbase.
Andre
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-26 14:24 Horst Herb <hherb@malleenet.net.au>
parent: chrup@mac.com
0 siblings, 1 reply; 9+ messages in thread
From: Horst Herb @ 2001-10-26 14:24 UTC (permalink / raw)
To: pgsql-sql
On Saturday 20 October 2001 08:53, you wrote:
> Andre,
>
> The only reason to have data for one query in different databases is if
> the databases are on different machines. If you're running on PC-type
> hardware, you might have to do that some times for performance reasons.
> But if at that point, you should ask yourself if you shouldn't invest in
> some faster hardware ;-).
There are many more good reasons to do so. Example from my own domain,
medicine:
1.) one database hold demographic information. This information is needed by
administration, nursing, and medical staff as well as by external service
providers such as pathology.
2.) another database holds medical information. The information here is
highly sensitive. The database admin should be a medical doctor,and it must
be unaccessible to clerical staff. SQL permissions are only a *very* weak
protection, not good enough for this purpose. For many good reasons, foremost
security, this information will not reside in the same database (sometimes
not even on the same machine) as the administrative data or the demographic
data.
3.) For obvious reasons, it would be bad to replicate the demographic data in
order to provide this data to administration, medical staff etc for their own
purposes.
Thus, we have multiple databases on multiple servers all depending on each
other. Some servers are read-only, others need the backend to log every
single query for audit trailing. The latter alone would blow the storage
needs out of proportion if applied to the whole set of databases. At present,
it is up to our client software to sort this out "manually". A minor
headache.
Horst
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-26 16:44 Josh Berkus <josh@agliodbs.com>
parent: Horst Herb <hherb@malleenet.net.au>
0 siblings, 1 reply; 9+ messages in thread
From: Josh Berkus @ 2001-10-26 16:44 UTC (permalink / raw)
To: Horst Herb <hherb@malleenet.net.au>; pgsql-sql
Horst,
> There are many more good reasons to do so. Example from my own
> domain,
> medicine:
> Thus, we have multiple databases on multiple servers all depending on
> each
> other.
Your reasons all make sense. What you need is not inter-database
queries but a robust middleware layer, such as J2EE. Your sort of
situation is why middleware exists.
If GreatBridge was still around, I'd reccommend you contact them, as I
knwo they implemented a solution for your sort of situation. However, I
don't know who'd do it now.
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Diferent databases on same query...
@ 2001-10-26 17:41 Thomas Good <tomg@admin.nrnet.org>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 9+ messages in thread
From: Thomas Good @ 2001-10-26 17:41 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: Horst Herb <hherb@malleenet.net.au>; pgsql-sql
On Fri, 26 Oct 2001, Josh Berkus wrote:
> Horst,
>
> > There are many more good reasons to do so. Example from my own
> > domain,
> > medicine:
> > Thus, we have multiple databases on multiple servers all depending on
> > each
> > other.
>
> Your reasons all make sense. What you need is not inter-database
> queries but a robust middleware layer, such as J2EE. Your sort of
> situation is why middleware exists.
Josh,
I'm pretty sure Horst does this already - check out GNU Med.
(Hallo Horst, Viele Grüsse aus New York. ;-)
I guess the problem is that the procedural language mentioned in
the original post has a few hurdles yet to manage.
BTW, I do this (within the context of very small scripts) using
perl DBI. It is cake with Perl. And - your code is relatively
portable. Aside from certain Oracle idiosyncracies of course
(NVL, DECODE, SYSDATE...)
> If GreatBridge was still around, I'd reccommend you contact them, as I
> knwo they implemented a solution for your sort of situation. However, I
> don't know who'd do it now.
What about RedHat? Are they working on this?
Dieter (Simader) of SQL Ledger also does this sort of thing within
his product (which uses perl dbi): www.sql-ledger.org
Cheers
--------------------------------------------------------------------
Saint Vincent Catholic Medical Centers
--------------------------------------------------------------------
Thomas Good tomg@ { admin | q8 } .nrnet.org
Programmer/Analyst Phone: 718-818-5528
Behavioral Health Services Fax: 718-818-5056
Residential Services Mobile: 917-282-7359
--------------------------------------------------------------------
/* Rekordmeister ist nur der FC Bayern München! */
--------------------------------------------------------------------
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2001-10-26 17:41 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-10-19 01:45 Diferent databases on same query... Douglas Rafael da Silva <douglas@inducola.com.br>
2001-10-19 19:05 ` Andre Schnabel <a_schnabel@t-online.de>
2001-10-19 22:53 ` chrup@mac.com
2001-10-26 14:24 ` Horst Herb <hherb@malleenet.net.au>
2001-10-26 16:44 ` Josh Berkus <josh@agliodbs.com>
2001-10-26 17:41 ` Thomas Good <tomg@admin.nrnet.org>
2001-10-21 00:11 Re: Diferent databases on same query... Douglas Rafael da Silva <douglas@inducola.com.br>
2001-10-23 20:17 ` Andre Schnabel <a_schnabel@t-online.de>
2001-10-23 15:03 Re: Diferent databases on same query... Troy <tjk@tksoft.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox