Updates via ODBC commands

Hi List,


I have a client that complains that using the old (7.4) ODBC driver he
could update the database with the ODBC commands (i.e. - not by sending
explicit "update" sqls), but that using the latest available driver he
cannot. I have not yet seen the code in question, so I cannot yet give
out more details, but I wanted to know whether there is a known
regression in functionality as a result of the move from sockets code to
pqlib (or for whatever other reason).


Thanks,



Shachar


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Updates via ODBC commands

> I have a client that complains that using the old (7.4) ODBC driver he
> could update the database with the ODBC commands (i.e. - not by sending
> explicit "update" sqls), but that using the latest available driver he
> cannot. I have not yet seen the code in question, so I cannot yet give
> out more details, but I wanted to know whether there is a known
> regression in functionality as a result of the move from sockets code to
> pqlib (or for whatever other reason).

Yes. There is well known issue with dropping updateable cursor support.
I'm sorry if you need updateable cursor you have to stay in using 07.03
version (or maybe 08.00 - I don't know it exactly). I'm sure that there
is no updateable cursor support in 08.01 version.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Re: Updates via ODBC commands

Am 13 Jan 2006 um 23:52 hat Ludek Finstrle geschrieben:

> Yes. There is well known issue with dropping updateable cursor support.
> I'm sorry if you need updateable cursor you have to stay in using 07.03
> version (or maybe 08.00 - I don't know it exactly). I'm sure that there
> is no updateable cursor support in 08.01 version.

Hi,

I have found the same problem - or even missing feature in the new driver.
But why the developers of the driver are dropping working features ?

This is the case why I must ship a driver version with my sample application
to show it's complete features. Its a bad idea to tell the user to use an o=
ld version,
because my application only works with that.

I just have done a look at the website and did not see any comments about t=
hat.
If this is really missing, all users relying on updateable cursors, are irr=
itated.

I think this is an important information for users. More, I may be informed=
about, that
the old driver is in a frozen state - so there are no more bugfixes for it =
- no more
support for that version ?

Also I identified little problems - or differences in using 8.x version wit=
h the cursor
library. I must retry that to report it.

Regards, Lothar

>
> Regards,
>
> Luf
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>


--
Lothar Behrens | Rapid Prototyping ...
Rosmarinstr 3 |
40235 Düsseldorf | www.lollisoft.de




---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Updates via ODBC commands

Ludek Finstrle wrote:

>Yes. There is well known issue with dropping updateable cursor support.
>I'm sorry if you need updateable cursor you have to stay in using 07.03
>version (or maybe 08.00 - I don't know it exactly). I'm sure that there
>is no updateable cursor support in 08.01 version.
>
>
Let me give you a short rundown of the customer. We started a project of
migrating one of their programs to Postgresql. Things were already
working when we hit a snag - performance for retreving large tables was
not acceptable. I managed to narrow it down to a problem in the ODBC
driver (7.4), where the lines are retreived one at a time, probably due
to the need to emulate updateable cursors.

The client decided to invest money in getting the problem solved. This
was about the time that Hiroshi quit the project. After some
consideration, they decided to freeze work on the particular product
they were working on, and work on another one of their projects, which
uses OLE DB. They sponsored the lion's share of the development of
PgOleDb to date (with my company, Lingnu, doing the actual work plus
putting a little pro bono work as well).

They now got back to their original product, and were suprised to find
out that what used to work no longer does. I described the work done on
the ODBC driver as a "rewrite". Assuming they decide to move forward
with the support, there are three possible avenues for continuing:
1. Fix the performance problem with 7.4
2. Add updateable cursors to 8.01
3. Switch the product to use OLE DB and add updateable rowsets there
(PgOleDb does not currently support updates either).

I'm currently assuming that any of the above would have to actually be
done by Lingnu with their sponsorship, but feel free to pleasently
surprise me. I'm fairly sure that I'll manage to convince them that 1 is
not a good idea (unless someone here thinks that the new version
under-performs in relation to the direct socket version). I believe they
will go with 2, in which case we'll obviously release any work we do.

>Regards,
>
>Luf
>
>
Shachar

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Updates via ODBC commands

> I have found the same problem - or even missing feature in the new driver.
> But why the developers of the driver are dropping working features ?

They don't want. But there is a lot of reasons leads to the current
state. Hiroshi left the project at first. No one else understand the
ODBC driver code. The updateable cursor is the most blind part
without comments (or so few comments that we don't understand it).

Old driver uses socket and the new one uses libpq library to communicate
with backend. There is a lot of problems with the code and the result
was that we lost some fetures (as keyset cursor, updateable cursor and
maybe some others). We're working on the psqlODBC driver.
I don't see you help us. You are only the judge (it's the easiest role,
isn't it?).

> Its a bad idea to tell the user to use an old version,
> because my application only works with that.

Do you have better idea? Then welcome and say it to us. But please be
constructive. I'm working on the project for two months and I see no
other helper instead Dave and Anoop. I see few other helper for long
time in pgsql-odbc mailing list.

> I just have done a look at the website and did not see any comments
> about that.

This is true. Are you volunteer to maintain the web pages? We don't
have time for it. I can't follow bug fixing. So web pages isn't in
my focus.

> If this is really missing, all users relying on updateable
> cursors, are irritated.

I'm irritated from you. May I stop developing the psqlODBC project?
On the other side feel free to repair updateable cursors. What does
defends you?

> I think this is an important information for users. More, I may be
> informed about, that the old driver is in a frozen state - so there
< are no more bugfixes for it - no more support for that version ?

I hope Dave will respond to this two sentences in few days.

> Also I identified little problems - or differences in using 8.x
> version with the cursor library. I must retry that to report it.

No you don't must. Feel free to fix it ant send us the patch which
solve the problem.

I see no bug report from you. You maybe report it a long time ago.
Feel free to report it again if noone work on it.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 1: 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

Re: Updates via ODBC commands

> They now got back to their original product, and were suprised to find
> out that what used to work no longer does. I described the work done on
> the ODBC driver as a "rewrite". Assuming they decide to move forward
> with the support, there are three possible avenues for continuing:
> 1. Fix the performance problem with 7.4
> 2. Add updateable cursors to 8.01
> 3. Switch the product to use OLE DB and add updateable rowsets there
> (PgOleDb does not currently support updates either).
>
> I'm currently assuming that any of the above would have to actually be
> done by Lingnu with their sponsorship, but feel free to pleasently
> surprise me. I'm fairly sure that I'll manage to convince them that 1 is
> not a good idea (unless someone here thinks that the new version
> under-performs in relation to the direct socket version). I believe they
> will go with 2, in which case we'll obviously release any work we do.

I'm sorry I don't answer you directly. I hope Dave will answer you in
relative short time.

Please be patient

Luf

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Re: Updates via ODBC commands

On 14/1/06 13:57, "Ludek Finstrle" wrote:

>> They now got back to their original product, and were suprised to find
>> out that what used to work no longer does. I described the work done on
>> the ODBC driver as a "rewrite". Assuming they decide to move forward
>> with the support, there are three possible avenues for continuing:
>> 1. Fix the performance problem with 7.4
>> 2. Add updateable cursors to 8.01
>> 3. Switch the product to use OLE DB and add updateable rowsets there
>> (PgOleDb does not currently support updates either).
>>
>> I'm currently assuming that any of the above would have to actually be
>> done by Lingnu with their sponsorship, but feel free to pleasently
>> surprise me. I'm fairly sure that I'll manage to convince them that 1 is
>> not a good idea (unless someone here thinks that the new version
>> under-performs in relation to the direct socket version). I believe they
>> will go with 2, in which case we'll obviously release any work we do.
>
> I'm sorry I don't answer you directly. I hope Dave will answer you in
> relative short time.

Sorry, I'm short of time for psqlODBC right now so haven't been following
the list much the last week or so.

The libpq and previous versions of the driver do perform similarly - I
benchmarked them quite extensively when Anoop first ported the code.

Personally I'd like to see #2 happen, and even better, for them to sponsor
your time to do the work Shachar - we know you are experienced at writing
drivers, and I'm sure you could put your hand to psqlODBC fairly easily :-)

I'd rather not see #3 happen (from a psqlODBC contributor's point of view),
but obviously that doesn't apply to you!

Regards, Dave.


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

Re: Updates via ODBC commands

Ludek Finstrle wrote:

>>I have found the same problem - or even missing feature in the new driver.
>>But why the developers of the driver are dropping working features ?
>>
>>
>
>They don't want. But there is a lot of reasons leads to the current
>state. Hiroshi left the project at first. No one else understand the
>ODBC driver code. The updateable cursor is the most blind part
>without comments (or so few comments that we don't understand it).
>
>
Actually, at the time I did dive into it and got some sense from it (as
I said - it was responsible for a huge performance penalty). I may have
been too busy on PgOleDb at the time to assist.

I doubt my assitance would have helped anyways. The updateable cursor
implementation in psqlODBC was fundumentally broken. It heavilly relied
on the oid and tid fields, which meant that you could not query views
unless you explicitly defined the query as "static". The actual query
was also done one row at a time (hence the performance penalty mentioned
above). What I'm saying is that a rewrite would have had to happen even
if the code was understood.

>I don't see you help us. You are only the judge (it's the easiest role,
>isn't it?).
>
>
I'm, generally, with Ludek on this one. psqlODBC is free software. With
freedom comes responsibility. No one asked you to pay for your use of
the driver, but no one owes you anything in return. If you really need
this feature, you had better be prepared to either develop it yourself
or sponsor its development.

I know this seems like a tough lesson to learn, but it's an important
one. If you re-read the thread you will notice that asking a factual
question (i.e. - "was support for updateable cursors removed?", or even
"why was it removed", if phrased politely enough) will get answered, but
a complaint ("how dare you remove it?") will not be looked upon kindly.
If the realities of an open source project seem too harsh for you, feel
free to hire a consultant to do the communication with the list on your
behalf, and get into the code in case no answer comes. With the lack of
care comes, of course, the price tag for the consultant's time. That is
the tradeoff that free software offers.

It so happens that I run a company that offers such a service, but
before I'm accused of pluggin my own services,
http://www.postgresql.org/support/professional_support has a very long
list of companies who will love to offer you such a service. In fact,
maybe it's time I register Lingnu there....

>>Its a bad idea to tell the user to use an old version,
>>because my application only works with that.
>>
>>
>
>Do you have better idea? Then welcome and say it to us. But please be
>constructive. I'm working on the project for two months and I see no
>other helper instead Dave and Anoop. I see few other helper for long
>time in pgsql-odbc mailing list.
>
>
Sadly, such are the realities of most open source projects, Ludek.
Better accept it, or you will become bitter. Allow me to let you in on a
little secret - getting help from two people is considered a lot. NONE
of the FOSS projects I started ever got any real help from anyone.

>>I just have done a look at the website and did not see any comments
>>about that.
>>
>>
>
>This is true. Are you volunteer to maintain the web pages? We don't
>have time for it. I can't follow bug fixing. So web pages isn't in
>my focus.
>
>
To rephrase what Ludek is saying, the web site is indeed out of date. If
you have the time and skill to help with that, your time contribution
would be greatly appreciated.

>>If this is really missing, all users relying on updateable
>>cursors, are irritated.
>>
>>
>I'm irritated from you. May I stop developing the psqlODBC project?
>On the other side feel free to repair updateable cursors. What does
>defends you?
>
>
Again, allowing myself to paraphrase Ludek, don't look a gift horse in
the mouth (but do make sure there are no greek hiding inside). Some of
the people working on the complex set of code that is Postgresql and
related software are volunteers. Some are doing so under specific focus.
It is the unfortunate state of affairs that not all features are
addressed as quickly as everyone would want them to. People's irritation
has nothing to do with it, as I'm sure Ludek is willing to send each and
every one of them a complete refund of the money they paid him, twice
over for making them feel better.

If this feature is important to you, you *may* be in luck and my client
will choose to sponsor my work on it. If that doesn't happen, you can
round up all the parties that are irritated, have every one of them pay
some money, and gather enough money to sponsor it yourself. If there
truely are that many people waiting for this feature, each should have
to pay more than, say, 200$. That's not much to pay for unlimited seats
unlimited installation servers license for a fully ACID complient database.

>>Also I identified little problems - or differences in using 8.x
>>version with the cursor library. I must retry that to report it.
>>
>>
>
>No you don't must. Feel free to fix it ant send us the patch which
>solve the problem.
>
>
Actually, feel free to file a focused reproduceable bug report as well.
They are, usually, just as useful as actual patches. Just bear in mind
that it is possible that you will file a very reasonable bug report, and
no one will get around to looking at it after all.

Shachar

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend