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.