Subject: Re: [SQL] PostgreSQL to Oracle
From: Reece Hart <reece@harts.net>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: pgsql-general@postgresql.org, "Jonah H. Harris" <jonah.harris@gmail.com>,
	Ezequias Rodrigues da Rocha <ezequias.rocha@gmail.com>
In-Reply-To: <200703151620.34309.xzilla@users.sourceforge.net>
References: <55c095e90703090954m74e2b06arf73e3037f25f5c18@mail.gmail.com>
	<36e682920703091032u22cd9901w6212c9aba999ec8c@mail.gmail.com>
	<200703151620.34309.xzilla@users.sourceforge.net>
Content-Type: text/plain
Date: Fri, 16 Mar 2007 10:18:56 -0700
Message-Id: <1174065536.5244.22.camel@snafu.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2007-03-15 at 16:20 -0400, Robert Treat wrote:
> Do you find [HSODBC] works well?  I've used it from some older Oracle
> instances connecting back into PostgreSQL and the results I had have
> been flakey at best.

For us too. We tried in early 2006 with Oracle 8 via hsodbc to
postgresql 7.4.6 and had several problems. From what I remember:

- Oracle complained about SQLError when using unixODBC 3.0. This
necessitated using 2.5. 
http://www.ianywhere.com/developer/product_manuals/sqlanywhere/0902/en/html/dbpgen9/00000291.htm

- We had type conversion problems. I can't remember many details of
this, but I *think* they may have been text/varchar and timestamp/date.
We don't use blobs.

- It took our admins a fair bit of time to get working. It probably
didn't help that Oracle was running on AIX.

- The real nail in the coffin seemed to be that there was no way to push
predicates to the remote side. This mean that a remote relation was
materialized locally in order to join with local data and that we'd
never get reasonable performance for cross-database joins (which,
perhaps, it's not designed to do).


I'm particularly curious about this last problem.  Without pushing
predicates to the remote side, it's hard for me to imagine that HSODBC
would be terribly useful for anything but building a local warehouse.
This seems so odd that I think I must be wrong, but I couldn't find any
documentation that to support or discourage cross-database joins.  Can
someone out there enlighten me?  What's the intent for HSODBC and what
are its limitations?  Can one access remote server side functions via
HSODBC (eg, sqlplus> select col1,plpgsqlfx(col1) from remotetable)?


Thanks,
Reece

-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.