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
wayto 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
transactionwas created). If no transaction exists (was created by the user) but local and remote tables / views are
updatedby 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
aboutremote 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
allowfor 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
updateableis 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
serverAND have guarantees that the updates on both ends succeeds / fails as a unit. If we want read only access to
remotetables, then one could create a view (using a custom on select rule) and use dblink right now!
What do you think?
Later
Rob
