Re: Updateable cursors

On Wed, 24 Jan 2007, FAST PostgreSQL wrote:
>
> We are trying to develop the updateable cursors functionality into
> Postgresql. I have given below details of the design and also issues we are
> facing. Looking forward to the advice on how to proceed with these issues.
>
> Rgds,
> Arul Shaji
>

Hi Arul,

...I can see people are picking apart the implementation details so you're
getting good feedback on your ambitious proposal. Looks like you've put a
lot of thought/work into it.

I've never been a fan of cursors because they encourage bad behavior;
"Think time" in a transaction sometimes becomes "lunch time" for users and
in any event long lock duration is something to be avoided for the sake of
concurrency and sometimes performance (vacuum, etc). My philosophy is "get
in and get out quick."

Ten years ago May, our first customer insisted we implement what has
become our primary API library in Java and somewhat later I was shocked to
learn that for whatever reason Java ResultSets are supposed to be
implemented as _updateable_cursors._ This created serious security issues
for handing off results to other programs through the library - ones that
don't even have the ability to connect to the target database. Confirmed
in the behavior of Informix, we went through some hoops to remove the need
to pass ResultSets around. (If I had only known Postgres didn't implement
the RS as an updateable cursor, I'd have pushed for our primary platform
to be Postgres!)

What impresses me is that Postgres has survived so well without updateable
cursors. To my mind it illustrates that they aren't widely used. I'm
wondering what troubles lurk ahead once they're available. As a
DBA/SysAdmin, I'd be quite happy that there existed some kind of log
element that indicated updateable cursors were in use that I could search
for easily whenever trying to diagnose some performance or deadlocking
problem, etc, say log fiile entries that indicated the opening and later
closing of such a cursor with an id of some kind that allowed matching up
open/close pairs. I also think that that the documentation should be
updated to not only indicate usage of this new feature, but provide
cautionary warnings about the potential locking issues and, for the
authors of libraries, Java in particular, the possible security issues.

Regards,
Richard

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
rtroy(at)ScienceTools(dot)com, http://ScienceTools.com/