
>> 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

Mon, 21 May 2001 07:55:13 -0400
Andrew Rawnsley <ronz@ravensfield.com>
Queries across multiple databases =?iso-8859-1?q?=A0?=(was: SELECT
	from a table in another database).
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.
====================================================================

Mon, 21 May 2001 16:04:25 +0200
Zak McGregor <zak@mighty.co.za>
Re: Queries across multiple databases ���(was: SELECT from a table in another database).
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

Mon, 21 May 2001 12:26:11 -0400
Andrew Rawnsley <ronz@ravensfield.com>
Re: Queries across multiple databases (was: SELECT from a table in
	another database).
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


Mon, 21 May 2001 17:40:54 +0100
"Richard Huxton" <dev@archonet.com>

 =?iso-8859-1?Q?Re:_=5BGENERAL=5D_Queries_across_multiple_databases_=A0=28?=
	=?iso-8859-1?Q?was:_SELECT_from_a_table_in_another_database=29.?=
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.
====================================================================

Mon, 21 May 2001 18:50:10 +0200
Zak McGregor <zak@mighty.co.za>
Re: Queries across multiple databases ���(was: SELECT from a table in another database).
> > 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

Tue, 22 May 2001 07:31:18 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Re: [GENERAL] Queries across multiple databases ?(was:
	SELECT from a table in another database).
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

Tue, 22 May 2001 14:51:28 +0200
snpe <snpe@verat.net>
Multiple database - multiple query - multiple server 
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.


Tue, 22 May 2001 13:06:30 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Multiple database - multiple query - multiple server