Thread: Re: [GENERAL] One SQL to access two databases.
On Friday 29 November 2002 17:14, Joe Conway wrote: > pilsl@goldfisch.at wrote: > >>>Does anybody know if postgres support a SQL statement that handles two > >>>diferent databases (in the same server)? > >> > >>Have a look at the contrib/dblink directory in the standard postgresql > >>distro. > > > > Didnt find such a directory (or a similar one) in my 7.1.3-distri and > > this would be very helpful to save db-handles in persistent programs. > > dblink was first released with PostgreSQL 7.2. But as of yesterday, a "new > and improved" PostgreSQL 7.3 was released with a much improved dblink. If > you can, upgrade to 7.3. > > Joe > Hi Joe ! Now when the 7.3 release is out,can we get back to plpq ? I did send You sources before vacation, and You said that You will take a look.I hope I am not disturbing You. If You think that this is bad Idea, I give up hope that we merge this functions into dblink, an I will do it manually for my projects as I did before(I must say that this is a frustration for me because I must tweak the code with every new release of postgres).I am not using new plpq functions jet, so even if You donot want to merge, maybe You can give me some comments(as I said before, I do not understand memory management and memory contests to well) ? Thank You in advance. Regards !
Darko Prenosil wrote: > Now when the 7.3 release is out,can we get back to plpq ? > I did send You sources before vacation, and You said that You will take a > look. > I hope I am not disturbing You. If You think that this is bad Idea, I give up > hope that we merge this functions into dblink, an I will do it manually for > my projects as I did before(I must say that this is a frustration for me > because I must tweak the code with every new release of postgres). > I am not using new plpq functions jet, so even if You do not want to merge, > maybe You can give me some comments(as I said before, I do not understand > memory management and memory contests to well) ? > Thank You in advance. > I'm still interested in merging the plpq functions into dblink. As I said before, particularly now that plpgsql can returns sets, I think these functions are very useful. There are several other changes I'd like to make to dblink at the same time. I've recently been getting at least one email a week, off-list, from someone interested in using dblink against *other* RDBMSs (e.g. Oracle, Sybase, etc). Here's what I'm thinking about doing (in very loose terms -- comments, pointers, etc very much welcome): - split dblink into a set of front-end user accessible functions (e.g. dblink, dblink_exec, etc) and a loadable library of libpq based functions (a "connection library") that implement the front-end ones. The plpq functions would be part of the libpq connection library, with more generic front-end user functions. - use the libpq connection library as the model api for other types of connection libraries (JDBC, ODBC, oracle, freetds <sybase, mssql>, mysql, etc). - create an in-memory hash table of loaded connection libraries, and perhaps a table for registering the library paths, etc. - create an in memory hash table of persistent connections, and perhaps a table to register connections for reuse. As I said, this is all very preliminary; comments, suggestions, requests are all welcome. I'm not quite sure how to do the loadable library part, but I envision it being similar to how PLs are loaded when needed, and used when already loaded. Joe
On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: > As I said, this is all very preliminary; comments, suggestions, requests > are all welcome. Only idea/dream: what implement dblink as "virtual" schema. CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; SELECT * FROM myschema.tabname; This solution allows use dblink as really transparent. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/
On 2 Dec 2002 at 12:05, Karel Zak wrote: > On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: > > As I said, this is all very preliminary; comments, suggestions, requests > > are all welcome. > Only idea/dream: what implement dblink as "virtual" schema. > CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; > SELECT * FROM myschema.tabname; > This solution allows use dblink as really transparent. Well, that is an excellent solution but I want to suggest a further modification.. How would you select from a table in certain schema in remote database? i.e. does current implementation supports nested schemas? Like select * from remotedb.schemaa.tablea will work? If it does, this might be the best transparency we could ever get.. ByeShridhar -- pension: A federally insured chain letter.
Karel Zak wrote: > On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: > >>As I said, this is all very preliminary; comments, suggestions, requests >>are all welcome. > > Only idea/dream: what implement dblink as "virtual" schema. > > CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; > > SELECT * FROM myschema.tabname; > > This solution allows use dblink as really transparent. Yeah, something along these lines is in my long term vision, but I don't think it will happen for 7.4. I'd like one more contrib/dblink release for the code to mature, and to solidify the features and understand the common usage issues. Hopefully for the release *after* 7.4 I'll be ready to make a proposal to integrate dblink into the backend, get it accepted, and get it implemented. Joe
On Mon, Dec 02, 2002 at 08:56:41AM -0800, Joe Conway wrote: > Karel Zak wrote: > >On Sat, Nov 30, 2002 at 01:11:20PM -0800, Joe Conway wrote: > > > >>As I said, this is all very preliminary; comments, suggestions, requests > >>are all welcome. > > > > Only idea/dream: what implement dblink as "virtual" schema. > > > > CREATE SCHEMA myschema AS DBLINK TO ... some connection options ...; > > > > SELECT * FROM myschema.tabname; > > > > This solution allows use dblink as really transparent. > > Yeah, something along these lines is in my long term vision, but I don't > think it will happen for 7.4. I'd like one more contrib/dblink release for > the code to mature, and to solidify the features and understand the common > usage issues. Agree. This expect load a lot of information about remote tables to BE forcorrect planner & executor running. BTW, do youthink is possible loadthis information also from non-PostgreSQL servers (Oracle, DB2...)?The problem with multiple client-SQLlibs in BE is only a small part of transparent DBLINK imlementetion. > Hopefully for the release *after* 7.4 I'll be ready to make a proposal to > integrate dblink into the backend, get it accepted, and get it implemented. If you want to (a lot) use client library in backend it will need real andbetter memory managemnt for FE libs -- for examplesame mmgr as use BE. Karel -- Karel Zak <zakkr@zf.jcu.cz>http://home.zf.jcu.cz/~zakkr/