Re: psqlodbc versioning - Mailing list pgsql-odbc

Dave Page wrote:
> ...
>
> WRT to the lock issue, I haven't checked, but I suspect that the driver
> doesn't report it because PostgreSQL simply waits for it to clear before
> continuing (or detecting a deadlock and aborting the transaction). I'm
> not sure that the driver is supposed to report it off the top of my head
> though.

Okay, I wondered if this would come down to the question of exactly what
is the odbc spec.

The application in question originally ran with a non-SQL database
engine ("ProISAM") that suffered from table size limits.  The larger
clients started moving to Oracle a few years ago as they outgrew those
limits.  More recently, the vendor announced to all clients that they
would stop supporting ProISAM entirely, and everybody had to either move
to Oracle (big bucks) or MS SQL server, or Postgres, which they were
just starting to support. I suspect they never did a lot of testing on
postgres, just assumed they could make minor adjustments to their Oracle
code and it would just work. And apparently Oracle's odbc driver does
report row locks so the application can relay that information to the user.

We were one of the first clients to adopt postgres, and were surprised
to run into this symptom.  I have users calling my beeper all the time
saying the application is broken, essentially because they can't
distinguish a record lock from the system being hung, and it's starting
to drive me up the wall.

Obviously we're talking about some problematic application design. It
relies too much, and too conservatively, on row locks.  If one user is
looking at a client information screen (even if they aren't changing
anything) no other users can work with that client in any way until the
first user leaves that screen.  Bad things can happen when somebody
walks away from their computer and goes to lunch.

Being unable to improve the application ourselves since we don't own it,
and assuming we're not just getting a snow job about what odbc supports,
I'd be pleased if we could promote adding lock reporting to the psql
odbc interface -- even if that means expanding the spec a little to
duplicate that part of Oracle's behavior which our vendor apparently
relies on. (Or does that imply postgres-proper issues too, making the
idea unworkable?)

A less desirable solution is to cough up the bucks and convert to
Oracle. I'd have a pretty hard time selling that to our bean counters
this year.

In a better world, we would ditch the vendor completely, but they
monopolized this little corner of the market some time ago and no viable
competitor has emerged yet.  It's an unwieldy legacy application that
most of their clients are entrenched with -- and although some of them
have better resources than we do, frustration abounds.

   -- Mark