Thread: Queries across multiple databases (was: SELECTfrom a table in another database).
Queries across multiple databases (was: SELECTfrom a table in another database).
From
Andrew Rawnsley
Date:
>> 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
Re: Queries across multiple databases (was: SELECT from a table in another database).
From
Zak McGregor
Date:
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. ====================================================================
Re: [GENERAL] Queries across multiple databases (was: SELECT from a table in another database).
From
"Richard Huxton"
Date:
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
Re: Queries across multiple databases (was: SELECT from a table in another database).
From
Zak McGregor
Date:
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. ====================================================================
Re: Queries across multiple databases (was: SELECT from a table in another database).
From
Andrew Rawnsley
Date:
On Monday 21 May 2001 10:04am, you wrote: > 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 imagine its one of those initial implementation decisions made 12 years ago by people completely separate from the current maintainers, to whom this sort of need wasn't a priority. I would also hazard to guess (in near-complete ignorance - correct me if I'm wrong) that it would be difficult to change without doing serious re-plumbing. Hence its 'exotic' status. The current set of maintainers certainly have enough to think about without slamming them with that sort of change... I do think the issue will continue to bubble to the top as more and more folk get stuck into the position of wanting to transition to Postgres from Oracle/Sybase/whatever and either don't want to recode or simply can't, and also want to maintain as much database neutrality as possible. The 'drop the schema' thing isn't quite the way to do it, no. Its a gross hack, but if it gets us through until it becomes a more important issue then so be it. Hence the sense of getting the source... (or, as I have heard Paul Everitt say, you can actually fix the problem after hundreds of manhours as opposed to just being told that your support level isn't high enough). -- Regards, Andrew Rawnsley Ravensfield Geographic Resources, Ltd. (740) 587-0114 www.ravensfield.com
Re: Re: [GENERAL] Queries across multiple databases ?(was: SELECT from a table in another database).
From
Bruce Momjian
Date:
> > 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... Schema is on my radar screen for 7.2. I am waiting to do some research in what needs to be done, but my initial idea is to use the system cache to do namespace mapping, just like is done now for temp tables. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Hello,
I know that queries across multiple databases are in TODO list
Does it mean that databases can be on multiple servers (postmasters) and
that update,insert,delete across multiple databases are possible.
Thanks
snpe@verat.net
On Tue, 22 May 2001, snpe wrote: > Hello, > I know that queries across multiple databases are in TODO list > Does it mean that databases can be on multiple servers (postmasters) and > that update,insert,delete across multiple databases are possible. All the multiple database stuff is in the exotic features section, it's not here now, and probably won't be for a while unless someone steps up to do it.