Horton

Thread

  1. dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <postgres@cybertec.at> — 2003-06-13T19:58:07Z

    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

  2. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-23T17:34:27Z

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

  3. Re: dblink_ora - a first shot on Oracle ...

    Joe Conway <mail@joeconway.com> — 2003-06-24T04:36:54Z

    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

  4. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-24T04:41:20Z

    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

  5. Re: dblink_ora - a first shot on Oracle ...

    Joe Conway <mail@joeconway.com> — 2003-06-24T04:42:59Z

    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

  6. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-06-24T04:47:49Z

    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

  7. Re: dblink_ora - a first shot on Oracle ...

    Joe Conway <mail@joeconway.com> — 2003-06-24T04:49:46Z

    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

  8. Re: dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <hs@cybertec.at> — 2003-06-24T06:24:52Z

    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

  9. Re: dblink_ora - a first shot on Oracle ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-06-24T13:56:34Z

    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

  10. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-19T01:19:55Z

    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

  11. Re: dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <hs@cybertec.at> — 2003-07-19T10:30:31Z

    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

  12. Re: dblink_ora - a first shot on Oracle ...

    Joe Conway <mail@joeconway.com> — 2003-07-19T18:36:52Z

    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

  13. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-19T22:29:01Z

    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

  14. Re: dblink_ora - a first shot on Oracle ...

    Peter Eisentraut <peter_e@gmx.net> — 2003-07-21T07:45:43Z

    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

  15. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-21T13:39:29Z

    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

  16. Re: dblink_ora - a first shot on Oracle ...

    Rod Taylor <rbt@rbt.ca> — 2003-07-21T13:47:51Z

    > I don't see the problem.

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

  17. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-21T14:01:11Z

    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

  18. Re: dblink_ora - a first shot on Oracle ...

    Tom Lane <tgl@sss.pgh.pa.us> — 2003-07-21T14:16:33Z

    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.

  19. Re: dblink_ora - a first shot on Oracle ...

    Joe Conway <mail@joeconway.com> — 2003-07-21T14:48:22Z

    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

  20. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-21T15:09:31Z

    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

  21. Re: dblink_ora - a first shot on Oracle ...

    Peter Eisentraut <peter_e@gmx.net> — 2003-07-21T15:21:58Z

    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@gmx.net

  22. Re: dblink_ora - a first shot on Oracle ...

    Thomas Swan <tswan@idigx.com> — 2003-07-21T15:26:29Z

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

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

  23. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-21T16:13:39Z

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

  24. Re: dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <hs@cybertec.at> — 2003-07-21T16:15:14Z

    > 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

  25. Re: dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <hs@cybertec.at> — 2003-07-22T05:34:20Z

    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

  26. Re: dblink_ora - a first shot on Oracle ...

    Josh Berkus <josh@agliodbs.com> — 2003-07-22T16:28:36Z

    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

  27. Re: dblink_ora - a first shot on Oracle ...

    Bruce Momjian <pgman@candle.pha.pa.us> — 2003-07-22T16:57:48Z

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

  28. Re: dblink_ora - a first shot on Oracle ...

    Hans-Jürgen Schönig <hs@cybertec.at> — 2003-07-25T08:49:48Z

    > 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