cross-db queries (was Are we losing momentum?)
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
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
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.
It may not be the "best" solution, but it is one that is possible to use now (if you don't want atomic remote updates) or could be done with atomic remote updates relatively soon.
It is similar to the way MS-SQL works. And, once setup the local and remote tables all look the same to the client application. I think that is important.
Later
Rob
Import Notes
Resolved by subject fallback
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
...Except if you need only few records from remote database(on ISDN link for
example) and remote view (dblink) first selects all the records from remote,
and after that WHERE clause is executed on prepared result.
You are absolutely right. Hadn't considered that situation.
Later
Rob
Import Notes
Resolved by subject fallback
Darko Prenosil wrote:
Unfortunately any other way ends up with first selecting *ALL* records from
host ! If there is no such limitation I'll be pretty satisfied with dblink,
and will newer ask for "cross-db-queries" again !!!
Yeah, this is why a proper implementation following the spec is needed.
If the external access was part of the backend, then the planner could
be taught to push down qualifiers to the external source where
appropriate (I think -- maybe Tom will comment on this).
Joe
Import Notes
Reply to msg id not found: 200304161829.23603.darko.prenosil@finteh.hr
Joe Conway <mail@joeconway.com> writes:
Darko Prenosil wrote:
Unfortunately any other way ends up with first selecting *ALL* records from
host ! If there is no such limitation I'll be pretty satisfied with dblink,
and will newer ask for "cross-db-queries" again !!!
Yeah, this is why a proper implementation following the spec is needed.
If the external access was part of the backend, then the planner could
be taught to push down qualifiers to the external source where
appropriate (I think -- maybe Tom will comment on this).
Yes, the newer version of SQL-MED has APIs that allow this sort of thing
to be done. Of course, it's another huge chunk of work beyond basic
SQL-MED ... but at least the roadmap is there, and when we get to the
end of the road we might even find other DBMSes that can speak the same
language. If we invent our own spec the chance of handling cross-DBMS
queries intelligently is nil :-(
regards, tom lane
On Wednesday 16 April 2003 15:31, Rob Butler wrote:
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.It may not be the "best" solution, but it is one that is possible to use
now (if you don't want atomic remote updates) or could be done with atomic
remote updates relatively soon.It is similar to the way MS-SQL works. And, once setup the local and
remote tables all look the same to the client application. I think that is
important.
...Except if you need only few records from remote database(on ISDN link for
example) and remote view (dblink) first selects all the records from remote,
and after that WHERE clause is executed on prepared result.
I used dblink a lot, and the only way to avoid this was to create
function(with parameters) that executes dblink , or to create view that
sends the original query to the host (Which is why Joe added
dblink_current_query() function to dblink at first place ).
First way has limitations because You can't add rewrite rule for the
function(or at last I newer succeed with that).
The second way has very bad limitation because you always must:
SELECT * from remoteView(all the fields), otherwise you broke view
definition, and you can't for example SELECT count(*) FROM remoteView.
Unfortunately any other way ends up with first selecting *ALL* records from
host ! If there is no such limitation I'll be pretty satisfied with dblink,
and will newer ask for "cross-db-queries" again !!!
P.S.: Sorry for bad English !