Thread: ODBC driver for Windows & future...

ODBC driver for Windows & future...

From
"Philippe Lang"
Date:
Hi,

I think I'm not the only one to get worried about the future of the
PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
now and then, but nobody seems to understand anymore how the driver
really works...

What most surprises me, is that there is not viable commercial
alternative to the open-source ODBC driver. All the commercial drivers I
have tested do not work at all... On the opposite, the PGSQL .NET driver
development seems to be quite active, so does that mean ODBC is dead?
I'm sure we are really a lot out here to be interested in a clean ODBC
driver for Windows. If it continues like this, Postgresql will end up
being a fantastic database, but cut out from the rest of the world.
Well, the ODBC world at least.

So, what next?

1. Does the driver have to be completely rewritten? I have heard of a
new 7.4 protocol that the actual driver does not support, and of libpq,
that could be used to access the database, but really, that's all I
know... Can these changes be incorporated in the actual driver?

2. Can anyone draw a skeleton of the how the "ideal" driver should be
architectured? I remember seeing quite a lot of volunteers a few months
ago, and I'm willing to do so myself, but "developing an ODBC driver" is
not something we do every morning before coffee...


Anyway, thanks to all of you that bring something to open-source.
Postgresql through ODBC is still a viable production technology, I
notice it every day. I'm just worring about the future...

----------------------------------
Philippe Lang
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel:   +41 (26) 422 13 75
Fax:   +41 (26) 422 13 76
GSM:   +41 (79) 351 49 94
Email: philippe.lang@attiksystem.ch
Skype: philippe.lang

Re: ODBC driver for Windows & future...

From
Matthew Schwartz
Date:
Philippe Lang wrote:
Hi,

I think I'm not the only one to get worried about the future of the
PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
now and then, but nobody seems to understand anymore how the driver
really works...

What most surprises me, is that there is not viable commercial
alternative to the open-source ODBC driver. All the commercial drivers I
have tested do not work at all... On the opposite, the PGSQL .NET driver
development seems to be quite active, so does that mean ODBC is dead?
I'm sure we are really a lot out here to be interested in a clean ODBC
driver for Windows. If it continues like this, Postgresql will end up
being a fantastic database, but cut out from the rest of the world.
Well, the ODBC world at least.

So, what next?

1. Does the driver have to be completely rewritten? I have heard of a
new 7.4 protocol that the actual driver does not support, and of libpq,
that could be used to access the database, but really, that's all I
know... Can these changes be incorporated in the actual driver?

2. Can anyone draw a skeleton of the how the "ideal" driver should be
architectured? I remember seeing quite a lot of volunteers a few months
ago, and I'm willing to do so myself, but "developing an ODBC driver" is
not something we do every morning before coffee...


Anyway, thanks to all of you that bring something to open-source.
Postgresql through ODBC is still a viable production technology, I
notice it every day. I'm just worring about the future...

----------------------------------
Philippe Lang
Attik System
rte de la Fonderie 2
1700 Fribourg
Switzerland
http://www.attiksystem.ch

Tel:   +41 (26) 422 13 75 
Fax:   +41 (26) 422 13 76
GSM:   +41 (79) 351 49 94
Email: philippe.lang@attiksystem.ch
Skype: philippe.lang

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster  
Hello

I am not at the level where I feel that I could even assist with writing an odbc driver, but would like to do what I can to help. I follow the odbc threads with interest as I have committed to postgres.

I use postgres on a daily basis and have proven it to be a robust, reliable system- it is a phenomenal product with great potential. As a user of versions 7.2 and 7.4, I know that there are certain problems that need fixing before the odbc driver is complete and enterprise ready.

Please, someone, help sort out this problem and make sure that odbc will be viable in the future.

--
___________________________________________________________
Matthew Schwartz

Citizen Surveys
South Africa
tel: 021 447 4484
fax: 021 448 6312
cell: 083 774 2420
email: matthew@citizensurveys.com










----------------------------------------------------------------
This email is intended only for the use of the individual or entity
named above and may contain information that is confidential and
privileged. If you are not the intended recipient, you are hereby
notified that any dissemination, distribution or copying of this email
is strictly prohibited. Opinions, conclusions and other information in
this message that do not relate to the official business of our firm
shall be understood as neither given nor endorsed by it.
----------------------------------------------------------------


Attachment

Re: ODBC driver for Windows & future...

From
Simeó Reig
Date:
>Hi,
>
>I think I'm not the only one to get worried about the future of the
>PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
>now and then, but nobody seems to understand anymore how the driver
>really works...
>
>What most surprises me, is that there is not viable commercial
>alternative to the open-source ODBC driver. All the commercial drivers I
>have tested do not work at all... On the opposite, the PGSQL .NET driver
>development seems to be quite active, so does that mean ODBC is dead?
>I'm sure we are really a lot out here to be interested in a clean ODBC
>driver for Windows. If it continues like this, Postgresql will end up
>being a fantastic database, but cut out from the rest of the world.
>Well, the ODBC world at least.

>So, what next?


we have been using the driver since three years ago, we had made an
application
with 40 concurrent ODBC users, 144 tables and milions of rows, postgresql is
fantastic, with a great performance (dell 2650 dual processor raid 10) and
the most important : zero problems in three years.

Now, we have to make other application but we will not use postgresql
because
we don't trust ODBC project, seems nobody understands nothing !! People make
questions and nobody answer, people offer his help and nobody answer, where
are
the mantainers of the project ?? I remember three years ago when Iroshi
leader the
project this things never had happened.

why iroshi leaves the project ?? what was the unpleased reason  ?


>1. Does the driver have to be completely rewritten? I have heard of a
>new 7.4 protocol that the actual driver does not support, and of libpq,
>that could be used to access the database, but really, that's all I
>know... Can these changes be incorporated in the actual driver?

speed,full ODBC 3.0 compliant, SSL, data compression,
full large object support ...and the most important : new people
with new ideas.

>2. Can anyone draw a skeleton of the how the "ideal" driver should be
>architectured? I remember seeing quite a lot of volunteers a few months
>ago, and I'm willing to do so myself, but "developing an ODBC driver" is
>not something we do every morning before coffee...

yes, it's a hard work

>Anyway, thanks to all of you that bring something to open-source.
>Postgresql through ODBC is still a viable production technology, I
>notice it every day. I'm just worring about the future...

Postgresql is one of the most greats databases but if you want to work
with an application that uses windows ODBC (visual basic, Fox Pro, asp,
asp.net ...) is turning on a "russian roulette".


Re: ODBC driver for Windows & future...

From
Johan Wehtje
Date:
I also think that a high performance ODBC driver will be critical to the
success of Postgresql 8.0, given that the advent of Native windows
support offers such a huge opportunity for Postgresql to show it's
mettle as a MS SQL Server killer. In particular what is the status of
the ODBC driver in relation to the many changes that have occurred
between 7.x and 8. ?

On Windows platforms ODBC will remain the default method to quickly link
a Database with any number of applications for some time to come.


Simeó Reig wrote:

>>Hi,
>>
>>I think I'm not the only one to get worried about the future of the
>>PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
>>now and then, but nobody seems to understand anymore how the driver
>>really works...
>>
>>What most surprises me, is that there is not viable commercial
>>alternative to the open-source ODBC driver. All the commercial drivers I
>>have tested do not work at all... On the opposite, the PGSQL .NET driver
>>development seems to be quite active, so does that mean ODBC is dead?
>>I'm sure we are really a lot out here to be interested in a clean ODBC
>>driver for Windows. If it continues like this, Postgresql will end up
>>being a fantastic database, but cut out from the rest of the world.
>>Well, the ODBC world at least.
>>
>>
>
>
>
>>So, what next?
>>
>>
>
>
>we have been using the driver since three years ago, we had made an
>application
>with 40 concurrent ODBC users, 144 tables and milions of rows, postgresql is
>fantastic, with a great performance (dell 2650 dual processor raid 10) and
>the most important : zero problems in three years.
>
>Now, we have to make other application but we will not use postgresql
>because
>we don't trust ODBC project, seems nobody understands nothing !! People make
>questions and nobody answer, people offer his help and nobody answer, where
>are
>the mantainers of the project ?? I remember three years ago when Iroshi
>leader the
>project this things never had happened.
>
>why iroshi leaves the project ?? what was the unpleased reason  ?
>
>
>
>
>>1. Does the driver have to be completely rewritten? I have heard of a
>>new 7.4 protocol that the actual driver does not support, and of libpq,
>>that could be used to access the database, but really, that's all I
>>know... Can these changes be incorporated in the actual driver?
>>
>>
>
>speed,full ODBC 3.0 compliant, SSL, data compression,
>full large object support ...and the most important : new people
>with new ideas.
>
>
>
>>2. Can anyone draw a skeleton of the how the "ideal" driver should be
>>architectured? I remember seeing quite a lot of volunteers a few months
>>ago, and I'm willing to do so myself, but "developing an ODBC driver" is
>>not something we do every morning before coffee...
>>
>>
>
>yes, it's a hard work
>
>
>
>>Anyway, thanks to all of you that bring something to open-source.
>>Postgresql through ODBC is still a viable production technology, I
>>notice it every day. I'm just worring about the future...
>>
>>
>
>Postgresql is one of the most greats databases but if you want to work
>with an application that uses windows ODBC (visual basic, Fox Pro, asp,
>asp.net ...) is turning on a "russian roulette".
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>.
>
>
>

Re: ODBC driver for Windows & future...

From
Richard Huxton
Date:
Johan Wehtje wrote:
> I also think that a high performance ODBC driver will be critical to
> the success of Postgresql 8.0, given that the advent of Native
> windows support offers such a huge opportunity for Postgresql to show
> it's mettle as a MS SQL Server killer. In particular what is the
> status of the ODBC driver in relation to the many changes that have
> occurred between 7.x and 8. ?

AFAIK no substantial changes have been made in the ODBC driver recently.
Dave generously stepped forward to look after the project until a lead
developer came forward. We're still waiting for that person to come forward.

> On Windows platforms ODBC will remain the default method to quickly
> link a Database with any number of applications for some time to
> come.

The central problem is that none of us have the skills and/or time to
build/maintain an ODBC driver even though we'd all like to see updates.
There are three options I can see possible:
1. Someone steps up and takes over development as before
2. Increased use by Windows users leads to a Windows developer to step
forward and go to #1
3. The ODBC driver is replaced with a wrapper to another driver
(JDBC/.Net) hopefully reducing the maintenance requirements.

There is an ODBC=>JDBC gateway out there (closed-source I believe) but I
don't know of a .Net one.

--
   Richard Huxton
   Archonet Ltd

Re: ODBC driver for Windows & future...

From
"Merlin Moncure"
Date:
> There is an ODBC=>JDBC gateway out there (closed-source I believe) but
I
> don't know of a .Net one.

I think rather the ODBC driver should wrap the libpq library.  The .net
driver has some justification for implementing its own protocol stack
because of marshalling issues; the ODBC has little to gain besides
paying a extra maintenance price which is now being paid in full :-).
There may be some valid reasons why this was done that I'm not aware of,
though.

There are many tangential issues to these projects that make them
difficult...for example internationalization issues that might
discourage the more traditional driver writer.

Merlin



Re: ODBC driver for Windows & future...

From
"Russ Cobbe"
Date:
> > There is an ODBC=>JDBC gateway out there (closed-source I believe) but
> I
> > don't know of a .Net one.
>
> I think rather the ODBC driver should wrap the libpq library.  The .net
> driver has some justification for implementing its own protocol stack
> because of marshalling issues; the ODBC has little to gain besides
> paying a extra maintenance price which is now being paid in full :-).
> There may be some valid reasons why this was done that I'm not aware of,
> though.

We might be able to help out here.  We have written more ODBC drivers
than any one else (50+).  www.opayc.com

What is the status of the project?
What needs doing?

We probably wouldn't be able to contribute anything until the new
year but we can start looking at things now.

Russ Cobbe, President
Inline Internet Systems, Inc.
20 Marlatts Road
Thorold, ON L2V 1N1 Canada
1-905-680-0436x211 http://www.inline.net
Providing Comprehensive E-Business Solutions


Re: ODBC driver for Windows & future...

From
"Dave Page"
Date:

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Russ Cobbe
> Sent: 30 November 2004 14:10
> To: pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] ODBC driver for Windows & future...
>
> We might be able to help out here.  We have written more ODBC
> drivers than any one else (50+).  www.opayc.com

You should know what you are doing then! :-)

> What is the status of the project?

The current status is that the project has no primary maintainer, and
just has myself and Peter Eisentraut doing occasional bug fixes and
snapshot releases (also, Scot Loach has contributed recently). I cannot
speak for Peter, but I no longer have any personal interest in ODBC as
my primary project (pgAdmin) is now libpq based, and I'm not exactly an
ODBC expert anyway.

> What needs doing?

Some time back I tried to drum up some new developers, and compiled the
following list:

> 1) ODBC 3 compliance should be checked. Are all the required ODBC
>    functions present. Do they work as expected.
>
> 2) Ditto, Unicode versions of all the functions.

1 & 2 were roughly checked by Peter - and with the odd exception,
everything was pretty much there iirc. Check the archives for details.

> 3) An audit of the code for possible buffer overrun problems should
  >  be undertaken.

Peter provided patches to address various buffer overruns - a further
audit probably wouldn't hurt though.

> 4) An update to the v3 fe-be protocol is required.
>
> 5) SSL support should be added.

The general concencus is that these issues are best resolved by
replacing the current connection code (mainly in connection.c/socket.c)
with libpq. This will of course future-proof much of the driver.

> 6) The GUI needs a cleanup (move to use of tabs rather than separate
>    dialogues, and a 'test connection' button?).

I've cleaned up a fair bit of the GUI (though it still currently uses
buttons, not tabs). The test connection button would probably make a
nice project for a new developer to dabble with.

> 7) Documentation and the website needs updating (as always :-) )

In addition, there are a number of bugs/support request logged on the
project site at http://gborg.postgresql.org/project/psqlodbc. I suspect
the majority of these are not necessarily bugs, but configuration/3rd
party software issues - regardless, they should be confirmed and closed
or fixed. This would not necessarily take an expert in the driver
internals, so any ODBC programmer could tackle some of these using their
preferred language.

The only actual bugs that I know of that are outstanding are:

1) Apparently there is a threading bug which can cause a crash on loaded
IIS websites. I have asked, but no-one has yet provided me with a
logfile or backtrace (whether I could actually fix it if they did is
another issue altogether!).

2) There is a bug in the experimental cursor support that causes crashes
from time to time. This option is currently turned off by default.

In addition, the installer needs to be updated to an open source
solution. I will undertake this myself, using Wix (allowing production
of merge modules for use with the pginstaller and other projects).

> We probably wouldn't be able to contribute anything until the
> new year but we can start looking at things now.

Any time you can contribute would be gratefully received :-)

I would also add that the reports of doom and gloom here are a little
exagerated imho. Yes there is a lack of maintainers atm (which is
definitely not good), however, the driver essentially works and is
pretty stable for the majority of users. Most of the work that needs to
be done is on the newer fe/be protocol and SSL, which though highly
desirable, are not totally essential to the future of the planet, given
that PostgreSQL still supports the older protocol.

Regards, Dave

P.S. To the rest of the list, the previous maintainer was Hiroshi Inoue,
not Iroshi :-).

Re: ODBC driver for Windows & future...

From
Jeff Eckermann
Date:
--- Russ Cobbe <russc@inline.net> wrote:

> > > There is an ODBC=>JDBC gateway out there
> (closed-source I believe) but
> > I
> > > don't know of a .Net one.
> >
> > I think rather the ODBC driver should wrap the
> libpq library.  The .net
> > driver has some justification for implementing its
> own protocol stack
> > because of marshalling issues; the ODBC has little
> to gain besides
> > paying a extra maintenance price which is now
> being paid in full :-).
> > There may be some valid reasons why this was done
> that I'm not aware of,
> > though.
>
> We might be able to help out here.  We have written
> more ODBC drivers
> than any one else (50+).  www.opayc.com
>
> What is the status of the project?
> What needs doing?
>
> We probably wouldn't be able to contribute anything
> until the new
> year but we can start looking at things now.

Many people (including me) would be blessing you if
you took it on.

The status of the project is: drifting.  The driver
has been maintained by volunteers, with the attendant
time and resource problems.  The last maintainer,
Hiroshi Inouye, did a great job, but since he quit,
no-one has stepped forward to take his place, in spite
of appeals.

Based on recent discussions on various lists (my
memory is hazy as to which), the opinion of the
Hackers appears to be that the ODBC driver is best
reimplemented as a wrapper to libpq, the PostgreSQL C
client library.

The nature of the current driver is determined by
implementation choices made years ago, when
circumstances were different.  The code effectively
reimplements what libpq already provides, but in a
manner that is likely to be less robust.  On the face
of it, further development along that track is a
questionable exercise at best.  Especially in view of
the fact that none of us, who is currently involved,
actually understands the code.

>
> Russ Cobbe, President
> Inline Internet Systems, Inc.
> 20 Marlatts Road
> Thorold, ON L2V 1N1 Canada
> 1-905-680-0436x211 http://www.inline.net
> Providing Comprehensive E-Business Solutions
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match
>




__________________________________
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.
http://promotions.yahoo.com/new_mail

Re: ODBC driver for Windows & future...

From
"Merlin Moncure"
Date:
> > I think rather the ODBC driver should wrap the libpq library.  The
.net
> > driver has some justification for implementing its own protocol
stack
> > because of marshalling issues; the ODBC has little to gain besides
> > paying a extra maintenance price which is now being paid in full
:-).
> > There may be some valid reasons why this was done that I'm not aware
of,
> > though.
>
> We might be able to help out here.  We have written more ODBC drivers
> than any one else (50+).  www.opayc.com
>
> What is the status of the project?
> What needs doing?

Well, the current ODBC driver has changed very little in the last 2-3
years.  The primary maintainer left quite some time ago.  The ODBC
driver meets about 90% of most user's requirements.  My personal opinion
is that it's fine for client side development but is sketchy for server
work.

Well, if you've written a lot of ODBC drivers, I'd imagine you had some
type of framework which could be re-implemented fairly easy.  If this is
true, and you are willing to open source your stuff under an appropriate
license, or contribute newly crated stuff, I'd say to go for it!

I'd say for starters the current code needs to be looked over and a fair
estimation of what would be required to replace the protocol
implementation with libpq would be an excellent start.   Beyond that, it
really depends on how you would like to approach things with your
contributions.

Merlin

Re: ODBC driver for Windows & future...

From
Johann Zuschlag
Date:
Hi,

there are some developers around, but there don't seem to be in the
position to take over the development for whatever reason. Dave can't do
the job, neither can I nor anybody else. (I'm not good at C and I don't
really understand the ODBC API).

Ok, so there is nobody ready to step up to do the job.

Then let's try a different approach. Let's start some kind of joint
development.

So we just start by drawing some lines, e.g, Merlin proposing to wrap
the libpg library. Somebody else could line out how the proposed driver
architecture should look like and so on. Maybe we can use plenty of the
old ODBC code. And there are some developers around who want to help
with coding. So the load of the development is distributed.

Well, I could do the documentation of the process and maybe some
organizational stuff. Saying that I know I have limited time. But I want
to contribute somehow. I need the driver.

Anybody want to join? Comments?

regards,

Johann

Philippe Lang wrote:

>Hi,
>
>I think I'm not the only one to get worried about the future of the
>PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
>now and then, but nobody seems to understand anymore how the driver
>really works...
>
><snip>
>


Re: ODBC driver for Windows & future...

From
Robert Treat
Date:
On Tuesday 30 November 2004 05:16, Simeó Reig wrote:
> >Hi,
> >
> >I think I'm not the only one to get worried about the future of the
> >PGSQL ODBC driver. Since Iroshi has left last year, I have seen fixes
> >now and then, but nobody seems to understand anymore how the driver
> >really works...
> >
> we have been using the driver since three years ago, we had made an
> application
> with 40 concurrent ODBC users, 144 tables and milions of rows, postgresql
> is fantastic, with a great performance (dell 2650 dual processor raid 10)
> and the most important : zero problems in three years.
>
> Now, we have to make other application but we will not use postgresql
> because
> we don't trust ODBC project, seems nobody understands nothing !!

So for three years you have taken from the work of hundreds of others who have
provided you with a free database and a working driver to use along with it,
but now when there are signs of trouble, you're just going to pack up and
leave?  Why not dedicate just one of your programmers to working on the ODBC
driver full time?  This way you can assure that all of your issues with the
driver are still addressed, you'll save the costs of switching to another
system, and still get an extremely robust database for the cost of one
programmer? Seems like a good investment to me.

--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL

Re: ODBC driver for Windows & future...

From
Robert Treat
Date:
On Tue, 2004-11-30 at 04:36, Philippe Lang wrote:
> What most surprises me, is that there is not viable commercial
> alternative to the open-source ODBC driver. All the commercial drivers I
> have tested do not work at all...

Have you tested Command Prompts commercial ODBC driver
(http://www.commandprompt.com/entry.lxp?lxpe=296) ? It has SSL support
and is fully compatible with PostgreSQL. In fairness I haven't used it
but Command Prompt has a pretty good relationship with the PostgreSQL
community; I'm sure they'd be interested in any feedback you wanted to
give them.


Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


Re: ODBC driver for Windows & future...

From
Simeó Reig
Date:
----- Original Message -----
>From: "Robert Treat" <xzilla@XXXXXXXXXXXX>
>
>So for three years you have taken from the work of hundreds of others who
>have
>provided you with a free database and a working driver to use along with
it,
>but now when there are signs of trouble, you're just going to pack up and
>leave?  Why not dedicate just one of your programmers to working on the
>ODBC
>driver full time?  This way you can assure that all of your issues with the
>driver are still addressed, you'll save the costs of switching to another
>system, and still get an extremely robust database for the cost of one
>programmer? Seems like a good investment to me.

Yes, you are right. But we are not good c programmers, but we are going to
offer
our help to improve npgsql because it's written in c sharp.

The problem is not this, nobody can't help in every project. The problem is
that
odbc project is frozen since one year ago, well, solutions? I believe that
one
solutions could be rewrite all or make, if is it possible, a bridge between
odbc
project an other project.

Best regards





Re: ODBC driver for Windows & future...

From
Shachar Shemesh
Date:
Richard Huxton wrote:

> AFAIK no substantial changes have been made in the ODBC driver
> recently. Dave generously stepped forward to look after the project
> until a lead developer came forward. We're still waiting for that
> person to come forward.

Hi,

I just wanted to chip in with a slightly irrelevant info.

Those who don't know me - I'm the CEO of Lingnu Open Source Consulting
(http://www.lingnu.com). We are the ones who wrote the OLE DB provider
for Postgresql (http://gborg.postgresql.org/projects/oledb). It was done
as a payed job by a client who needed OLE DB support. While not
complete, it is already working fairly well.

More to the point, OLE DB uses libpq as a backend. While this did save
us from rewriting the protocol (we started work after 7.4 was already
out, so protocol versions was not an issue), it did introduce some
points that had better be considered. As far as I know, OLE DB is the
only driver currently using libpq. Some aspects of memory allocation and
thread safety make it a little difficult to scale further reliably.
These are not crucial problems, but they are things to consider when
developing a driver (as opposed to an end application).

Also of interest is that this very same client is also interested in the
ODBC driver for a different project. We have already did some porting of
their application, and have spotted a serious performance issue with
ODBC when long query results are retrieved. It is possible (thought it
would be best not to count on it) that we will do some work in that
direction on ODBC in the foreseeable future. The reason we did not step
forward and offered ourselves as full maintainers of the code is that we
don't feel we have the resources for that. It is good to know, however,
that the facilities for sending patches and having them committed exists.

>> On Windows platforms ODBC will remain the default method to quickly
>> link a Database with any number of applications for some time to
>> come.
>
>
> The central problem is that none of us have the skills and/or time to
> build/maintain an ODBC driver even though we'd all like to see
> updates. There are three options I can see possible:
> 1. Someone steps up and takes over development as before

Like I said, that is unlikely to be us, unless we get paid for it. It
seems we are going to be learning at least some of the code (and have
submitted a modest patch in the past, in fact). If someone with more
ODBC experience exists, however, I think it would be best to let them do
this task. We are maintaining OLE DB already, and do not really seek the
position of "master of Postgresql drivers".

> 2. Increased use by Windows users leads to a Windows developer to step
> forward and go to #1

That's how free software usually works.

> 3. The ODBC driver is replaced with a wrapper to another driver
> (JDBC/.Net) hopefully reducing the maintenance requirements.

Now I don't know JDBC and .Net very well. If they are anything like OLE
DB, that's not going to be a simple one. The interfaces are fairly
incompatible.

> There is an ODBC=>JDBC gateway out there (closed-source I believe) but
> I don't know of a .Net one.

       Shachar

P.S.
While I won't claim that the OLE DB driver is as complete as the JDBC or
.Net ones (or the ODBC one, while we're at it), I'd just like to make
sure that the reason it wasn't mentioned was this rather than ignorance.

    Sh.

--
Shachar Shemesh
Lingnu Open Source Consulting ltd.
http://www.lingnu.com/


Re: ODBC driver for Windows & future...

From
"Joshua D. Drake"
Date:
Robert Treat wrote:
> On Tue, 2004-11-30 at 04:36, Philippe Lang wrote:
>
>>What most surprises me, is that there is not viable commercial
>>alternative to the open-source ODBC driver. All the commercial drivers I
>>have tested do not work at all...
>
>
> Have you tested Command Prompts commercial ODBC driver
> (http://www.commandprompt.com/entry.lxp?lxpe=296) ? It has SSL support
> and is fully compatible with PostgreSQL. In fairness I haven't used it
> but Command Prompt has a pretty good relationship with the PostgreSQL
> community; I'm sure they'd be interested in any feedback you wanted to
> give them.

Actually we would :). On that note however it is my suggestion that you
wait till the end of the year. At the end of the year our driver (which
you do get source with upon request because it is LGPL). We are
currently working on adding:

Complete level 1 ODBC compatibility
selefCursors

Sincerely,

Joshua D. Drake



>
>
> Robert Treat


--
Command Prompt, Inc., home of PostgreSQL Replication, and plPHP.
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-667-4564 - jd@commandprompt.com - http://www.commandprompt.com
Mammoth PostgreSQL Replicator. Integrated Replication for PostgreSQL

Attachment

Re: ODBC driver for Windows & future...

From
Marko Ristola
Date:
Hi,

I have investigated the ODBC driver behaviour, when the query result has
very many rows.

It seems, that the whole query result is stored as such into a memory
buffer before any
further processing.
That buffer is reallocated, when needed. If the buffer is for example
50Mbytes, and
after reallocation it's size will be 100Mbytes. The malloc() or
realloc() takes a very long time.
I investigated this bottleneck on Linux ODBC driver.

The procedure without an ODBC cursor is as follows:
1. Read all query result data from the backend to the huge buffer.
    (maybe sometimes restructure the buffer, if some column size on the
buffer is exceeded.)
    This seems to be the bottleneck with the large malloc() operation.
2. Read (and convert) the asked results from the buffer for the given row.


Allocating huge buffers is inefficient.
Linux operating system handles allocating big files much better
than allocating big memory areas.
More efficient would be to use a temporary file:
sequential file scans are rather fast.

One way for solving the problem:

Maybe the key for solving the bottleneck is to tune the operating system
to free enough memory
beforehand: If the operating system has 100Mb unused memory, it is a lot
faster,
than if it has only 2Mb unused memory ready for fast memory allocations.

Good way for solving the problem:

The bottleneck can be avoided on the program side by using ODBC cursor.
With ODBC cursor one can fetch for example 1000 rows in one batch
from the database server. You get next 1000 rows with a new fetch.
This way there is no limit on the number of rows fetched on any database.

On large result sets, there is always a limit with the memory on 32 bit
systems.
On 64 bit systems this limit goes away, but the limit with some slowdown on
nonlocal CPU memory won't go away even on high end machines.
(NUMA machines have about 2Gbytes memory near each CPU. Other memory is
behind
a slower bus ). So memory allocations over 2Gb are not good for speed.

So, the ODBC cursor scales well for any huge query result, on any
client operating system. It works even on Java, where memory is
extremely limited.

Other ways to solve the problem?
ODBC Code: How about  allocating memory in 4Mb chunks? The operating
system handles
small memory allocations more easily and frees more memory to be available
in the background while the ODBC driver fills the allocated chunk.

Marko Ristola

*Shachar Shemesh wrote:

*lso of interest is that this very same client is also interested in the
ODBC driver for a different project. We have already did some porting of
their application, and have spotted a serious performance issue with
ODBC when long query results are retrieved. It is possible (thought it
would be best not to count on it) that we will do some work in that
direction on ODBC in the foreseeable future. The reason we did not step
forward and offered ourselves as full maintainers of the code is that we
don't feel we have the resources for that. It is good to know, however,
that the facilities for sending patches and having them committed exists.


Re: ODBC driver for Windows & future...

From
markw@mohawksoft.com
Date:
>
> Hi,
>
> I have investigated the ODBC driver behaviour, when the query result has
> very many rows.
>
> It seems, that the whole query result is stored as such into a memory
> buffer before any
> further processing.
> That buffer is reallocated, when needed. If the buffer is for example
> 50Mbytes, and
> after reallocation it's size will be 100Mbytes. The malloc() or
> realloc() takes a very long time.
> I investigated this bottleneck on Linux ODBC driver.

There is actually a setting that makes the ODBC driver use a cursor to
only grab a chunk at a time, its in the docs somewhere as I have used it
in the past.


>
> The procedure without an ODBC cursor is as follows:
> 1. Read all query result data from the backend to the huge buffer.
>     (maybe sometimes restructure the buffer, if some column size on the
> buffer is exceeded.)
>     This seems to be the bottleneck with the large malloc() operation.
> 2. Read (and convert) the asked results from the buffer for the given row.
>
>
> Allocating huge buffers is inefficient.
> Linux operating system handles allocating big files much better
> than allocating big memory areas.
> More efficient would be to use a temporary file:
> sequential file scans are rather fast.
>
> One way for solving the problem:
>
> Maybe the key for solving the bottleneck is to tune the operating system
> to free enough memory
> beforehand: If the operating system has 100Mb unused memory, it is a lot
> faster,
> than if it has only 2Mb unused memory ready for fast memory allocations.
>
> Good way for solving the problem:
>
> The bottleneck can be avoided on the program side by using ODBC cursor.
> With ODBC cursor one can fetch for example 1000 rows in one batch
> from the database server. You get next 1000 rows with a new fetch.
> This way there is no limit on the number of rows fetched on any database.
>
> On large result sets, there is always a limit with the memory on 32 bit
> systems.
> On 64 bit systems this limit goes away, but the limit with some slowdown
> on
> nonlocal CPU memory won't go away even on high end machines.
> (NUMA machines have about 2Gbytes memory near each CPU. Other memory is
> behind
> a slower bus ). So memory allocations over 2Gb are not good for speed.
>
> So, the ODBC cursor scales well for any huge query result, on any
> client operating system. It works even on Java, where memory is
> extremely limited.
>
> Other ways to solve the problem?
> ODBC Code: How about  allocating memory in 4Mb chunks? The operating
> system handles
> small memory allocations more easily and frees more memory to be available
> in the background while the ODBC driver fills the allocated chunk.
>
> Marko Ristola
>
> *Shachar Shemesh wrote:
>
> *lso of interest is that this very same client is also interested in the
> ODBC driver for a different project. We have already did some porting of
> their application, and have spotted a serious performance issue with
> ODBC when long query results are retrieved. It is possible (thought it
> would be best not to count on it) that we will do some work in that
> direction on ODBC in the foreseeable future. The reason we did not step
> forward and offered ourselves as full maintainers of the code is that we
> don't feel we have the resources for that. It is good to know, however,
> that the facilities for sending patches and having them committed exists.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>