dblink_ora - a first shot on Oracle ...

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

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

Hans-J���rgen Sch���nig 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@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@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@candle.pha.pa.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@candle.pha.pa.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@candle.pha.pa.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@joeconway.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@candle.pha.pa.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@candle.pha.pa.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@gmx.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@gmx.net

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