Thread: psqlodbc versioning

Can someone explain the versioning convention I see here:

   ftp://ftp8.us.postgresql.org/postgresql/odbc/versions/src/

Is 7.2.5 current?  The 07.03.0200 has a later file date, but I don't
know if I am looking at a later version of the same package or something
different.

I'm basically trying to get feedback to a proprietary database vendor
who claims to have recently started to support postgres.  We're using
their product on a Red Hat ES platform and are having some trouble with
row locks - they cause some queries to simply hang uninformatively, and
the vendor is blaming the problem on the odbc driver not reporting the
lock.  To be honest I am not sure whether these people know what they
are talking about -- I won't claim to know much, being just a user who
hasn't been working with pgsql for very long, and then only through
their abstraction layer.

What we have installed currently is psqlodbc-7.2.5 along with
postgresql-7.3.4, and I'm in the process of setting up 7.4.3 on a test
server. The reason I'm not using the 07.03.0200 driver(?) is that it
doesn't seem to generate a libodbc.so file, which is needed for the
installation scripts provided by this vendor's product.

I can probably provide a little more information (on-list or off-list)
if it might help anyone here make some sense of our situation.

   -- Mark

> ... doesn't seem to generate a libodbc.so file

A very little digging shows I'm all wet about where that file comes
from... ah well, that's a indication of how little I know. I'd still
appreciate help with the other points if anyone can enlighten me.

> -----Original Message-----
> From: pgsql-odbc-owner@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Mark Slagell
> Sent: 07 July 2004 20:39
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] psqlodbc versioning
>
> Can someone explain the versioning convention I see here:
>
>    ftp://ftp8.us.postgresql.org/postgresql/odbc/versions/src/
>
> Is 7.2.5 current?  The 07.03.0200 has a later file date

07.03.0200 is current. We changed to using ODBC notation for the version
which includes leading zeros and adds a couple on the end.

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.

Regards, Dave.

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

Mark Slagell wrote:
> 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?)

Could you show us some kind of specification about what this new lock
reporting interface would look like (what functions, what parameters,
etc.)?

It is possible to look at the current set of locks, and in 7.5 there
will even be a LOCK NOWAIT option that allows you to try a lock and
return a failure without waiting if the lock can't be acquired.  These
are only table locks.  To acquire row-level locks, you use SELECT FOR
UPDATE before you actually write to the table, also with the new NOWAIT
option.

But the whole concept of locks is sort of obsolete since PostgreSQL uses
multiversion concurrency control which does not require locks (loosely
speaking).  Moreover, long-running transactions (which would be
required to hold locks for a long time) are very problematic and should
be avoided at all costs.  What you need is a user-space cooperative
locking system.  There is a bit of that in contrib/userlock, but making
that work, plus making that work with the ODBC driver and in a way that
you application can swallow could be a large project.

Peter Eisentraut wrote:
>
> Could you show us some kind of specification about what this new lock
> reporting interface would look like (what functions, what parameters,
> etc.)?

I'll try to get some input from the vendor on this. I don't know what
their source looks like, being just a local admin of one of their client
sites -- and probably getting a bit too involved in things I have no
control over.

> ... the whole concept of locks is sort of obsolete since PostgreSQL uses
> multiversion concurrency control which does not require locks (loosely
> speaking)...

Maybe this app is married to the lock concept because it has to work in
a roughly equivalent way with various underlying database layers, and so
tries to cling to the mechanisms they have in common.  The concurrency
control idea makes a lot of sense, and I wouldn't be surprised if they
are not aware of it or don't understand it.

Also they have things set up so that maybe a "session" ends up not
meaning what it should. For instance, although the application has its
own separate users and means of authenticating them, I am pretty sure it
makes all postgres queries as a single generic user.

Thanks for taking the trouble to reply.  I'll pass along all the
information I can, and try to light a constructive fire under these guys.

   -- Mark