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.

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

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

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 !

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..

Bye
Shridhar

--
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 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/

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.