Thread: Queries across multiple databases  (was: SELECTfrom a table in another database).

>> I don't know what you are using those database for, but nothing prevents
> >you from letting your clients connect to the different databases the
> >same time.


>But that requires me to make a new database connection for each database I
>need to access.

>And putting 200+ tables in one single database is not an option.

>The application which needs to be able to do this is a
>cross-database-application (MSSQL, Oracle, Sybase) and I have almost no
>room for doing major changes to the SQL which this application uses.

>But the lack of this feature in Postgres makes it almost impossible to
>make a structured database design for huge application. I know this
>question have been asked before in another postgres forum as early as
>1998, and what Bruce Momjian said then was that most commercial databases
>couldn't do it, which was probably right for 1998, but today even MySQL
>can do this! Sybase, Oracle and MSSQL can also do this. I think even DB2
>and Informix can.

>I was really suprised when I discovered that this was even an issue with
>Postgres, because everything else in this wonderful DBM is on an
>enterprise level of quality and functionality.

I'm stuck in the same cleft in the tree - database application originally
written for Oracle and Sybase, that still needs to work in Oracle, and the
SQL and database structure etched in stone.  The problem isn't about a client
with multiple connections, its about executing the following query:

SELECT A.*, B.* FROM FOO.USERS A, BAR.FAVORITE_BEERS B WHERE A.USER =
B.GUZZLER

Putting 200+ tables in a database certainly isn't a big deal, as I think Tom
Lane points out in another post in this thread. I am poking at the parser in
my copious free time just to see how easy it would be to just strip a schema
name off the items in the FROM clause before anything happens, but one
doesn't pick up the internals of the parser in 10-15 minutes a day...hints
anyone? Anyway, this way I COULD put all the tables in one database, keep the
schema-based queries, and no one would ever know.

I would twitch on the floor in utter extasy if I could hose Oracle...while
their licensing is more flexible than in the past, it still doesn't sit
right, and despite all their claims to the contrary their java support is a
joke.  And maybe their pinheaded sales reps WOULD STOP CALLING ME EVERY WEEK.

If I ever come up with said schema-dropping patch, and anyone else wants it,
let me know.

--
Regards,

Andrew Rawnsley
Ravensfield Geographic Resources, Ltd.
(740) 587-0114
www.ravensfield.com

On Mon, 21 May 2001 07:55:13 -0400
Andrew Rawnsley <ronz@ravensfield.com> wrote:

> If I ever come up with said schema-dropping patch, and anyone else
wants it,
> let me know.

I'd dance a happy jig ;-)

I'm not sure whether it is quite the way to do it, but I'd have a better
time with things if I could span databases in a single request. Are
there theoretical problems with spanning databases in a single query? Is
it a feature of bad database design & implementation?

Thanks

Ciao

Zak

--
====================================================================
Zak McGregor
http://www.carfolio.com - Specifications of cars online. Over 7000!
--------------------------------------------------------------------
Of course my password is the same as my pet's name.
My macaw's name was Q47pY!3, but I change it every 90 days.
====================================================================

From: "Zak McGregor" <zak@mighty.co.za>

> On Mon, 21 May 2001 07:55:13 -0400
> Andrew Rawnsley <ronz@ravensfield.com> wrote:
>
> > If I ever come up with said schema-dropping patch, and anyone else
> wants it,
> > let me know.
>
> I'd dance a happy jig ;-)
>
> I'm not sure whether it is quite the way to do it, but I'd have a better
> time with things if I could span databases in a single request. Are
> there theoretical problems with spanning databases in a single query? Is
> it a feature of bad database design & implementation?

I think the developers are planning full schema support for the relatively
near future (possibly even 7.2, but check the archives and see what's been
said). Although it looks easy to access a table from another database,
things can rapidly become more complicated as you start having to deal with
transactions, triggers, rules, constraints...

- Richard Huxton

On Mon, 21 May 2001 16:04:25 +0200
Zak McGregor <zak@mighty.co.za> wrote:

> I'm not sure whether it is quite the way to do it, but I'd have a
better
> time with things if I could span databases in a single request. Are
> there theoretical problems with spanning databases in a single query?
Is
> it a feature of bad database design & implementation?

I suspect my statement was not so clear. I mean is it a feature of *my*
bad database design & implementation if I am finding it necessary to
contemplate cross-database queries?

Apologies for the vagueness of the original...

Ciao

Zak


--
====================================================================
Zak McGregor
http://www.carfolio.com - Specifications of cars online. Over 7000!
--------------------------------------------------------------------
Of course my password is the same as my pet's name.
My macaw's name was Q47pY!3, but I change it every 90 days.
====================================================================