Hello all,

I took a look at the docs for Postgres views & rules.  I also took a look at dblink.  I think there is a very plausible way to get "cross-db" queries in postgres

1) Support for 2PC added.
2) Modify dblink (if it needs modification) to operate within the current transaction created by the user (if a user transaction was created).  If no transaction exists (was created by the user) but local and remote tables / views are updated by the SQL statement then postgres creates a transaction to operate within.  That way both local and remote tables / views are updated as a single atomic unit.  (Which is what the user would expect to happen.)
3) Add a new "keyword" to Postgres.  Choose any of the following:
	a) create remoteview ... (all necessary information about remote machine and query)
	b) create view [local | remote] .... (necessary info)
	c) create view remote-server.schema.table (necessary info)

Postgres could then create a read only view of the remote table using dblink.  A user could add additional rules to allow for the view to be updateable just as they do now.

Also, as an additional feature for postgres we could simplify creating updateable views for "simple" queries.  I.E. 

	create view [updateable] (necessary info / simple select statement).  If a simple select statement is used, and updateable is specified, postgres creates all the rules to implement an updateable view.  This is not necessary of course, it would just simply make the common (?) case of creating an updateable view from a simple query.

The support for 2PC and modification of dblink is only necessary IF we want to be able to update tables on the remote server AND have guarantees that the updates on both ends succeeds / fails as a unit.  If we want read only access to remote tables, then one could create a view (using a custom on select rule) and use dblink right now!

What do you think?

Later
Rob


Wed, 16 Apr 2003 10:10:28 -0400
Rob Butler <robert.butler5@verizon.net>
cross-db queries (was Are we losing momentum?)
On Wednesday 16 April 2003 19:40, Rob Butler wrote:
> 	a) create remoteview ... (all necessary information about remote machine
> and query) b) create view [local | remote] .... (necessary info)
> 	c) create view remote-server.schema.table (necessary info)

That is not a good way. Oracle does/recommends a create synonym so that either 
a remote view or table can be treated as if it is a loacl table/view. This 
takes care of select/insert/update/delete etc. I think that is a better way 
of integrating remote objects in current database. 

Of course, if there are some standards regarding remote database object, they 
will take precedence over oracle style of syntax. But personally I  think 
oracle syntax is good enough. (I just check sql 92 syntax and it does have 
provision for remote database access)

 Shridhar


Wed, 16 Apr 2003 19:55:43 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: cross-db queries (was Are we losing momentum?)
Rob Butler wrote:
> 
> What do you think?
> 

See my last post to Shridhar -- the answer is to follow the existing 
spec covering external data access. Search the archives for SQL-MED. It 
is a non-trivial undertaking which is probably why it has not been 
undertaken yet ;-)

Joe


Wed, 16 Apr 2003 09:22:27 -0700
Joe Conway <mail@joeconway.com>
Re: cross-db queries (was Are we losing momentum?)