Re: Win ODBC drivers for Postgres

From: "Sam O'Connor" <Sam(at)OConnor(dot)net>
To: Byron Nikolaidis <byron(dot)nikolaidis(at)home(dot)com>
Cc: pgsql-interfaces(at)postgresql(dot)org
Subject: Re: Win ODBC drivers for Postgres
Date: 2000-06-07 22:16:15
Message-ID: 393EC9AF.5D691A47@OConnor.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-interfaces

Having just ported my Postgres ODBC application to work with DB2,
MSSQL, Oracle and Informix I'm quite interested in the differences
between ODBC drivers. I've implemented a simple connection pooling
scheme on top of ODBC but it means that I'm not making use of the
more clever schemes built into the commercial drivers. The main
area where I would like to hand responsibility to the driver is
thread safety. I'd like to be able to open a connection in a thread
and have it behave like a private connection even if the driver
multiplexes it though an existing connection. In my current system
I have to handle putting mutexs around all the database access code
which is annoying. Thread safe connection pooling would be very
useful in PgODBC.

Does anyone have any experience or knowledge about how the Pg ODBC
drivers behave in a multithreaded application?

Another thing I've noticed is that the transaction handling is
somewhat broken in the PG ODBC driver.
I make a connection with auto commit turned off.
I make a statement with the connection.
I do a series of large object operations. (bind, insert, send data)
(Which must be in a single transaction to work.)
It doesn't work.

But if I execute some other query with the statement before
doing the lo stuff, or if I do an explicit BEGIN it works fine.
All my lo access code is now preceded by:
if (db_type == Db_type_PostgreSQL)
execute direct ("BEGIN")

Sam O'Connor

Byron Nikolaidis wrote:
>
> Hey there partner,
>
> To quote the immortal Butthead: "uhhhh....................., ok."
>
> Seriously, I wonder how this "consultant" came up with this? Sounds
> awefully generalized. I think I could say that line about anything,
> including ODBC itself.
>
> Also, many of the compromises of the ODBC driver are based on shortcomings
> of the backend. Jeez, we still "simulate" a Prepared Statement because
> there isn't one in the backend. Is the backend ever gonna have this?AFAIK,
> the JDBC driver simulates the prepared statement also.
>
> Some may remember the "declare/fetch" feature of the ODBC driver, that makes
> use of postgres cursors. This feature was added to help with the network
> bandwidth problem -- instead of getting the entire query result, it fetched
> it in chunks when required.
>
> Yeah, so maybe connection pooling would be nice, but most things use one or
> two connections don't they? Do most processes keep asking odbc for a new
> connection?
>
> Byron
>
> ----- Original Message -----
> From: Mihai Gheorghiu <tanhq(at)bigplanet(dot)com>
> To: <pgsql-interfaces(at)postgresql(dot)org>
> Sent: Wednesday, June 07, 2000 10:36 AM
> Subject: [INTERFACES] Win ODBC drivers for Postgres
>
> > The following is a quote from a consultant's opinion on my intention to
> > migrate to Postgresql.
> > Quote:
> > [ODBC public domain drivers] are immature, inefficient and are network
> > bandwidth hogs. The public domain drivers don't support connection caching
> > which reuses an existing database connection for same queries to reduce
> the
> > query result time.
> > Unquote.
> > Please comment.
> >
> > Best regards,
> >
> > Mihai
> >
> > PS See also pgsql-general for opinion on Postgres :-)
> >

In response to

Browse pgsql-interfaces by date

  From Date Subject
Next Message Peter Mount 2000-06-08 06:45:51 RE: Win ODBC drivers for Postgres
Previous Message Byron Nikolaidis 2000-06-07 21:30:43 Re: Win ODBC drivers for Postgres