Re: unixODBC vs postgeSQL driver on RHEL4?

Good news -- I have achieved closure on this issue:

On Mon, 17 Oct 2005, Greg Martin wrote:
> I have utterly failed getting the postgreSQL ODBC driver working
> with unixODBC on Red Hat Enterprise Linux 4 for Intel (actually CentOS4
> but it's supposed to be the same thing) using the standard RPM packages.
> However, the driver provided with the unixODBC package works fine.
> I have tried lots of different tweaks in the config files and have concluded
> I must be missing something basic.
> I would simply shrug and use the unixODBC-provided driver, except
> that I'd like to use the most current ODBC driver and my understanding
> is the unixODBC-provided driver is somewhat out of date. Plus it's
> pretty damn annoying that I can't get it to work at all with the
> PostgreSQL-provided driver.

STEP 1: After playing around with RHEL for a while I decided to disable
SEL (in /etc/selinux/config) -- this wasn't responsible for my problem,
but I was able to get a much better understanding of what was going
on when I turned SEL off. (NOTE: I'm not ready to administer SEL on a
complex real-world production system yet; lots of learning still needed)

STEP 2: The key piece of information I was missing was that the
unixODBC-provided driver works with Unix-domain sockets, while the
PostgreSQL-provided driver works with TCP/IP sockets! RHEL / CentOS 4
comes with PostgreSQL TCP/IP socket access turned off, so that's why
the unixODBC-provided driver worked while the PostgreSQL-provided
driver didn't.

SOLUTION: Turn on TCP/IP socket access:
* edit /var/lib/pgsql/data/postgresql.conf and add for TCP/IP access:
tcpip_socket = true
* edit /var/lib/pgsql/data/pg_hba.conf and add for TCP/IP access:
host all all 127.0.0.1 255.255.255.0 trust
host all all 0.0.0.0 255.255.255.255 reject

I should mention for completeness that I went on to do further testing
of the PostgreSQL-provided ODBC driver and found it deficient.
1) It's stuck at ODBC version 2.5, which is problematic for applications
that generate ODBC 3.0 function calls.
2) The driver still has serious bugs, for example I can do:
SELECT COUNT(*) FROM some_table;
and occasionally the result comes back with DataType = varchar
meaning my attempt to read it into a long will fail (I'm using OTL
to generate my ODBC calls).

This last problem was serious enough that the only viable option for me
(since I don't want to deviate too far from the RHEL4 / CentOS 4 base
install) was to purchase the OpenLink ODBC driver. I have only just
started using it, but so far it seems a clear cut above the others.

Anyway, thanks to everyone for working on PostgreSQL, and I'm sure the
PostgreSQL ODBC driver will catch up to OpenLink in a couple years.

Greg Martin
gamartin(at)shout(dot)net