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



---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

> 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.

Um, the default use case is to "intent exclusive" lock the current row,
so you can do some calculations on columns inside the application
without
them changing in the meantime.
So, imho that lock is a substantial feature of FOR UPDATE cursors.
The lock is usually freed as soon as you fetch the next row.
In MVCC db's it is also a method to read a guaranteed up to date
version.

Andreas

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

On Wed, 2007-01-24 at 14:27 +0100, Zeugswetter Andreas ADI SD wrote:
> > 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.
>
> Um, the default use case is to "intent exclusive" lock the current row,
> so you can do some calculations on columns inside the application
> without
> them changing in the meantime.
> So, imho that lock is a substantial feature of FOR UPDATE cursors.
> The lock is usually freed as soon as you fetch the next row.
> In MVCC db's it is also a method to read a guaranteed up to date
> version.

Completely agree.

The standard doesn't say it, but it might be taken to imply that locks
continue to be held, as with 2PC, and released when the cursor is
closed. But I'm not really sure I'd want that either, IMHO.

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



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org