RE:  ability to access tables from different DB in same SQL query

I have a good example right now of why this is a good feature, 
and why I suspect it is impractical or impossible to implement 
one of my bread-n-butter sybase-based apps using PG.  This is 
rather a fraught question, because NOT migrating to PG may cost 
us thousands of dollars.  More like tens of thousands, since we 
would need to bring our old licenses up to date (I just found a 
horrendous bug in Sybase SQL Server 4.9 date arithmetic) and 
buy a couple of new licenses for our project.  So a lot is
riding on our ability to port existing apps to PG.

I don't think this feature is in SQL92, so I'm making a plea for
an "extra", another way in which PG can be "better than" the bare
standard.

-------

There is a telemetry gathering daemon (several actually) dumping
telemetry into tables.  The tables are large (within the limits
of Sybase, which are 250 fields per record and 3700-some bytes).
Say about 220 fields per record, in the high hundreds of bytes,
all ints and floats, and the tables are ever growing in length.
Say between 10K and 250K records depending on how long the logger
has been running and at what frequency telemetry has been gathered.

The engineering staff person wants to do some analysis on these
data.  The front end app provides an easy, friendly way to select
a date range (or other RSE) from the huge mass of telemetry,
and to refine the FSE.

The selected data are then copied into a temp table using a 
SELECT INTO.

Here's the point, then.  The user's temp table wants to live in
a DB with generous permissions:  ordinary users can create and
delete tables!  But the original telemetry data want to live in
a very protected DB where users absolutely cannot mess with the
original tables OR go creating tables of their own that compete
with the originals for storage space.

This is where two important features of Sybase come in handy, and
I don't think Oracle does this (correct me, O Oracle users, if I'm
slandering the product):  Different DB can be located on different
partitions, or different inviolable physical chunks of one
partition.  One server can "see" multiple DB.  And Sybase SQL queries 
can span databases, that is, the database name is part of the FQON 
(fully qual object name).  So it's as easy as

	select * into sandbox.guest.DMyn_de897082595_D1 from
		telem.dbo.hires_Log_1 where logstamp between
		'Jun 19 1998 03:00' and 'Jun 19 1988 08:00'

The user has no privs other than 'select' anywhere in the telem DB.
He/she now works freely with the smaller, lighter table in the
sandbox DB (*not* repeating expensive queries against the potentially
very, very large table in 'telem').  The user could, of course, 
specify a very large range of data and create quite a large temp table 
(there are some safety limits in the app, but they are generous).  
The worst result of this would be that the user would get bored waiting 
for the query to return :-), the dataset would be too big to plot easily, 
and other users of the sandbox DB would be annoyed when all the disk 
space was used up.

Now, as I understand PG, the user would have to create the temp table 
in the incoming telemetry db, because the SELECT query could not
reference tables in 2 different DB.   So he/she would have to be granted 
table create/delete in a place where creation of large tables could 
easily interfere with the essential job of logging the incoming telemetry.
Not acceptable.  The user playpen has to be separate from the incoming
production data.

If PG offers some other way of 

	guaranteeing inviolable space for tables that grow with a 
		realtime feed, 
	yet making those tables accessible -- via SQL query --
		to nonpriv users who want to grab chunks of the data into 
		temp tables created on the fly, 

I would very much like to know how to do that.  Yeah, I could do it 
by buffering the data row by row in the app and then inserting it from 
the app into the new table, but what the heck is SQL for if not to do 
that job more efficiently and concisely?  SELECT INTO is the right
syntax.  But the limitations of PG prevent my using it.

Yeah, I could mirror the whole telemetry db to a "public" server 
periodically, and let the users query that.  But the engineers want 
up-to-the-last-sample data to analyze;  they can change the sampling 
speed in real time, if something interesting is happening;  they don't 
want to wait for the 24-hourly or 12-hourly flush to the warehouse.

Solutions using psql special commands are not acceptable;  it has to
be SQL.

I hope this makes the case for cross-DB queries, which I have agitated
feebly for in the past but never really justified.  If not (if there is
a good way to achieve the same result in PG without x-db queries) then
please do tell!

de


Mon, 22 Jun 1998 16:02:00 -0700 (PDT)
De Clarke <de@ucolick.org>
SQL queries accessing tables in more than one db
De Clarke wrote:
> 
> RE:  ability to access tables from different DB in same SQL query

[snip]

> Here's the point, then.  The user's temp table wants to live in
> a DB with generous permissions:  ordinary users can create and
> delete tables!  But the original telemetry data want to live in
> a very protected DB where users absolutely cannot mess with the
> original tables OR go creating tables of their own that compete
> with the originals for storage space.
> 
> 	select * into sandbox.guest.DMyn_de897082595_D1 from
> 		telem.dbo.hires_Log_1 where logstamp between
> 		'Jun 19 1998 03:00' and 'Jun 19 1988 08:00'

A couple of comments on this.  Sybase does have quite an elegant
system for this <db>.<owner>.<table>.<column> for simple queries,
table can be omitted, when not ambiguous, user can be omitted and the
full form can be used in all queries if the permissions are right.  So
you could just as easily say:

select * from telem..hires_Log_1 where logstamp=sandbox..table1.timestamp

Now we could take it one step further and put the name of the database
server before all of this, so you could say:

select * from office1.sales..saleslog,
              office2.sales..saleslog,
... etc

I wouldn't vouch for performance in this case though.

Ocie
 

Mon, 22 Jun 1998 16:24:39 -0700 (PDT)
ocie@paracel.com
Re: [HACKERS] SQL queries accessing tables in more than one db