Thread: ODBC driver for Windows & future...
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
Philippe Lang wrote:
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.
HelloHi, 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
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
>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".
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) > >. > > >
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
> 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
> > 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
> -----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 :-).
--- 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
> > 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
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> >
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
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
----- 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
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/
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
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.
> > 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 >