Re: Updateable cursors

On Wed, 2007-01-24 at 14:54 +1100, John Bartlett wrote:

> The reason for those 5 options is to consider different means to cover the
> Prepared Stmt requirement where the different stages of processing are
> actually in different transactions.

John,

Thanks for explaining.

Wow! I've never come across such a requirement before, personally and
hadn't even imagined anybody would want to do this.

ISTM the main use for positioned UPDATE/DELETE is for a single
transaction to first open a cursor and then loop around doing FETCH and
then positioned UPDATE/DELETE on that cursor.

It would make the implementation considerably easier to limit the
initial implementation to only work using WITHOUT HOLD cursors (the
default). This will allow you to cache the ctid, rather than re-seeking
via the index, so will offer considerably better performance also.

That is also the safe thing to do, since PostgreSQL's implementation of
WITH HOLD cursors doesn't leave the rows locked. That can lead to the
rows being deleted from under the cursor, for which the standard is
unclear as to whether that is acceptable, or not.

AFAICS the SQL Standard also requires that the positioned Update/Delete
also effect only a single row. When using WITH HOLD cursors the desired
row's ctid may have changed. Re-executing the original WHERE condition
might easily reveal more than one row where previously there was only
one. The cursor itself provides no mechanism for telling rows apart in
that circumstance when no Primary Key is defined on the table. We can
surround that with various checks, maybe. ISTM that even allowing this
using WITH HOLD cursors seems likely to be both a poor-performing and
fragile application programming technique.

I'd suggest we add the combination of WITH HOLD cursors and positioned
updates to the small pile of SQL standard items we don't really want to
support for practical reasons.

At very least, I'd suggest we do the straightforward part of this for
8.3 and see whether we want a more full implementation in later
releases.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com