
Does anybody know if postgres support a SQL statement that handles two 
diferent databases (in the same server)?

-- 
Diogo de Oliveira Biazus
diogo@ikono.com.br
Ikono Sistemas e Automaï¿½ï¿½o
http://www.ikono.com.br



Wed, 27 Nov 2002 18:08:09 -0200
Diogo Biazus <diogo@ikono.com.br>
One SQL to access two databases.
On Wed, 27 Nov 2002, Diogo Biazus 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.


Wed, 27 Nov 2002 13:58:19 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: One SQL to access two databases.
> > 
> > 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.



thnx,
peter


-- 
mag. peter pilsl
IT-Consulting
tel: +43-699-1-3574035
fax: +43-699-4-3574035
pilsl@goldfisch.at
http://www.goldfisch.at

Fri, 29 Nov 2002 11:23:02 +0100
pilsl@goldfisch.at
Re: One SQL to access two databases.
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


Fri, 29 Nov 2002 09:14:07 -0800
Joe Conway <mail@joeconway.com>
Re: 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 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.

Regards !


Sat, 30 Nov 2002 12:19:27 +0000
Darko Prenosil <darko.prenosil@finteh.hr>
Re: [GENERAL] One SQL to access two databases.
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


Sat, 30 Nov 2002 13:11:20 -0800
Joe Conway <mail@joeconway.com>
Re: [GENERAL] One SQL to access two databases.
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/

Mon, 2 Dec 2002 12:05:25 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: [GENERAL] One SQL to access two databases.
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..

Bye
 Shridhar

--
pension:	A federally insured chain letter.


Mon, 02 Dec 2002 16:58:52 +0530
"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in>
Re: [GENERAL] One SQL to access two databases.
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


Mon, 02 Dec 2002 08:56:41 -0800
Joe Conway <mail@joeconway.com>
Re: [GENERAL] One SQL to access two databases.

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 for
 correct planner & executor running. BTW, do you think is possible load
 this information also from non-PostgreSQL servers (Oracle, DB2...)?
 
 The problem with multiple client-SQL libs 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 and
 better memory managemnt for FE libs -- for example same mmgr as use BE.

    Karel

-- 
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

Tue, 3 Dec 2002 10:13:21 +0100
Karel Zak <zakkr@zf.jcu.cz>
Re: [GENERAL] One SQL to access two databases.
On Fri, 29 Nov 2002 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.

Yeah, it sounds like you're running an older version of Postgresql.  Good 
news, 7.3 is out.  I'd suggest upgrading to it and then you should have 
the contrib/dblink directory.


Tue, 3 Dec 2002 10:01:17 -0700 (MST)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: One SQL to access two databases.