Queries across multiple databases (was: SELECT from 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.
====================================================================
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
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.
====================================================================
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.