dblink_ora - a first shot on Oracle ...

Hi there ...

I have spent some time working on an Oracle version of dblink. It works
quite nicely for me and I hope it does for others.

It already supports some basic features such as persistent connection
and fetching data. This is not a perfect piece of software and there is
lot of room for enhancing this stuff.
If there is somebody out there who is interesting in this kind of stuff
I would be glad.
Maybe I will have some time in the next few days so that I can provide
an interface for flat files and some other database such as Berkley DB
as well. Maybe there will also be a version for MySQL but this one will
be used for MIGRATION purposes only. In other words: I won't touch MySQL
- just for migration and to get rid of it.

Personal thanks to Joe Conway, most of the code has been stolen from him.

Here is what you can do with the Oracle version:

SELECT dblink_oraconnect('scott/tiger(at)dbname');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'BEGIN', dblink_oraexec('BEGIN');
SELECT 'UPDATE emp SET sal = sal - 1',
dblink_oraexec('UPDATE emp SET sal = sal - 1');
SELECT 'ROLLBACK', dblink_oraexec('ROLLBACK');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'BEGIN', dblink_oraexec('BEGIN');
SELECT 'UPDATE emp SET sal = sal + 1',
dblink_oraexec('UPDATE emp SET sal = sal + 1');
SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
AS (ename text, sal text);
SELECT 'UPDATE emp SET sal = sal - 1',
dblink_oraexec('UPDATE emp SET sal = sal - 1');
SELECT 'COMMIT', dblink_oraexec('COMMIT');
SELECT dblink_oradisconnect();

[hs(at)sabrina dblink_ora]$ psql test < func.sql
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
DROP FUNCTION
CREATE FUNCTION
dblink_oraconnect
-------------------
OK
(1 row)

NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
--------+------
SMITH | 798
ALLEN | 1598
WARD | 1248
JONES | 2973
MARTIN | 1248
BLAKE | 2848
CLARK | 2448
SCOTT | 2998
KING | 4998
TURNER | 1498
ADAMS | 1098
JAMES | 948
FORD | 2998
MILLER | 1298
(14 rows)

NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal - 1 | 14
(1 row)

NOTICE: Affected: 0
?column? | dblink_oraexec
----------+----------------
ROLLBACK | 0
(1 row)

NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
--------+------
SMITH | 798
ALLEN | 1598
WARD | 1248
JONES | 2973
MARTIN | 1248
BLAKE | 2848
CLARK | 2448
SCOTT | 2998
KING | 4998
TURNER | 1498
ADAMS | 1098
JAMES | 948
FORD | 2998
MILLER | 1298
(14 rows)

NOTICE: Affected: -1
ERROR: Cannot execute SQL statement
NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal + 1 | 14
(1 row)

NOTICE: SQL statement successful
NOTICE: Found 2 columns
ename | sal
--------+------
SMITH | 799
ALLEN | 1599
WARD | 1249
JONES | 2974
MARTIN | 1249
BLAKE | 2849
CLARK | 2449
SCOTT | 2999
KING | 4999
TURNER | 1499
ADAMS | 1099
JAMES | 949
FORD | 2999
MILLER | 1299
(14 rows)

NOTICE: Affected: 14
?column? | dblink_oraexec
------------------------------+----------------
UPDATE emp SET sal = sal - 1 | 14
(1 row)

NOTICE: Affected: 0
?column? | dblink_oraexec
----------+----------------
COMMIT | 0
(1 row)

dblink_oradisconnect
----------------------
OK
(1 row)

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

This seems like a natural addition to our existing dblink in /contrib.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> Hi there ...
>
> I have spent some time working on an Oracle version of dblink. It works
> quite nicely for me and I hope it does for others.
>
> It already supports some basic features such as persistent connection
> and fetching data. This is not a perfect piece of software and there is
> lot of room for enhancing this stuff.
> If there is somebody out there who is interesting in this kind of stuff
> I would be glad.
> Maybe I will have some time in the next few days so that I can provide
> an interface for flat files and some other database such as Berkley DB
> as well. Maybe there will also be a version for MySQL but this one will
> be used for MIGRATION purposes only. In other words: I won't touch MySQL
> - just for migration and to get rid of it.
>
> Personal thanks to Joe Conway, most of the code has been stolen from him.
>
> Here is what you can do with the Oracle version:
>
>
> SELECT dblink_oraconnect('scott/tiger(at)dbname');
> SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
> AS (ename text, sal text);
> SELECT 'BEGIN', dblink_oraexec('BEGIN');
> SELECT 'UPDATE emp SET sal = sal - 1',
> dblink_oraexec('UPDATE emp SET sal = sal - 1');
> SELECT 'ROLLBACK', dblink_oraexec('ROLLBACK');
> SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
> AS (ename text, sal text);
> SELECT 'BEGIN', dblink_oraexec('BEGIN');
> SELECT 'UPDATE emp SET sal = sal + 1',
> dblink_oraexec('UPDATE emp SET sal = sal + 1');
> SELECT * FROM dblink_ora('SELECT ename, sal FROM emp')
> AS (ename text, sal text);
> SELECT 'UPDATE emp SET sal = sal - 1',
> dblink_oraexec('UPDATE emp SET sal = sal - 1');
> SELECT 'COMMIT', dblink_oraexec('COMMIT');
> SELECT dblink_oradisconnect();
>
>
>
> [hs(at)sabrina dblink_ora]$ psql test < func.sql
> DROP FUNCTION
> CREATE FUNCTION
> DROP FUNCTION
> CREATE FUNCTION
> DROP FUNCTION
> CREATE FUNCTION
> DROP FUNCTION
> CREATE FUNCTION
> DROP FUNCTION
> CREATE FUNCTION
> dblink_oraconnect
> -------------------
> OK
> (1 row)
>
> NOTICE: SQL statement successful
> NOTICE: Found 2 columns
> ename | sal
> --------+------
> SMITH | 798
> ALLEN | 1598
> WARD | 1248
> JONES | 2973
> MARTIN | 1248
> BLAKE | 2848
> CLARK | 2448
> SCOTT | 2998
> KING | 4998
> TURNER | 1498
> ADAMS | 1098
> JAMES | 948
> FORD | 2998
> MILLER | 1298
> (14 rows)
>
> NOTICE: Affected: -1
> ERROR: Cannot execute SQL statement
> NOTICE: Affected: 14
> ?column? | dblink_oraexec
> ------------------------------+----------------
> UPDATE emp SET sal = sal - 1 | 14
> (1 row)
>
> NOTICE: Affected: 0
> ?column? | dblink_oraexec
> ----------+----------------
> ROLLBACK | 0
> (1 row)
>
> NOTICE: SQL statement successful
> NOTICE: Found 2 columns
> ename | sal
> --------+------
> SMITH | 798
> ALLEN | 1598
> WARD | 1248
> JONES | 2973
> MARTIN | 1248
> BLAKE | 2848
> CLARK | 2448
> SCOTT | 2998
> KING | 4998
> TURNER | 1498
> ADAMS | 1098
> JAMES | 948
> FORD | 2998
> MILLER | 1298
> (14 rows)
>
> NOTICE: Affected: -1
> ERROR: Cannot execute SQL statement
> NOTICE: Affected: 14
> ?column? | dblink_oraexec
> ------------------------------+----------------
> UPDATE emp SET sal = sal + 1 | 14
> (1 row)
>
> NOTICE: SQL statement successful
> NOTICE: Found 2 columns
> ename | sal
> --------+------
> SMITH | 799
> ALLEN | 1599
> WARD | 1249
> JONES | 2974
> MARTIN | 1249
> BLAKE | 2849
> CLARK | 2449
> SCOTT | 2999
> KING | 4999
> TURNER | 1499
> ADAMS | 1099
> JAMES | 949
> FORD | 2999
> MILLER | 1299
> (14 rows)
>
> NOTICE: Affected: 14
> ?column? | dblink_oraexec
> ------------------------------+----------------
> UPDATE emp SET sal = sal - 1 | 14
> (1 row)
>
> NOTICE: Affected: 0
> ?column? | dblink_oraexec
> ----------+----------------
> COMMIT | 0
> (1 row)
>
> dblink_oradisconnect
> ----------------------
> OK
> (1 row)
>
>
> Regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
> Tel: +43/2952/30706; +43/664/233 90 75
> www.cybertec.at, www.postgresql.at, kernel.cybertec.at
>

[ application/x-gzip is not supported, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> This seems like a natural addition to our existing dblink in /contrib.
>

Yeah. But we'd need to detect whether or not the Oracle client libs are
available. I'm not sure how to do that with the contrib build system.
And we'd need a fair amount of integration/reorganizing the existing
code. Also, I've got the same issues with integrating jdbclink.

I don't think I'll be able to get that done between now and July 1st.

Joe

OK, can you take ownership of it?

---------------------------------------------------------------------------

Joe Conway wrote:
> Bruce Momjian wrote:
> > This seems like a natural addition to our existing dblink in /contrib.
> >
>
> Yeah. But we'd need to detect whether or not the Oracle client libs are
> available. I'm not sure how to do that with the contrib build system.
> And we'd need a fair amount of integration/reorganizing the existing
> code. Also, I've got the same issues with integrating jdbclink.
>
> I don't think I'll be able to get that done between now and July 1st.
>
> Joe
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> OK, can you take ownership of it?
>

You mean a TODO entry? Sure, as long as Hans is OK with it.

Joe

Well, we have a patch, so we need someone to babysit it until it is
applied, or put it somewhere and reference it via TODO.

---------------------------------------------------------------------------

Joe Conway wrote:
> Bruce Momjian wrote:
> > OK, can you take ownership of it?
> >
>
> You mean a TODO entry? Sure, as long as Hans is OK with it.
>
> Joe
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Well, we have a patch, so we need someone to babysit it until it is
> applied, or put it somewhere and reference it via TODO.
>

OK -- either way is fine by me.

Joe

Joe Conway wrote:
> Bruce Momjian wrote:
>
>> OK, can you take ownership of it?
>>
>
> You mean a TODO entry? Sure, as long as Hans is OK with it.
>
> Joe

I am ok with it.
The only problem I have at the moment is that I don't know how to build
properly and to check for the libs needed by Oracle.

The entire code is built on a library by a German developer because the
OCI interface itself is more than just insane (you will need 100000000
lines of code to establish a connection to the server).

As soon as I have all I need it should not be a huge task to finish the
code.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Joe Conway <mail(at)joeconway(dot)com> writes:
> Yeah. But we'd need to detect whether or not the Oracle client libs are
> available. I'm not sure how to do that with the contrib build system.
> And we'd need a fair amount of integration/reorganizing the existing
> code. Also, I've got the same issues with integrating jdbclink.

> I don't think I'll be able to get that done between now and July 1st.

Seems reasonable to put integrating both of 'em as a TODO for 7.5.

I think you're clearly the guy who has to review those patches, so
if you don't have time right now, it's not gonna happen for 7.4.

regards, tom lane

Joe, I can do the configure detection of the Oracle library needed for
/contrib. I don't think we follow the beta freeze as much for /contrib
stuff, but this particular /contrib is more integrated into the main
system than most. If you want to merge it in during the next month, I
can do the configure work for you.

If I am wrong about the /contrib deadlines, someone please let me know.
As I remember, we allow /contrib authors to change things up to the end
because it is no an integral part of the system.

---------------------------------------------------------------------------

Joe Conway wrote:
> Bruce Momjian wrote:
> > This seems like a natural addition to our existing dblink in /contrib.
> >
>
> Yeah. But we'd need to detect whether or not the Oracle client libs are
> available. I'm not sure how to do that with the contrib build system.
> And we'd need a fair amount of integration/reorganizing the existing
> code. Also, I've got the same issues with integrating jdbclink.
>
> I don't think I'll be able to get that done between now and July 1st.
>
> Joe
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:
> Joe, I can do the configure detection of the Oracle library needed for
> /contrib. I don't think we follow the beta freeze as much for /contrib
> stuff, but this particular /contrib is more integrated into the main
> system than most. If you want to merge it in during the next month, I
> can do the configure work for you.
>
> If I am wrong about the /contrib deadlines, someone please let me know.
> As I remember, we allow /contrib authors to change things up to the end
> because it is no an integral part of the system.

Bruce,

Thanks a lot. I will integrate named connections as proposed by the most
recent version of dblink as soon as possible.
Thanks for doing the configure stuff. What we need is Oracle's OCI
interface and libsqlora (http://www.poitschke.de/libsqlora8/).

Also; I have done some work on an improved version of jdbclink but it
does not work yet (supports persistent connections and so forth).

Joe, you have told me some time ago that you are planning some sort of
connection table which tells dblink to establish some connections
automatically. Did you work on that?

I hope we can merge it in a useful way.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Hans-Jürgen Schönig wrote:
> Thanks a lot. I will integrate named connections as proposed by the most
> recent version of dblink as soon as possible.
> Thanks for doing the configure stuff. What we need is Oracle's OCI
> interface and libsqlora (http://www.poitschke.de/libsqlora8/).

I was thinking that we should be merging dblink_ora (and eventually
jdbclink) into dblink. If you wanted to start down that road, here are
my thoughts on how that should be done.

dblink functions can be roughly divided into three groups. I'll address
each one separately:

1) connection related

SQL interface:
--------------
1.a dblink_connect (text <conn_str>)
1.b dblink_connect (text <conn_name>, text <conn_str>)
1.c dblink_disconnect ()
1.d dblink_disconnect (text <conn_name>)

To these I would add:
1.e dblink_connect (text <name>, text <conn_str>, text <conn_type>)

Acceptable values for <conn_type> would be 'postgres', 'oracle', and
eventually 'jdbc', non-case-sensitive. New types could be added to
this list later if/when other connection types are developed.

1.a and 1.b would default to type 'postgres'. Hence the only way to
get a non-postgres connection would be to use a named persistent
connection, but I think that makes sense anyway.

Implementation:
---------------
There are actually only two underlying functions, dblink_connect()
and dblink_disconnect(). Both of these should be modified so that
the mode (i.e. unnamed_default, named_default, named_with_type) is
discovered based on the arguments, then the real work farmed out to
connection type specific functions. I'm not entirely sure how it
should be handled when, for instance, the oracle library is not found
by configure. I suppose in that case you can use #ifdef's to provide
stubs with appropriate error messages.

2) connection consumers

SQL interface:
--------------
2.a dblink_open (text <cursor_name>, text <sql>)
2.b dblink_fetch (text <cursor_name>, int <num>)
2.c dblink_close (text <cursor_name>)
2.d dblink (text <sql>)
2.e dblink_exec (text <sql>)
2.f dblink_open (text <conn_name>, text <cursor_name>, text <sql>)
2.g dblink_fetch (text <conn_name>, text <cursor_name>, int <num>)
2.h dblink_close (text <conn_name>, text <cursor_name>)
2.i dblink (text <conn_name_or_str>, text <sql>)
2.j dblink_exec (text <conn_name_or_str>, text <sql>)

2.a - 2.e all use the unnamed connection and therefore only apply
to connections of type 'postgres'

2.f - 2.h all use named connections

2.i and 2.j can use named connections or connection strings

The existing named connections functions can be made to work with
<conn_type> == 'oracle', etc. It probably makes sense to add two
functions:
2.k dblink (text <conn_str>, text <conn_type>, text <sql>)
2.l dblink_exec (text <conn_str>, text <conn_type>, text <sql>)
This would allow dynamic specification of connections to
connection types other than 'postgres'.

Implementation:
---------------
Similar to the above, there is only one underlying function for each
SQL function name. These should be modified in a similar manner as
the connection functions.

3) miscellaneous utility functions

SQL interface:
--------------
3.a dblink_get_pkey
3.b dblink_build_sql_insert
3.c dblink_build_sql_delete
3.d dblink_build_sql_update
3.e dblink_current_query

These functions don't use libpq, they use SPI or other internals.
I wouldn't try to support other types of databases for them.

> Joe, you have told me some time ago that you are planning some sort of
> connection table which tells dblink to establish some connections
> automatically. Did you work on that?
No, I wouldn't worry too much about that right now.

Comments?

Joe

One reason I am excited about an Oracle-enabled dblink is that it gives
us a seamless way for PostgreSQL to operate in an evironment with
multiple database products, which I think is important.

As far as the Oracle libraries, once you have an Oracle-enabled patch in
CVS, I will put a some value in Makefile.global to indicate whether the
Oracle libraries were found by configure. You can use any name you
wish. I also see you include postgres.h, so I can add a C-level define
if that helps you too, though the easier way would be for the dblink
Makfile to add a -DWITH_ORACLE to the dblink compile line.

I will wait for you guys to complete your work.

---------------------------------------------------------------------------

Joe Conway wrote:
> Hans-J?rgen Sch?nig wrote:
> > Thanks a lot. I will integrate named connections as proposed by the most
> > recent version of dblink as soon as possible.
> > Thanks for doing the configure stuff. What we need is Oracle's OCI
> > interface and libsqlora (http://www.poitschke.de/libsqlora8/).
>
> I was thinking that we should be merging dblink_ora (and eventually
> jdbclink) into dblink. If you wanted to start down that road, here are
> my thoughts on how that should be done.
>
> dblink functions can be roughly divided into three groups. I'll address
> each one separately:
>
> 1) connection related
>
> SQL interface:
> --------------
> 1.a dblink_connect (text <conn_str>)
> 1.b dblink_connect (text <conn_name>, text <conn_str>)
> 1.c dblink_disconnect ()
> 1.d dblink_disconnect (text <conn_name>)
>
> To these I would add:
> 1.e dblink_connect (text <name>, text <conn_str>, text <conn_type>)
>
> Acceptable values for <conn_type> would be 'postgres', 'oracle', and
> eventually 'jdbc', non-case-sensitive. New types could be added to
> this list later if/when other connection types are developed.
>
> 1.a and 1.b would default to type 'postgres'. Hence the only way to
> get a non-postgres connection would be to use a named persistent
> connection, but I think that makes sense anyway.
>
> Implementation:
> ---------------
> There are actually only two underlying functions, dblink_connect()
> and dblink_disconnect(). Both of these should be modified so that
> the mode (i.e. unnamed_default, named_default, named_with_type) is
> discovered based on the arguments, then the real work farmed out to
> connection type specific functions. I'm not entirely sure how it
> should be handled when, for instance, the oracle library is not found
> by configure. I suppose in that case you can use #ifdef's to provide
> stubs with appropriate error messages.
>
> 2) connection consumers
>
> SQL interface:
> --------------
> 2.a dblink_open (text <cursor_name>, text <sql>)
> 2.b dblink_fetch (text <cursor_name>, int <num>)
> 2.c dblink_close (text <cursor_name>)
> 2.d dblink (text <sql>)
> 2.e dblink_exec (text <sql>)
> 2.f dblink_open (text <conn_name>, text <cursor_name>, text <sql>)
> 2.g dblink_fetch (text <conn_name>, text <cursor_name>, int <num>)
> 2.h dblink_close (text <conn_name>, text <cursor_name>)
> 2.i dblink (text <conn_name_or_str>, text <sql>)
> 2.j dblink_exec (text <conn_name_or_str>, text <sql>)
>
> 2.a - 2.e all use the unnamed connection and therefore only apply
> to connections of type 'postgres'
>
> 2.f - 2.h all use named connections
>
> 2.i and 2.j can use named connections or connection strings
>
> The existing named connections functions can be made to work with
> <conn_type> == 'oracle', etc. It probably makes sense to add two
> functions:
> 2.k dblink (text <conn_str>, text <conn_type>, text <sql>)
> 2.l dblink_exec (text <conn_str>, text <conn_type>, text <sql>)
> This would allow dynamic specification of connections to
> connection types other than 'postgres'.
>
> Implementation:
> ---------------
> Similar to the above, there is only one underlying function for each
> SQL function name. These should be modified in a similar manner as
> the connection functions.
>
> 3) miscellaneous utility functions
>
> SQL interface:
> --------------
> 3.a dblink_get_pkey
> 3.b dblink_build_sql_insert
> 3.c dblink_build_sql_delete
> 3.d dblink_build_sql_update
> 3.e dblink_current_query
>
> These functions don't use libpq, they use SPI or other internals.
> I wouldn't try to support other types of databases for them.
>
>
> > Joe, you have told me some time ago that you are planning some sort of
> > connection table which tells dblink to establish some connections
> > automatically. Did you work on that?
> No, I wouldn't worry too much about that right now.
>
> Comments?
>
> Joe
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian writes:

> Joe, I can do the configure detection of the Oracle library needed for
> /contrib.

I have a philosophical problem with putting Oracle detection code into the
PostgreSQL build system. That way, you create a dependency that Oracle
needs to be installed before you install PostgreSQL, but of course we want
PostgreSQL to be first. Consider where this will be going. Soon,
configure will be full with detection code for half a dozen other database
systems. Perhaps a plug-in architecture for dblink is a better solution.
Or make dblink a separate project.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Of course, PostgreSQL will still install without the Oracle libraries.

Of course, if you want dblink to use Oracle libraries, you have to
install the Oracle libraries first, or rerun configure after you install
them and reinstall dblink.

I don't see the problem.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Bruce Momjian writes:
>
> > Joe, I can do the configure detection of the Oracle library needed for
> > /contrib.
>
> I have a philosophical problem with putting Oracle detection code into the
> PostgreSQL build system. That way, you create a dependency that Oracle
> needs to be installed before you install PostgreSQL, but of course we want
> PostgreSQL to be first. Consider where this will be going. Soon,
> configure will be full with detection code for half a dozen other database
> systems. Perhaps a plug-in architecture for dblink is a better solution.
> Or make dblink a separate project.
>
> --
> Peter Eisentraut peter_e(at)gmx(dot)net
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> I don't see the problem.

How about a (simple!) configure process in the dblink directory only
which detects the various items.

Rod Taylor wrote:
-- Start of PGP signed section.
> > I don't see the problem.
>
> How about a (simple!) configure process in the dblink directory only
> which detects the various items.

I thought of that but configure seems so confusing that setting up
another on in a contrib directory seemed pretty hard.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I don't see the problem.

I tend to agree with Peter: if dblink is going to start depending on
stuff outside Postgres, it ought to be become a separate project,
if only to simplify distribution and configuration issues.

Perhaps it could be split into two parts, a PG-specific part and
a cross-DBMS part?

regards, tom lane

PS: Has anyone looked any further at the SQL-MED standard? ISTM that's
where we ought to head in the long run.

Tom Lane wrote:
> I tend to agree with Peter: if dblink is going to start depending on
> stuff outside Postgres, it ought to be become a separate project,
> if only to simplify distribution and configuration issues.
>
> Perhaps it could be split into two parts, a PG-specific part and
> a cross-DBMS part?
>
> regards, tom lane
>
> PS: Has anyone looked any further at the SQL-MED standard? ISTM that's
> where we ought to head in the long run.

I think for that very reason (SQL-MED) we need to come to terms with
this issue. If/when connections to external data sources is in the
backend, you'll have those exact same dependencies. And in fact, we do
today: consider '--with-openssl' or '--with-tcl'.

I had always assumed we would need '--with-oracle', '--with-jdbc', etc
(or whatever you want to call them) to support backend connections to
external sources. And this discussion is the very reason I was hesitant
to pursue dblink_ora or jdbclink now, because I didn't think people
would be comfortable with configure options to support a contrib library.

Joe

Joe Conway wrote:
> > PS: Has anyone looked any further at the SQL-MED standard? ISTM that's
> > where we ought to head in the long run.
>
> I think for that very reason (SQL-MED) we need to come to terms with
> this issue. If/when connections to external data sources is in the
> backend, you'll have those exact same dependencies. And in fact, we do
> today: consider '--with-openssl' or '--with-tcl'.
>
> I had always assumed we would need '--with-oracle', '--with-jdbc', etc
> (or whatever you want to call them) to support backend connections to
> external sources. And this discussion is the very reason I was hesitant
> to pursue dblink_ora or jdbclink now, because I didn't think people
> would be comfortable with configure options to support a contrib library.

I know we normally require a configure flag to look for special
capabilities, like ssl, but I thought we could skip that because it was
a /contrib and just look by default, but I now see that people don't
want to take that step.

I thought dblink was too integrated in the backend code to be a separate
project.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Joe Conway writes:

> I think for that very reason (SQL-MED) we need to come to terms with
> this issue. If/when connections to external data sources is in the
> backend, you'll have those exact same dependencies.

No, SQL-MED is a framework to plug in different connectors -- exactly what
some are suggesting for dblink.

--
Peter Eisentraut peter_e(at)gmx(dot)net

On 7/21/2003 9:16 AM, Tom Lane wrote:

>Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
>
>
>>I don't see the problem.
>>
>>
>
>I tend to agree with Peter: if dblink is going to start depending on
>stuff outside Postgres, it ought to be become a separate project,
>if only to simplify distribution and configuration issues.
>
The ability to optionally link to another library does not necessitate a
functional dependency on it.

>Perhaps it could be split into two parts, a PG-specific part and
>a cross-DBMS part?
>
> regards, tom lane
>
>PS: Has anyone looked any further at the SQL-MED standard? ISTM that's
>where we ought to head in the long run.
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>

Hans, I am a little confused about what you are suggesting. Are you
suggesting flag to the make of the contrib module rather than configure
tests?

I agree this is a killer feature for many people and would like to have
it in 7.4.

---------------------------------------------------------------------------

Hans-Jrgen Schnig wrote:
> > I think for that very reason (SQL-MED) we need to come to terms with
> > this issue. If/when connections to external data sources is in the
> > backend, you'll have those exact same dependencies. And in fact, we do
> > today: consider '--with-openssl' or '--with-tcl'.
> >
> > I had always assumed we would need '--with-oracle', '--with-jdbc', etc
> > (or whatever you want to call them) to support backend connections to
> > external sources. And this discussion is the very reason I was hesitant
> > to pursue dblink_ora or jdbclink now, because I didn't think people
> > would be comfortable with configure options to support a contrib library.
> >
> > Joe
>
> If dblink was a core module I would say that adding the configure stuff
> would be very natural. Since this is contrib stuff I was not that sure
> about configure anymore. We will need additional flags for external data
> sources in the (hopefully) near future so I think we should add it.
>
> Personally I tend to think about a solution like that. dblink has a
> great future and many people simply love it (I cannot think of a
> customer who does not like it - it is a killer feature):
>
> - implement the concepts proposed by Joe on this list yesterday (I am
> talking about the functions dblink should provide)
> - add support to configure
> - merge dblink with dblink_ora as soon as the changes are ready
> - adapt jdbc_link and merge it with dblink
> - implement dblink_db2, dblink_csv, dblink_xml, and maybe some others
> - leave it in contrib because this way it will be shipped with the core
> distribution and people will use it more frequently
>
> I hope that I will finish the Oracle stuff (named connections, ...)
> within the next 3 days.
>
> Regards,
>
> Hans
>
>
> --
> Cybertec Geschwinde u Schoenig
> Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
> Tel: +43/2952/30706; +43/664/233 90 75
> www.cybertec.at, www.postgresql.at, kernel.cybertec.at
>
>
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> I think for that very reason (SQL-MED) we need to come to terms with
> this issue. If/when connections to external data sources is in the
> backend, you'll have those exact same dependencies. And in fact, we do
> today: consider '--with-openssl' or '--with-tcl'.
>
> I had always assumed we would need '--with-oracle', '--with-jdbc', etc
> (or whatever you want to call them) to support backend connections to
> external sources. And this discussion is the very reason I was hesitant
> to pursue dblink_ora or jdbclink now, because I didn't think people
> would be comfortable with configure options to support a contrib library.
>
> Joe

If dblink was a core module I would say that adding the configure stuff
would be very natural. Since this is contrib stuff I was not that sure
about configure anymore. We will need additional flags for external data
sources in the (hopefully) near future so I think we should add it.

Personally I tend to think about a solution like that. dblink has a
great future and many people simply love it (I cannot think of a
customer who does not like it - it is a killer feature):

- implement the concepts proposed by Joe on this list yesterday (I am
talking about the functions dblink should provide)
- add support to configure
- merge dblink with dblink_ora as soon as the changes are ready
- adapt jdbc_link and merge it with dblink
- implement dblink_db2, dblink_csv, dblink_xml, and maybe some others
- leave it in contrib because this way it will be shipped with the core
distribution and people will use it more frequently

I hope that I will finish the Oracle stuff (named connections, ...)
within the next 3 days.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Bruce Momjian wrote:
> Hans, I am a little confused about what you are suggesting. Are you
> suggesting flag to the make of the contrib module rather than configure
> tests?
>
> I agree this is a killer feature for many people and would like to have
> it in 7.4.
>

Under these circumstances I was thinking of integrating it into the main
configuration mechanism - not just for contrib.
We will need it for cross db suff later on anyway.

Sorry for the confusion.

Regards,

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at

Hans, Bruce,

We're drafting the press release for 7.4 right now. What's the odds that
dblink_ora will be ready in time for 7.4?

--
Josh Berkus
Aglio Database Solutions
San Francisco

Josh Berkus wrote:
> Hans, Bruce,
>
> We're drafting the press release for 7.4 right now. What's the odds that
> dblink_ora will be ready in time for 7.4?

There seems to be some disagreement on whether the Oracle lib checks
should be in configure for a /contrib module, and I don't know how far
Hans is. I will say we are probably looking at 7/28 for beta.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> There seems to be some disagreement on whether the Oracle lib checks
> should be in configure for a /contrib module, and I don't know how far
> Hans is. I will say we are probably looking at 7/28 for beta.

I am afraid I won't make it until 7.4beta1.
The problem is that I have not managed to have more than just one
connection to oracle at the same time. For some Oracle reason I don't
know what went wrong. I will have a closer look at that.

However, named connections don't make too much sense with just one
connection ;). I have troubles testing it as long as the connect fails ...
There is some other Oracle specific stuff which seems to be more painful
than I have expected.
Have I already told you that I hate Oracle?

Let's wait for the next major release and prepare something really
useful. I am still not quite satisfied with what we have at the moment.

Hans

--
Cybertec Geschwinde u Schoenig
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/2952/30706; +43/664/233 90 75
www.cybertec.at, www.postgresql.at, kernel.cybertec.at