Re: Any Oracle HSODBC and PostgreSQL experience out there?

sfpuggers-

We're trying to make a postgresql db available within oracle using
hsodbc and dblinks. We're using oracle 9.2.0.5 on AIX, connecting to
postgresql 8.1.3 on x86_64-linux, using unixODBC and pgodbc (several
versions tried).

Has anyone out there made HSODBC work with PostgreSQL? If so, I'd
appreciate a config summary and we'll keep trying.

Among the issues we saw was that where clauses seem to be evaluated
locally, not remotely. So, a query like
SELECT * FROM schema(dot)table(at)database WHERE id=10
fetches all rows, then applies the where clause in Oracle. That's so
obviously unacceptable for large tables that there must be a better way,
but I've not seen any mention of this in groups and the two folks who've
done most of the configuration aren't aware of any alternatives. I'm
new to ODBC, but I can't believe that this is the intended behavior.

Are we missing something?

Thanks,
Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Wed, Apr 12, 2006 at 04:22:32PM -0700, Reece Hart wrote:
> sfpuggers-
>
> We're trying to make a postgresql db available within oracle using
> hsodbc and dblinks. We're using oracle 9.2.0.5 on AIX, connecting to
> postgresql 8.1.3 on x86_64-linux, using unixODBC and pgodbc (several
> versions tried).
>
> Has anyone out there made HSODBC work with PostgreSQL? If so, I'd
> appreciate a config summary and we'll keep trying.
>
> Among the issues we saw was that where clauses seem to be evaluated
> locally, not remotely. So, a query like
> SELECT * FROM schema(dot)table(at)database WHERE id=10
> fetches all rows, then applies the where clause in Oracle. That's so
> obviously unacceptable for large tables that there must be a better way,
> but I've not seen any mention of this in groups and the two folks who've
> done most of the configuration aren't aware of any alternatives. I'm
> new to ODBC, but I can't believe that this is the intended behavior.

This is also a problem with DBI-Link. Passing around the where clause
is a difficult thing to do. PostgreSQL doesn't make it available to
pass through a gateway (yet?) so I'm not surprised the ODBC has the same
issue. Disclaimer: I know nothing about HSODBC, but a have a good
general knowledge of gateways.

>
> Are we missing something?
>
> Thanks,
> Reece
>
> --
> Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
>

Reece,

> We're trying to make a postgresql db available within oracle using
> hsodbc and dblinks. We're using oracle 9.2.0.5 on AIX, connecting to
> postgresql 8.1.3 on x86_64-linux, using unixODBC and pgodbc (several
> versions tried).
>
> Has anyone out there made HSODBC work with PostgreSQL? If so, I'd
> appreciate a config summary and we'll keep trying.

I think this is a problem with HSODBC and just about any gateway. You'd
have the same issues with MS Access or MS DTS.

You could hack this by extending DBI-Link, although the syntax will be
awkward ... currently RULES give us no way to grab the WHERE clause from
an incoming query. So you'd need to do something like:

SELECT * FROM select_oracle_table(ARRAY['col = value','col < value'])

or similar.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On Thu, Apr 13, 2006 at 04:03:53PM -0700, Josh Berkus wrote:
> Reece,
>
> > We're trying to make a postgresql db available within oracle using
> > hsodbc and dblinks. We're using oracle 9.2.0.5 on AIX, connecting to
> > postgresql 8.1.3 on x86_64-linux, using unixODBC and pgodbc (several
> > versions tried).
> >
> > Has anyone out there made HSODBC work with PostgreSQL? If so, I'd
> > appreciate a config summary and we'll keep trying.
>
> I think this is a problem with HSODBC and just about any gateway. You'd
> have the same issues with MS Access or MS DTS.
>
> You could hack this by extending DBI-Link, although the syntax will be
> awkward ... currently RULES give us no way to grab the WHERE clause from
> an incoming query. So you'd need to do something like:

I sent something out on this this morning, but it doesn't appear to
have gone through. DBI-Link has a facility called remote_query() that
lets you stuff a whole query over the wire. No automated
predicate-pushing just yet, but any help would be helpful. I suspect
that Major Back-End Engine Magick(TM) would be involved with automated
predicate-pushing.

Cheers,
D
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

Thanks Josh, Elein, David for replies.

On Thu, 2006-04-13 at 16:03 -0700, Josh Berkus wrote:

> You could hack this by extending DBI-Link, although the syntax will be
> awkward ... currently RULES give us no way to grab the WHERE clause
> from an incoming query. So you'd need to do something like:

Am I missing something? I'm trying to query a pg database from within
Oracle... DBI-Link doesn't help with going that direction, right?

I'm surprised at this outcome with HSODBC... I'm new to gateways but I
don't see how HSODBC is useful if you can't push predicates to the
remote side. I realize that this is is non-trivial, but in the
PostgreSQL world it seems like a Fetter Small Magic Plugin (TM) to a
cost-based optimizer might do the trick. Of course, that would not help
the poor sods like me who are occasionally trapped in Obfuscated,
Reprehensible, Arcane, and Crappy Legacy Environment.

-Reece

P.S. A colleague recently showed me
http://www.larsditzel.de/ora_arch.pdf and my response was simply "That's
why I use PostgreSQL".

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

On Tue, Apr 18, 2006 at 05:43:11PM -0700, Reece Hart wrote:
> Thanks Josh, Elein, David for replies.
>
> On Thu, 2006-04-13 at 16:03 -0700, Josh Berkus wrote:
>
> > You could hack this by extending DBI-Link, although the syntax
> > will be awkward ... currently RULES give us no way to grab the
> > WHERE clause from an incoming query. So you'd need to do
> > something like:
>
> Am I missing something? I'm trying to query a pg database from
> within Oracle... DBI-Link doesn't help with going that direction,
> right?

It might, and here's a sketch of one way.

Let's say you have a set of tables in PostgreSQL that you want visible
from Oracle.

* Duplicate (or translate--this could be a little tricky) the
appropriate parts of their DDL on the Oracle side. Now there are
two sets of tables: one in PostgreSQL, the copy in Oracle.

* Hook up the Oracle set via DBI-Link in the same database as your
regular tables.

* Make some triggers on each table similar to the ones you'd use for
materialized views. The triggers would repeat INSERTs, UPDATEs and
DELETEs to the DBI-Link schema inside PostgreSQL. Ass-u-me'ing that
you have primary keys on each of those tables, you could automate
the trigger (function) creation process via using the information
schema.

* Do a bunch of INSERT INTO ... SELECT... on the with your PostgreSQL
tables as source and the DBI-Link views as destination to get
started.

> I'm surprised at this outcome with HSODBC... I'm new to gateways but I
> don't see how HSODBC is useful if you can't push predicates to the
> remote side. I realize that this is is non-trivial, but in the
> PostgreSQL world it seems like a Fetter Small Magic Plugin (TM) to a
> cost-based optimizer might do the trick.

I wish I were that smart. :)

Cheers,
D (pushing predicates from the PA)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!

On Wed, 2006-04-19 at 06:53 -0700, David Fetter wrote:
> It might, and here's a sketch of one way...
[trigger+DBI link proposal snipped]

Hey, that's just crazy enough that it might work... perhaps we'll look
into that. Thanks for the tip!

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0