I beg to differ with Tom Lane's opinion, but schemas do not solve the
problem with multi-database queries because of the following reasons:

1) When dealing with large databases, the use of multiple databases
reduces the risk of wiping out all the data, and reduces the recovery
time in case of accidents.

2) Multiple databases allow for different management policies on each
database, whereas schemas require some consistency across them all.
In a heterogeneous working environment, this is a signficant issue.

3) PostgreSQL should strive for heterogeneous multi-database queries,
so that applications currently using other systems could be slowly
migrated to PostgreSQL by moving portions of a database from other
vendors to PostgreSQL. In my work, the lack of PostgreSQL - Oracle
connectivity is a disabling impediment to wider PostgreSQL usage.

--Bob

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| President, Congenomics Inc. | URL:   http://www.congen.com/~bruc |
| P.O. Box 314                | Phone: 609 818 7251                | 
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+


Tue, 15 Apr 2003 18:54:53 -0400 (EDT)
"Robert E. Bruccoleri" <bruc@stone.congenomics.com>
Re: Are we losing momentum?
"Robert E. Bruccoleri" <bruc@stone.congenomics.com> writes:
> I beg to differ with Tom Lane's opinion, but schemas do not solve the
> problem with multi-database queries because of the following reasons:

> 1) When dealing with large databases, the use of multiple databases
> reduces the risk of wiping out all the data, and reduces the recovery
> time in case of accidents.

> 2) Multiple databases allow for different management policies on each
> database, whereas schemas require some consistency across them all.
> In a heterogeneous working environment, this is a signficant issue.

> 3) PostgreSQL should strive for heterogeneous multi-database queries,
> so that applications currently using other systems could be slowly
> migrated to PostgreSQL by moving portions of a database from other
> vendors to PostgreSQL. In my work, the lack of PostgreSQL - Oracle
> connectivity is a disabling impediment to wider PostgreSQL usage.

Please keep in mind that I was replying to a poster who said "cross-db
queries on the same server (meaning same postmaster, for our purposes)
are trivial; why hasn't Postgres got them when everybody else does?"

Your above arguments are all good ones, but they presume a scenario that
is much different and *MUCH* harder to implement than local "cross
database" queries.  My point is that schemas solve the same-server
problems that the original poster was interested in.  I did not say,
nor mean, that there is no need for cross-server queries.  But that is
a different problem.  Today we can only offer dblink; maybe someday
SQL-MED.

			regards, tom lane


Tue, 15 Apr 2003 19:18:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Are we losing momentum? 
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Please keep in mind that I was replying to a poster who said
> "cross-db
> queries on the same server (meaning same postmaster, for our
> purposes)
> are trivial; why hasn't Postgres got them when everybody else does?"

I think you're referring to one of my messages. If this is the case,
then you've misquoted me, that was not what I said.











__________________________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo
http://search.yahoo.com


Tue, 15 Apr 2003 16:51:18 -0700 (PDT)
ow <oneway_111@yahoo.com>
Re: Are we losing momentum? 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Please keep in mind that I was replying to a poster who said
    Tom> "cross-db queries on the same server (meaning same
    Tom> postmaster, for our purposes) are trivial; why hasn't
    Tom> Postgres got them when everybody else does?"

BTW, DB2 doesn't have 'em either. 

In DB2, you have Database -> Schema -> Objects

In DB2, you can of course have cross-schema queries but no cross-db
queries, unless you rig up the federated functionality to connect one
db to the other.

Much of the confusion stems from SQL-Server and Sybase having: 

   Database -> Objects

The Database is used to identify distinct schemas. I'm not sure if in
these systems they are physically separate entities (different lock
manager etc.) 

-- 
Peace, at last ?
Sailesh
http://www.cs.berkeley.edu/~sailesh


15 Apr 2003 17:50:59 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Are we losing momentum?
ow kirjutas K, 16.04.2003 kell 02:51:
> --- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > Please keep in mind that I was replying to a poster who said
> > "cross-db
> > queries on the same server (meaning same postmaster, for our
> > purposes)
> > are trivial; why hasn't Postgres got them when everybody else does?"
> 
> I think you're referring to one of my messages. If this is the case,
> then you've misquoted me, that was not what I said.

But what did you say then ?

I don't think that what MySQL has as databases is much different from
our schemas, so I too had some difficulty understandin what you were
complaing about

---------------
Hannu


16 Apr 2003 12:16:54 +0300
Hannu Krosing <hannu@tm.ee>
Re: Are we losing momentum?
Dear Tom,
> 
> Please keep in mind that I was replying to a poster who said "cross-db
> queries on the same server (meaning same postmaster, for our purposes)
> are trivial; why hasn't Postgres got them when everybody else does?"

My apologies for missing that context.

> Your above arguments are all good ones, but they presume a scenario that
> is much different and *MUCH* harder to implement than local "cross
> database" queries.  My point is that schemas solve the same-server
> problems that the original poster was interested in.  I did not say,
> nor mean, that there is no need for cross-server queries.  But that is
> a different problem.  Today we can only offer dblink; maybe someday
> SQL-MED.

Agreed. Thanks. --Bob

+-----------------------------+------------------------------------+
| Robert E. Bruccoleri, Ph.D. | email: bruc@acm.org                |
| President, Congenomics Inc. | URL:   http://www.congen.com/~bruc |
| P.O. Box 314                | Phone: 609 818 7251                | 
| Pennington, NJ 08534        |                                    |
+-----------------------------+------------------------------------+


Wed, 16 Apr 2003 12:41:09 -0400 (EDT)
"Robert E. Bruccoleri" <bruc@stone.congenomics.com>
Re: Are we losing momentum?